MS SQL SERVER 图像或大文本的输入输出

在MS SQL SERVER 安装目录下有个可执行文件叫 TEXTCOPY.EXE 可对 MS SQL SERVER 中的文本或图像数据进行输入输出. 不过你可以在MS-DOS方式下执行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. Ifthe 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 loginThe 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 tableThe 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.

为此, 可写一个存储过程,调用这个命令 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), @directionchar(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

下面是一个拷贝图像到SQL Server的pubs数据库的例子, 表名pub_info, 字段名logo，图像文件名picture.bmp，保存到pub_id='0736'记录 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'