Syntax error in TextHeader of StoredProcedure? 1

[Gooser]

OH NO!

I just tried to script out my stored procedure that I've been working on to continue work on it. I got this error message:

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

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

For help, click: [URL unfurl="true"]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476[/URL]

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

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

For help, click: [URL unfurl="true"]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476[/URL]

------------------------------
BUTTONS:

OK
------------------------------

Please help! I'll lose a week of work if I can't figure this out.

Thank you,
v/r
Gooser

 

[gmmastros]

What happens when you run this?

Select object_definition(Object_ID('RiskCheckOut'))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

George--

When I run that, I get this:

------------------------------------------------------------

/*

print 1
select * from risk
print 2
DECLARE @RiskToCheckOut int
print 3
SET @RiskToCheckOut = (SELECT TOP 1 RiskID FROM Risk ORDER BY RiskID Desc)
print 4
EXEC PropertyFacts.dbo.RiskCheckOut
     @RiskToCheckOut   --@OldRiskID
   , 'dbo-Tes

(1 row(s) affected)

Thanks,
v/r
Gooser

p.s. - What should I get?

 

[gmmastros]

If that doesn't work...

Select Text
From   syscomments
Where  Id = Object_ID('RiskCheckOut')
Order By ColId

As I was looking around, it appears that this problem may be caused by having nested comments in the header.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

p.s. - What should I get?

Ideally, you would get the code for the stored procedure.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

George,

I ran that second piece you posted, but it just gives me chunks of my stored procedure back, starting with a commented-out section that was at the top.

...confused...

Thank you,
v/r
Gooser



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Bailout We Need

 

[Gooser]

...Sorry - Stupid SIGNATURE!...

 

[gmmastros]

It didn't return the whole thing? That's weird. It could be that you need to switch to text mode in order to see the whole thing.

click Tools -> options
expand "Query Results"
expand "SQL server"
Click General
Set "Default destination for results" to "Results to text"

The syscomments table is a system table that stores all of your code. The TEXT column is an nvarchar(4000) column. So, if your code is longer than 4000 characters, it will come out in multiple rows (which explains why I put ORDER BY COLID).

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

I tried that too, it is still just chunks of it, not the whole thing.

 

[Gooser]

Each row has part missing. They just pick up randomly along a line of code and drop off randomly a few lines later. This procedure was quite long and most of it is not there.

Thank you so much for your excellent help as usual, George. Any further ideas?

 

[gmmastros]

Last thing I can think of...

click Tools -> options
expand "Query Results"
expand "SQL server"
Click "Results To Text"
Change "Maximum number of characters displayed in each column" to 8000.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

George-

That didn't do it either. It's starting to look like I will have to re-write most of it. [sobbing]

Thanks for your help!

v/r
Gooser

 

[Gooser]

George-

Again, I cannot thank you enough for your insightful help.

Unfortunately the Stored Procedure was corrupted beyond repair. Though I was eventually able to restore a ShadowCopy version of the database file from early yesterday morning and the Stored Procedure was still in that version. WHEW!

Lessons Learned
[li]BACKUP--even your dev box.[/li]
[li]No nested comments. in your Stored Procedure Headers. (Who would've thunk?)[/li]
[li]ShadowCopy can be a lifesaver![/li]
[li]Couldn't attach ShadowCopy version of db in DATA directory, got a weird 'File is Read-Only' error.[/li]
[li]No error message when .mdf was attached in a different directory(?)[/li]
[li]When in doubt, ask George.[/li]
[li]backup, BackUp, BACKUP![/li]
[li]Did I mention Backup?[/li]

Thanks again, George.

v/r
Gooser