Getting "Syntax error in TextHeader of StoredProcedure" error

Question

Getting "Syntax error in TextHeader of StoredProcedure" error

Rod at work

SSC-Dedicated

Points: 34017
More actions
June 19, 2009 at 2:14 pm

#210990

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%5B/quote%5D
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.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Rod at work SSC-Dedicated Points: 34017 More actions · Answer 1

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 Valued Member Points: 59 More actions · Answer 2

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 Say Hey Kid Points: 674 More actions · Answer 3

I know this is an old post... but saved my life today.

Cheers

Ronnie

luisalbt SSC Journeyman Points: 91 More actions · Answer 4

But how to modify the code of the transaction to correct the error ?

Thanks

hot2use SSCrazy Points: 2915 More actions · Answer 5

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 SSCrazy Points: 2339 More actions · Answer 6

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 SSC Journeyman Points: 81 More actions · Answer 7

Kingyute

SSC Journeyman

Points: 81

May 20, 2016 at 7:47 am

#1880489

The Gift that keeps on Giving.. Great Stuff.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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?

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

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 */

*/

bobt 46978 SSC Veteran Points: 269 More actions · Answer 10

Thank you, Rod at Work!

What a great, simple solution!

tommyo SSC Journeyman Points: 83 More actions · Answer 11

Good post!

I just wanted to add that every time this has happened to me, the culprit has been block-quotes within block-quotes, which will apparently compile and save OK (and execute OK), but will not open in SSMS.

/*

This comment is OK

/*

this part will screw you up

*/

Sector7G Ten Centuries Points: 1040 More actions · Answer 12

If you are getting this error, try to modify the proc in a different version of Management Studio.

I have been dealing with this problem all afternoon. I could not open any stored procedures in any database on any server. So I created a new proc on the instance running on my local PC. I create a proc like this:

create PROC TestProc1

as

SELECT getdate() AS TodayIs

Some people run into problems when their code is not commented properly. As you can see, there are no comments in this proc. But when I tried to modify the proc, I still got the same error. I tried restarting management studio, and then restarting my PC. I still could not modify the proc I just created. I finally figured out I CAN modify the proc in SSMS 2014, but not SSMS 2016. I think something in the last update I downloaded for 2016 may have caused this issue. I did the update yesterday, but I don't think I had to modify any procs until today.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Sector7G (9/23/2016)
If you are getting this error, try to modify the proc in a different version of Management Studio.
I have been dealing with this problem all afternoon. I could not open any stored procedures in any database on any server. So I created a new proc on the instance running on my local PC. I create a proc like this:
create PROC TestProc1
as
SELECT getdate() AS TodayIs
Some people run into problems when their code is not commented properly. As you can see, there are no comments in this proc. But when I tried to modify the proc, I still got the same error. I tried restarting management studio, and then restarting my PC. I still could not modify the proc I just created. I finally figured out I CAN modify the proc in SSMS 2014, but not SSMS 2016. I think something in the last update I downloaded for 2016 may have caused this issue. I did the update yesterday, but I don't think I had to modify any procs until today.

Yes, at our SQL Server Users Group meeting on Wednesday of this week we were told not to download and install the latest SSMS 2016 update for just that reason.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

With the latest SSMS 2016, you can avoid this error by changing the default scripting option "Convert user defined data types to base types" to True.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);