TextCopy: Import file into image and text column in SQL 2000

SQL 2000 has a undocumented command prompt tool TextCopy, which can load a file into a text or image column. The tool uses
DB-Library connectivity.
The tool is under \Microsoft SQL Server\MSSQL\Binn. Goto the folder in command prompt, type TextCopy -?
You will get the following help:
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.
Copy TextCopy.exe from the default location to C:\ dirve.
Example:
EXEC master..xp_cmdshell ‘ c:\textcopy.exe /S APRND0096 /U sa /P  /T dbo.Employees /D Northwind /C photo /W "WHERE
EmployeeID=1" /O /F C:\myfile.JPG’
For unicode data (e.g. nText), you will get the following error:
TEXTCOPY Version 1.0
DB-Library version 8.00.194
SQL Server ‘Server name’ Message 4004: Unicode data in a Unicode-only collation or
 ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC ve
rsion 3.7 or earlier. (Concerning line 1)
DB-Library Error 10007: General SQL Server error: Check messages from the SQL Se
rver.
ERROR: Query execution failed.
E.g., if you copy Note column, which is of NTEXT data type, from table dbo.Employees in Northwind, you will get the above
mentioned error.’
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s