Readers' Tips and Tricks - 20 Jun 2001

Copying a Text or Image Value

Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.

You can use SQL Server's little-known Textcopy utility to copy a text or image value into or out of SQL Server. You can find this utility in the directory that contains the standard SQL Server .exe files (C:Mssql7Binn by default for SQL Server 7.0 and C:MssqlBinn by default for SQL Server 6.5). Note that you must include the textcopy.exe file in the system path.

SQL Server Books Online (BOL) doesn't cover the Textcopy utility, but you can read a simple description by typing

textcopy /?

at the command prompt. The utility's syntax is

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]] [/D [database]] [/T table] [/C column] [/W "WHERE clause"] [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

The letters after the forward slash (/) are case-sensitive.

If you specify IN (/I), Textcopy copies the data from the specified file into SQL Server, replacing any existing text or image value. If you specify OUT (/O), the utility copies the text or image value from SQL Server into the specified file, replacing any existing file.

To simplify the use of Textcopy, you can use the stored procedure that Listing 1 shows. The following statement shows an example of how to copy an image into the logo column of the Pubs database's pub_info table from the picture.bmp file, where pub_id='0736':

sp_image @srvname = 'ServerName',         @login = 'Login',         @password = 'Password',         @dbname = 'pubs',         @tbname = 'pub_info',         @colname = 'logo',         @filename = 'c:picture.bmp',         @whereclause = " WHERE pub_id='0736' ",         @direction = 'I'

—Alexander Chigrik
[email protected]

Working with Audit Columns

Many developers audit table changes by adding columns to track who performed the update and the time the last update occurred. Listing 2, page 20, contains a sample script of a denormalized student grade table with the additional update columns UpdateTS, which holds a datetime value, and UpdateUserID, which identifies the user who performed the update.

Many developers add these columns to UPDATE statements throughout their application, as the first UPDATE statement in Listing 2 shows. The problem with this practice is that ad hoc UPDATE queries outside of the application might not include the additional fields.

One way to solve this problem is to include the audit columns in an UPDATE trigger, as Listing 2's CREATE TRIGGER statement shows. But this method creates a potential recursive-trigger situation. The success of the trigger depends on nested triggers being disabled. If someone modifies the trigger setting at the server or database level, your application will fail. The first update in Listing 2 occurs after the recursive triggers option is turned off at the database level. This update succeeds, as the SELECT statement demonstrates. However, the second update occurs after the recursive triggers option is turned on, and this update fails.

As a workaround, you could add criteria to stop the recursion. Callout A in Listing 2 shows an altered trigger that updates the audit columns only when the UserID has changed or more than a second has elapsed. But an UPDATE statement that affects many rows could take longer than the allotted time to complete. If the update does take longer, recursion occurs and the solution doesn't work.

You can get around the recursion problem by using either the @@NESTLEVEL or the TRIGGER_NESTLEVEL function. At callout B in Listing 2, the AFTER trigger becomes an INSTEAD OF trigger. The INSTEAD OF trigger fires only once, even if recursive triggers are enabled. The trick is to rebuild the row with the additional audit columns, then fire the intended update.

—Tom Antola
[email protected]

Writing a UDF to Generate State Abbreviations

Many companies adhere to strict standardization and business rules when storing their data. For example, where I work, we interchange data with a company that uses 2-character state abbreviations. The State field in its database is char(2) and won't accept anything but a correctly formatted state code. The problem is that we receive daily bulk spreadsheet-file imports from clients that sometimes use a state code and sometimes spell out the state name. As the DBA, I could change our State field data type to char(2) and require all our clients to comply. But our job is to make our clients' lives easier, not more complicated. Even with such a requirement, clients could still make mistakes—or simply rebel.

Using an UPDATE statement with the CASE function to change existing values in the database is easy. But we really needed a way to automatically scrub the data before we imported it into the database. With that goal in mind, I created a user-defined function (UDF) called statecode() on our SQL Server 2000 system. Listing 3 shows the function. Now, I can simply alter the SQL code in my Data Transformation Services (DTS) address-import package as follows:

SELECT prop_id, street_num, street_name, apt, city, dbo.statecode(state) ASstate, zipFROM [Sheet1$]

and the State value is automatically formatted correctly.

—Cade Bryant
[email protected]

Importing DB2 Dates into SQL Server

Our site often uses 0001-01-01 on DB2 OS390 as low date instead of NULL. We've developed an ANSI SQL statement, which Listing 4 shows, that we use in a Data Transformation Services (DTS) package to import into SQL Server any DB2 dates earlier than January 1, 1753, and assign those dates a value of any date value greater than or equal to 01/01/1753. This approach, which uses a DB2 Connect ODBC call from SQL Server to DB2 OS390, precludes having to programmatically massage the data on the mainframe, use FTP to send the data to SQL Server, then use the bulk copy program (bcp) to load the data. You might find other uses for this SQL code in handling data exchange between different data types and databases.

—Ron Eureka
[email protected]