在MS SQL SERVER 安装目录下有个可执行文件叫 TEXTCOPYEXE利用它可对 MS SQL SERVER 中的文本或图像数据进行输入输出如果你对它不熟悉可以在MSDOS方式下执行textcopy /? 得到它的描述
下面是这个工具的描述:
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] [/Wwhere 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 spec ified 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 bytes default value is 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
为此 可写一个存储过程调用这个命令
CREATE PROCEDURE sp_textcopy (
@srvname varchar ()
@login varchar ()
@password varchar ()
@dbname varchar ()
@tbname varchar ()
@colname varchar ()
@filename varchar ()
@whereclause varchar ()
@direction char())
AS
DECLARE @exec_str varchar ()
SELECT @exec_str =
textcopy /S + @srvname +
/U + @login +
/P + @password +
/D + @dbname +
/T + @tbname +
/C + @colname +
/W + @whereclause +
/F + @filename +
/ + @direction
EXEC masterxp_cmdshell @exec_str
下面是一个拷贝图像到SQL Server的pubs数据库的例子 表名pub_info 字段名
logo图像文件名picturebmp保存到pub_id=记录 sp_textcopy @srvn
ame = ServerName
@login = Login
@password = Password
@dbname = pubs
@tbname = pub_info
@colname = logo
@filename = c:\picturebmp
@whereclause = WHERE pub_id=
@direction = I
