Introduction

While developing applications using vendor software packages, you have likely encountered the arrival of a new software version. Although the new version inevitably contains many improvements, it generally includes a vendor announcement that many of the older version’s functions are obsolete and are not to be used in the new version. The vendor provides a replacement function and the programmer is instructed to systematically replace all obsolete function code with the new and improved version.

This process of migrating to a new software version can be very tedious and time-consuming, especially in large applications when large amounts of code should be scanned for replacement.

Here is a T-SQL procedure that eases the “pain” of migrating to new applications.

The Procedure

I coded a procedure called Sp_replace_and_document to get a source directory parameter that contains all of the source files to be scanned for obsolete functions (the source files are all text files containing source code and it doesn’t matter in which language!)

I also provided the old function name to search parameter, the new name to replace it, a remark for documenting purposes and also the operation date and coder name as input parameters.

The resulting scanned and replaced files will be created inside a destination directory also supplied in the parameter list. A table called text File is used by the procedure. The table stores the intermediate source file content and is truncated with every iteration.

The textFile Creation Script

Here’s the textFile creation script:

Use master

Go

Create table textFile (line varchar(4000))

Go

The process starts with getting the list of all files in the source directory by using xp_cmdshell extended stored procedure with the “dir /p” dos statement. After that, a loop is done over the list of files. The file content is BULK INSERTED to the textFile table and the scanned for the obsolete function and replaced and then the BCP utility exports the resulting file out to the destination directory.

The Sp_replace_and_document Script

Click here to see the script for the Sp_replace_and_document procedure.

Example of a Call to the Procedure

Click here for an example.

This will replace all occurrences for SM_free with MEM_free and create the resulting replaced files in the c:\replace\dest directory.

Notes:

• The sp_replace_and_document procedure was compiled inside the master database and run from there.
• Database Option should allow select into/bulk copy database option.
• A table called textFile will be created in the master database, and it is for the use of this procedure — do not change it.
• The procedure execution should be from a login that has a trusted Windows connection
  because BCP utility uses the –T flag.
• Source and destination directories are supposed to exist prior to procedure execution.
• The remark sign “//” is used in order to add the remark in the source line from this position to the end of the line. If your language supports other means of remarking lines, such as a “—” in PL/SQL, please use your remark sign.

Conclusion

This process can be used as a tool to help when migrating software to a newer version; it does automatic replacements of obsolete functions to new ones making your migration more quick and efficient.

--

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years’ experience working in the database field. Additionally, Mr. Lieba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. You can reach him at Eli.Leiba@2Cher.com.