I have a "merge" publication created, with only Procedures, Views and Functions. (Note I have other merge publications that replicate the tables from the same database). I keep getting the same error (see -below) on various procedures. I cannot find anything wrong with the procedures themselves. I also checked the offending procedure by removing it from the publication and compiling it in the database...it works fine. None of my other publications (all tables) encounter this error. * I have 3 others.
My version of SQL is 9.00.1399.06
Here is the message I am getting. Any help would be greatly appreciated. Note I changed the proc name due to client restrictions.
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Command Text:
select 'number' = convert(int, 0), 'definition' = definition
from sys.sql_modules
where object_id = object_id(@qualified_object_name)
union all
select 'number' = convert(int, procedure_number), 'definition' = definition
from sys.numbered_procedures
where object_id = object_id(@qualified_object_name)
Parameters: @qualified_object_name = [dbo].[pra_merge]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.MergeSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006)
Get help: http://help/52006
Source: mscorlib
Target Site: System.Text.StringBuilder Remove(Int32, Int32)
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
SQL Server 2005 - Replication - Snapshot Failing
Grayson Peddie
Raymond,
I can, however it may be a little while before I can give you the answer. We are in system testing at the moment and I cannot upgrade the service pack yet.
Mike Two
Hi Doug, I just responded to your feedback item on the Connect site. There seems to be some remaining issues with the stored procedure parsing logic (which may or may not to related the length of the header comments) in SP2 and it would be great if you can send me your stored procedure comments.
Thanks much,
-Raymond
moondaddy
Hi Bill, while it is great that you could workaround the problem by specifying the unsupported /VerbatimTextObjectScripting option to the snapshot agent, we would very much like to get to the bottom of the issue. As such, we would really appreciate if you can:
1) Verify whether the problem still exists in either SP1 or the latest SP2 CTP
2) If either SP1 or SP2 CTP still exhibit the problem, it would be great if you can post a sanitized version of the problematic procedure so we can do some concrete investigation to resolve the underlying issue.
Thanks much,
-Raymond
coolblue2000
Raymond,
The VerbatimTextObjectScripting did the trick. After my snapshot worked, I went back and reviewed the error messages and the procedures associated with them. The one thing I noticed is that on all the procedures that the snapshot would fail on, each procedure had a large amount of comments in the header. For instance, the merge procedure was 15, 000 characters worth of comments (roughly 2000 words). THANK YOU!
djt69
See the feedback ticket at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=273431.
Doug
Whoisit
Hi Bill,
My following postings for people who reported similar problems may prove useful to you:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=970476&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=315457&SiteID=1
On top of that, I would really appreciate if you can post a sanitized version of dbo.pra_merge (while still triggering the problem).
-Raymond
Grant_csi
Even though I have responded to Doug in private about the issue, I thought I should post the findings of my investigation in public just so folks can find out what is going on if they ever stumble upon the same issue. While there are a multitude of reasons for the same symtom\call stack (some are already fixed in SP2), what Doug saw was indeed a bug in my code which remained unfixed in SP2 and will affect anyone who tries to replicate a stored procedure\function\view\trigger with a multi-line header comment delimited by /* */ exceeding 4000 characters in length (and with the word "as" embedded somewhere in the comment). To workaround the issue, you can:
1) Use -- (dash dash) instead of /* */ for marking your comment block
2) Move your comment block inside the object body (Doug's workaround)
3) Specify the unofficial -VerbatimTextObjectScripting 0 option to the snapshot agent command line (warning: this will lop off the header comments in the replicated text object)
I will post back once I manage to get a QFE request approved for the fix.
-Raymond