Friday, February 11, 2011

Recovering from SQL Server Error: “Syntax Error in TextHeader”

After years of working with SQL Server, I thought I’d seen it all. Here’s a humbling reminder that I haven’t:

TITLE: Microsoft SQL Server Management Studio Script failed for StoredProcedure.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476 ADDITIONAL INFORMATION: Syntax error in TextHeader of StoredProcedure. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476 BUTTONS: OK

This occurred when I tried to script out a stored procedure so I could change it. Not a good sign. Fortunately I can fallback to good old sp_helptext to recover the procedure:

sp_helptext 'procname'
-- Results in the procedure’s code

Nifty, right? But why did this happen? It seems SQL Server Management Studio or whatever it uses to script out objects objects to nested comments in the script header. This is what I had, which is a no-no:

/*  example execution:
EXEC procName
  @intParm1=3 /*explanation*/  
 ,@intParm2=null /*explanation*/  
*/

ALTER PROCEDURE [dbo].procName  
/* proc definition*/

What I was trying to do was include a sample call to the procedure to aid future development. This was easily fixed by using the single-line comment syntax instead:

/*  example execution:
EXEC procName
  @intParm1=3 --explanation  
 ,@intParm2=null --explanation  
*/

ALTER PROCEDURE [dbo].procName  
/* proc definition*/

(Hopefully it’s obvious that I don’t normally write procedures with obscure names and parameters like I have above.)