Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting "Syntax error in TextHeader of StoredProcedure" error Expand / Collapse
Author
Message
Posted Friday, June 19, 2009 2:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 936, Visits: 1,629
I'm tried to edit one of our stored procedures in SSMS. When I attempt to open it, I get the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Script failed for StoredProcedure 'dbo.spWebCSVouchers99New'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Syntax error in TextHeader of StoredProcedure 'spWebCSVouchers99New'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476


I've searched on the Web and I know that the issue is an embedded comment, before the ALTER PROCEDURE statement. OK, that's neat, and cool, and all that, but how do I edit the SP to removed the embedded comment, if I cannot open it?


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #738639
Posted Friday, June 19, 2009 3:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 936, Visits: 1,629
I think I've found the answer in BOL, although it took me a long time to find it. Anyway, what I did was to enter the following in a query command window:

sp_helptext N'spWebCSVouchers99New'



Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #738676
Posted Thursday, May 15, 2014 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 12:09 AM
Points: 1, Visits: 1
Hii Central,

Its worked for me too for below error and ran the procedure with Helptext and able to edit it.

Thanks guies....!

Error : syntax error in TextHeader of StoredProcedure

Regards,
Sudhir
Post #1571136
Posted Thursday, March 26, 2015 4:33 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 7, 2016 7:06 AM
Points: 21, Visits: 421
I know this is an old post... but saved my life today.

Cheers

Ronnie
Post #1671683
Posted Tuesday, November 24, 2015 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 24, 2015 10:30 AM
Points: 1, Visits: 0
But how to modify the code of the transaction to correct the error ?

Thanks
Post #1739755
Posted Tuesday, December 22, 2015 12:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 22, 2015 12:05 AM
Points: 131, Visits: 71
And thanks from me to you for this post.

Got the details of a stored procedure with wrong block quotes that was hindering the MODIFY command with the same error message.

Here is what I found. Find the error:

/*
' --------------------------------------------------------------------- '
' Product: Product Name '
' System: System Namae '
' Subsystem: Selector '
' Function: Yyy3000ReportData_Prepare_ProjectResultFx30 '
' Owner: schema owner '
' --------------------------------------------------------------------- '
' Function: Sets the estimated time that is necessary for '
' the data preparation. Prepares the report data. '
' Updates the state in the SystemQueue. '
' --------------------------------------------------------------------- '
' Parameter: See below. '
' --------------------------------------------------------------------- '
' Sample: DECLARE @QueueId uniqueidentifier '
' SET @QueueId = NEWID() '
' EXEC <schema name>. '
' Yyy3000ReportData_Prepare_ProjectResultFx30 '
' 103 /*v16, v17: 113*/
' ,@QueueId '
' ,1 '
' ,2 '
' ,'W12489' '
' ,'001' '
' ,'0000375412' '
' ,1 '
' ,'D' '
<snipped for brevity purposes>

Alas the code is highlighted correctly in the above section, so you can see the error or what was left of the syntax issues. The comments where located before the definition of the actual sproc, which seems to be bad practice.

Funny enough the sproc was still working.

The fix was to drop the procedure and then insert back in the corrected code.

Thanks

hot2use



__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Post #1746989
Posted Tuesday, April 5, 2016 7:12 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 29, 2016 3:41 AM
Points: 23, Visits: 246
This post just saved the day for me as well.

I had 3 scripts of third party code that I was combining into a single sproc. In the original code there was lots of nested multi line comments that seemd to be tripping the MODIFY command up.

I reformatted them and now it works.

Cheers to the OP!
Post #1775036
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse