In this article, we will explore how to copy a single text or image value into or out of SQL Server using the textcopy.exe utility. This utility can be found in the directory containing the standard SQL Server EXE files (C:MssqlBinn by default for SQL Server 6.5 and C:Mssql7Binn by default for SQL Server 7.0).

The textcopy utility is not documented in SQL Server Books Online, but you can access its description by typing “textcopy /?” from the command prompt. The utility allows you to copy a specified text or image ‘column’ of a single row (specified by the “where clause”) of a specified ‘table’.

If the direction is set to IN (/I), the data from the specified ‘file’ is copied into SQL Server, replacing the existing text or image value. On the other hand, if the direction is set to OUT (/O), the text or image value is copied from SQL Server into the specified ‘file’, replacing any existing file.

Here is an example command for using the textcopy utility:

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

Let’s take a look at a stored procedure that simplifies the usage of the textcopy utility:

CREATE PROCEDURE sp_textcopy (
  @srvname     varchar (30),
  @login       varchar (30),
  @password    varchar (30),
  @dbname      varchar (30),
  @tbname      varchar (30),
  @colname     varchar (30),
  @filename    varchar (30),
  @whereclause varchar (40),
  @direction   char(1)
)
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
         'textcopy /S ' + @srvname +
         ' /U ' + @login +
         ' /P ' + @password +
         ' /D ' + @dbname +
         ' /T ' + @tbname +
         ' /C ' + @colname +
         ' /W "' + @whereclause +
         '" /F ' + @filename +
         ' /' + @direction
EXEC master..xp_cmdshell @exec_str

Using this stored procedure, you can easily copy an image into the SQL Server database. For example, to copy an image from the file “picture.bmp” into the “pub_info” table of the “pubs” database, where the “pub_id” is ‘0736’, you can execute the following command:

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

By utilizing the textcopy.exe utility and the provided stored procedure, you can easily copy text or image values into or out of SQL Server, simplifying the process of managing and transferring data.

Thank you for reading this article. Stay tuned for more SQL Server tips and tricks!