SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting "Syntax error in TextHeader of StoredProcedure" error


Getting "Syntax error in TextHeader of StoredProcedure" error

Author
Message
Rod
Rod
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 2059
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.
Rod
Rod
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 2059
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.
su.usnale123
su.usnale123
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Ronnie Rahman
Ronnie Rahman
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 433
I know this is an old post... but saved my life today.

Cheers

Ronnie
luisalbt
luisalbt
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 0
But how to modify the code of the transaction to correct the error ?

Thanks
hot2use
hot2use
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 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.

lindsayscott23
lindsayscott23
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 404
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!
Kingyute
Kingyute
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 52
The Gift that keeps on Giving.. Great Stuff.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39197 Visits: 38529
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?

Cool
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)
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4815 Visits: 9108
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 */
*/





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search