the frig blig

Today I took some time to debug a perl script which has been failing for a while. The script writes CREATE statements for stored procedures in a specified Sqlserver database. These can be used as backups.

I wrote the original in 2004, running on a Sqlserver 2000 machine. It broke when the database was upgraded — clue!

Long story short, the syntax for system queries changed. The fix was to change the following query [2000 version]:

SELECT ctext FROM syscomments WHERE status = 2 AND id=''

to this [2005 version]:

SELECT definition FROM sys.sql_modules WHERE object_id=''

Now back to other more pressing matters…

This entry was posted on Thursday, December 29th, 2011 at 2:53 pm and is filed under geeky. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.