srv_convert error copying stored procedure

[Glasgow]

I have a stored procedure that itself copies stored procedures and user defined functions from one database to another using OLE object manipulation procedures (i.e. sp_OA.... routines).

When copying a particular stored procedure, it fails with 'Error in srv_convert' when attempting to extract the Text property from the source stored procedure. I have a feeling this may be something to do with the length of the text - is there a limit (4000 bytes?) on this and does anyone have any idea how might I be able to work around this problem?

Thanks in advance.

 

[NoCoolHandle]

I do seem to remember diving into syscomments and bumping into this before.. The issue is that the text column that you would use to build the comments is a "computed" column and is based on the ctext column which is Binary..

Also the text column is an N'Text (unicode), so there is a 4000 char limit..

My memory of what I had to do for rebuilding stored procs was to do a cast on the ctext to rebuild the binary ctext col, but that there were some instances that caused problems.

The only thing I remember was having to use the cText col and do a cast to text to rebuild the logic. Using the text col didn't work correctly.

Hope this was some help, it was a year or so ago that I did this..

Rob

 

[Glasgow]

Thanks for the input Rob.

Is the Text property not based on the column in SysComments called 'text' rather than 'ctext'? The stored procedure I am trying to copy is a special case because its SQL spills across the 'text' columns of 2 records in SysComments and this would appear to be something that the Sp_OAGetProperty stored procedure can't handle when retrieving the text property.

I'm not sure where to start other than, perhaps, abandoning the Sp_OA routines (partially or completely) and attempting to manipulate SysComments & SysObjects directly which is a little scary to say the least!

 

[Glasgow]

On further investigation, Microsoft appear to have addressed a similar problem with sp_OASetProperty (resolved in SP3) but:

"However, the fix does not address retrieving strings longer than 4000 characters as output parameters through the sp_OAGetProperty OLE Automation extended stored procedure"

and

"You may receive this error message when you try to retrieve a string property that is between 4001 characters and 8000 characters (inclusive) in length through sp_OAGetProperty or as a return value from sp_OAMethod:

hr Source Description
---------- ---------------------------- --------------------------
0x8004271A ODSOLE Extended Procedure Error in srv_convert."

Source:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325492

Great!