Stored proc text corrupt if large comments in source script

Stored proc text corrupt if large comments in source script

Post by Erik Oxaa » Wed, 15 Oct 1997 04:00:00

Just wanted to share the following, it applies to 6.5 sp3:

After loading a set of SP-scripts with ISQL.EXE it turned out that for
several procedures the text (in syscomments table) was corrupt. What these
procedures had in common were large descriptive comment headers. Corruption
only occurred for comments in specific size ranges, e.g. 438 to 500 chars,
94? to 1006 chars, 1966 to 2012 chars.
Strangely, the corruption was not present if the same script was executed
using ISQL/W.

Eventually I was able to have ISQL load the procs without corruption to
syscomments by explicitly  setting the packet size to 8192 (switch '/a8192'
-- even though BOL says that *is* supposed to be the default packet size
under NT). Setting the packet size to 512 ( /a512 ) makes the problem
resurface, so maybe that is the actual default size.

Is somebody able to shed some light on what may be going on behind the
scenes here?

Incidentally, there is a KB article that describes similar corruption
problems for version 6.0, but does not give a workaround. . I'm surprised
this hasn't been fixed for 6.5.

Erik Oxaal
Conceptos a.s


Stored proc text corrupt if large comments in source script

Post by Russell Field » Wed, 15 Oct 1997 04:00:00

Of course, the workaround in the KB is to make several short comments.

However, if I remember correctly, the workaround we used was to have the
comment after the as rather than embedded between the create statement and
the as.  E.g.

create procedure myproc (


This caused us a problem.

create procedure myproc (

/* Really big comment */

This did not.  Does this sound like your problem? - Russell Fields


Stored proc text corrupt if large comments in source script

Post by CBK » Sat, 18 Oct 1997 04:00:00

Actually in my case the comment was *before* the create statement, I didn't
try moving it after the 'as' --- although I seem to recall reading
somewhere a recommendation to do so.

However, as long as it works fine by upping the packet size that's OK. I
was just wondering what was really going on behind the scenes to cause the

Erik Oxaal
Conceptos a.s


1. Create large Stored proc from within stored proc

Is there a way to create a large stored procedure (contains unicode data
but > 4000 characters) from within a stored procedure.   I am
dynamically creating a stored procedure based on user defined tables and
columns.   Up to now this was working using the

procedure needs to be > 4000 unicode characters.

2. SQLServerAgent Properties

3. stored proc parameter problem, comments please

4. SP Question

5. My ObjectPAL text file import script - comments anyone?

6. Repair-tracking database software?

7. Writing Large Text field to a text File in Stored Procedure

8. scripting comments out of a stored procedure

9. text to access, source column too large for buffer

10. Stored Proc corrupt

11. get source of a stored proc

12. SQL Server stored proc to VB Source generator