如何使用textcopy.exe工具对SQL Server中的文本或图像数据进行导入和 导出,你可以在包含标准的SQL Server可执行文件的目录中找到这个工具 (SQL Server 6.5默认在c:\mssql\binn,SQL Server 7.0默认在c:\mssql7\binn )。 Textcopy 工具在SQL Server的在线手册中没有介绍,不过你可以在命令行中执行 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] [/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.你可以使用下面的存储更简单的使用这个工具: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 @srvname = 'ServerName', @login = 'Login', @password = 'Password', @dbname = 'pubs', @tbname = 'pub_info', @colname = 'logo', @filename = 'c:\picture.bmp', @whereclause = " Where pub_id='0736' ", @direction = 'I'
如何使用textcopy.exe工具对SQL Server中的文本或图像数据进行导入和
导出,你可以在包含标准的SQL Server可执行文件的目录中找到这个工具
(SQL Server 6.5默认在c:\mssql\binn,SQL Server 7.0默认在c:\mssql7\binn
)。
Textcopy 工具在SQL Server的在线手册中没有介绍,不过你可以在命令行中执行
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] [/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.你可以使用下面的存储更简单的使用这个工具: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 @srvname = 'ServerName',
@login = 'Login',
@password = 'Password',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " Where pub_id='0736' ",
@direction = 'I'