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 r2r@sqlmag.com. 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:\Mssql7\Binn by default for SQL Server 7.0 and C:\Mssql\Binn 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
chigrik@hotmail.com