Atlanta Custom Software Development 

   Search        Code/Page

User Login



Forgot the Password?
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
» Regular Expr Tester
» Free Tools

Copy text or image into or out of SQL Server

Total Hit ( 7140)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article



In this article, I want to show, how you can copy a single text or image value into or out of SQL Server by using the textcopy.exe utility. You can find this utility in the directory containing the standard SQL Server EXE files (C:\Mssql\Binn for SQL Server 6.5, by default and C:\Mssql7\Binn for SQL Server 7.0, by default).

Textcopy.exe utility is not described in SQL Server Books Online, but you can get its description by typing textcopy /? from the command prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The value
is a specified text or image 'column' of a single row (specified by the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

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

  /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not
                     specified, the local SQL Server is used.
  /U login           The login to connect with. If 'login' is not specified,
                     a trusted connection will be used.
  /P password        The password for 'login'. If 'password' is not
                     specified, a NULL password will be used.
  /D database        The database that contains the table with the text or
                     image data. If 'database' is not specified, the default
                     database of 'login' is used.
  /T table           The table that contains the text or image value.
  /C column          The text or image column of 'table'.
  /W "where clause"  A complete where clause (including the WHERE keyword)
                     that specifies a single row of 'table'.
  /F file            The file name.
  /I                 Copy text or image value into SQL Server from 'file'.
  /O                 Copy text or image value out of SQL Server into 'file'.
  /K chunksize       Size of the data transfer buffer in bytes. Minimum
                     value is 1024 bytes, default value is 4096 bytes.
  /Z                 Display debug information while running.
  /?                 Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of textcopy.exe utility:

Click here to copy the following block
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))
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

This is the example to copy image into SQL Server database pubs, table pub_info, column name logo from picture.bmp file where pub_id='0736':

Click here to copy the following block
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 the way, you should insert something into text/image column before copy text/image into it. I mean, this value should not be null. You should write (for example):

Click here to copy the following block
INSERT INTO [dbo].[images] VALUES ('1', 0x0, null)

instead of:

Click here to copy the following block
INSERT INTO [dbo].[images] VALUES ('1', null, null)

Otherwise, you will get the following error message:

ERROR: Text or image pointer and timestamp retrieval failed.

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with, SQL Server and other MS technologies. He is, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )

Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.