[转帖]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 val
ue
is a specified text or image ';column'; of a single row (specified by th
e
"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 n
ot
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 tex
t or
image data. If ';database'; is not specified, the d
efault
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 keyw
ord)
that specifies a single row of ';table';.
/F file The file name.
/I Copy text or image value into SQL Server from ';fi
le';.
/O Copy text or image value out of SQL Server into ';
file';.
/K chunksize Size of the data transfer buffer in bytes. Minimu
m
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),
@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
下面是一个拷贝图像到SQL Server的pubs数据库的例子, 表名pub_info, 字段名
logo，图像文件名picture.bmp，保存到pub_id=';0736';记录 sp_textcopy @srvn
ame = ';ServerName';,
@login = ';Login';,
@password = ';Password';,
@dbname = ';pubs';,
@tbname = ';pub_info';,
@colname = ';logo';,
@filename = ';c:\picture.bmp';,
@whereclause = " WHERE pub_id=';0736'; ",
@direction = ';I';
|
