Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ch9862
Yak Posting Veteran
76 Posts |
 Posted - 2011-09-20 : 10:19:46
|
| Is there some sort of database-wide setting that disables the "--" comments?I'm working with 2 databases on MSSQL 2008 SP1, 64-bit - as a developer, not a dba.In database A - if I use inline comments (--) inside a stored procedure, I can only use sp_helptext to see the contents. (I then see line breaks in unexpected places - like in the middle of a variable name. Not sure if this has anything to do with it.)Right-clicking and selecting modify in the Mgmt Studio generates a "script failed" error. Under Additional Info there is "syntax error in text header...". No error number.If I use block comments (/*) everything's fine. I spoke with the dbo, who tells me he can't script my SP.No such behavior in database B - where i can use inline comments wherever I want.Any ideas what might be causing this?TIA |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 10:32:28
|
" I can only use sp_helptext to see the contents. (I then see line breaks in unexpected places - like in the middle of a variable name"sp_helptext has a width limit of, I think, 256 characters. You will get a line-break at that point (including in lines of code, let alone comments)"I spoke with the dbo, who tells me he can't script my SP"He tried and got an error? (what was the error?) or he doesn't know how / can't be bothered / something else?Can you script it in SSMS? (Right click and Script to New Query Window, or Clipboard)If it works on one DB and not the other its either a config setting (I don;t know of one), or a database corruption perhaps?You / your DBA could try runningDBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITYto check for any database corruption |
 |
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2011-09-20 : 11:20:33
|
quote:
sp_helptext has a width limit of, I think, 256 characters. You will get a line-break at that point (including in lines of code, let alone comments)
Thanks - that explains it.quote:
He tried and got an error? (what was the error?) or he doesn't know how / can't be bothered / something else?
This appears to be a script engine error. I can still create/alter SPs, with or without (--) comments in them, but they can't be later scripted if there are inline comments in them.When I (or dbo) right-click the SP in Mgmt Studio and select "Modify" or "Script to", we get the same error.TITLE: Microsoft SQL Server Management StudioScript failed for StoredProcedure 'user_name.proc_name'. (Microsoft.SqlServer.Smo)ADDITIONAL INFORMATION:Syntax error in TextHeader of StoredProcedure 'proc_name'. (Microsoft.SqlServer.Smo)Clicking on help button reveals message source to be:Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionTextquote:
If it works on one DB and not the other its either a config setting (I don;t know of one), or a database corruption perhaps?
Yes - I can't seem to google much on this issue. I'll ask the dbo to check the database.Thanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 11:43:38
|
| DBA could perhaps try scripting "All Stored Procedures" - within which would be yours. Might break at the same point, or (because its a slightly different angle of attack) it might work.Suggest checking the Patch Version for each database, if they are different then updating the broken one to be the same as the working one may fix it - might be some bug that has since been fixed.SELECT @@versionwill give you the patch version. If they are the same then there isn't anything to be done (unless its not the latest version and you want to upgrade)It might be the version of the database drivers (on the server). If both databases are on the same server then scratch that! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 11:54:23
|
Have you got nested comments before a CREATE PROCEDURE statement?I don't think "--" comments matter, but this will fail to script:IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'TEMP_SP_Comments') DROP PROCEDURE dbo.TEMP_SP_CommentsGO/*/*xxx*/*/CREATE PROCEDURE dbo.TEMP_SP_Comments @intDebug int=0ASSET NOCOUNT ONSET XACT_ABORT ONSET ARITHABORT ON PRINT 'In SProc'GOexec sp_helptext 'TEMP_SP_Comments' Ref: http://connect.microsoft.com/SQLServer/feedback/details/417074/error-scriping-procedure-when-there-are-nested-comments-before-create-procedure |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 11:58:46
|
| By the by, we store all SProc source code in filesystem files ... and from there in a revision control system.Gotchas like "can't have nested comments in SProc headers" reinforce the sense of that decision, for me. If the revision control breaks its in the DB, and vice versa. And either way its on the disk ... |
|
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2011-09-21 : 09:09:21
|
Edit: Not sure why my response was garbled, it looked OK on preview! So here is what I meant to write  .quote:
Originally posted by KristenHave you got nested comments before a CREATE PROCEDURE statement?I don't think "--" comments matter(...)
The problem was with the "--" comments, when I generated new SP using template provided with the Mgmt Studio, the top section had a comment block looking similar to:-- ========-- Author: -- ======== Such an SP would cause scripting error. But it was enough for me to change it to:/*-- ========-- Author:-- ========*/ And everything was fine.The problem disappeared when admin recycled server in the evening .quote:
Originally posted by KristenBy the by, we store all SProc source code in filesystem files ... and from there in a revision control system.
Similar here. I develop SPs in the database. Once they are stable, I script them, put into revision control and have DBA publish them as dbo.Thanks for your help! |
|
|
|
|
|
|
|
|
No inline comments?