Converting SUSER_NAME to SUSER_SNAME

[KABerasley]

I'm piloting an upgrade from 6.5 to 2000. I find I need to use SUSER_SNAME now where I've used SUSER_NAME in the past. I have several hundred stored procedures and triggers which use SUSER_NAME.

Has anyone developed or seen a way to do this without opening and running several hundred script files?

 

[MeanGreen]

Why don't you generate SQL Script of all stored procedures, do a FIND/REPLACE in WORD, then run the SCRIPTs in one pass? I don't think I have seen anything built in to SQL to do this.

Hope this helps.

 

[KABerasley]

Thanks, MeanGreen, your suggestion turned out to work fine.

I had intentionally not done this because it would not have worked in 6.5. Many of our procedures use temp tables created by parent procedures, tables that don't exist at compile time. Our script file creates the temp table, creates the procedure, then drops the temp table. A script generated from the server doesn't have the temp table creation, and errors out when it tries to create the procedure.

SQL 2000 seems to allow creation of procedures without requiring that all referenced objects exist.