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 12»»

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 1,004, Visits: 1,747
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 1,004, Visits: 1,747
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: Monday, July 25, 2016 4:05 AM
Points: 21, Visits: 430
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


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 29, Visits: 287
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
Posted Friday, May 20, 2016 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 11, 2016 8:44 AM
Points: 1, Visits: 52
The Gift that keeps on Giving.. Great Stuff.
Post #1788050
Posted Friday, May 20, 2016 8:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 23,174, Visits: 36,984
Just curious, if clicking on Modify resulted in an error did anyone try clicking on Script Stored Procedure As, select Alter to, then select New Query Editor Window and see if that worked?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1788073
Posted Wednesday, June 1, 2016 1:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 2,921, Visits: 8,961
Lynn Pettis (5/20/2016)
Just curious, if clicking on Modify resulted in an error did anyone try clicking on Script Stored Procedure As, select Alter to, then select New Query Editor Window and see if that worked?


I just encountered this problem and tried every option: script create to Window, file, clipboard..... same with script alter

hot2use (12/22/2015)
.....The comments where located before the definition of the actual sproc, which seems to be bad practice.....
hot2use

I think the problem I encountered is due to nested comments:

/*  comment blah blah
/* nested comment, blah blah */
*/




Post #1791395
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse