August 09, 2008

SQL Server 2005 Finicky with Nested Comments

I found out the hard way that MS SQL Server 2005 is sometimes finicky about nested comments leading up to CREATE PROCEDURE or ALTER PROCEDURE lines. Here's a simple illustration of this phenomenon:

/*Bad comment line*/2
CREATE PROCEDURE [dbo].[GetProduct]

This code executes without error...but everything grinds to a halt when you try to modify or script it. Management Studio reports a Syntax error in TextHeader.

I ran into this behavior after working an hour on a fairly complex sproc. I didn't back up my work before I ran it, assuming I could always pull it out of the database to make tweaks if necessary. Needless to say I was miffed to find that I couldn't open the stored procedure and effectively lost my work.

Thankfully, it's possible to retrieve code that's lost in this manner:

ROUTINE_Name = 'GetProduct'

1 comment:

Anonymous said...

Hello I find here from

I wrote there a message (very very late :) )

I wanted to inform you.
Good days.

Message at forum :


First excuse me for my english.

I took same error message for a user defined sql function (sql server 2005 - sp3).

I found error source, I couldnt take function source from sql server management studio with Modify and after tried to Genereate database script of function (from Task-Generate Scripts.) I saw that, function header like this

create function [dbo].[f_function_name](@p_variable nvarchar(300))
returns nvarchar(300)


There was not "as" before begin. But at the functions which doesnot give error "as" was its place.

When I altered function like this :

create function [dbo].[f_function_name](@p_variable nvarchar(300))
returns nvarchar(300)


Than, error corrected.

Problem was "AS" at my case, perhaps it may help someone.