在SQL Server數(shù)據(jù)庫(kù)的安裝目錄下有一個(gè)可執(zhí)行文件叫TEXTCOPY.EXE，使用它可以對(duì)SQL Server數(shù)據(jù)庫(kù)中的文本或圖像數(shù)據(jù)進(jìn)行輸入輸出。假如你對(duì)它不熟悉,可以在MS-DOS方式下執(zhí)行textcopy /? 來(lái)得到它的描述。

以下是這個(gè)工具的描述:

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 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 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.

我們可以寫(xiě)一個(gè)存儲(chǔ)過(guò)程來(lái)調(diào)用這個(gè)命令

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

以下是一個(gè)拷貝圖像到SQL Server的add數(shù)據(jù)庫(kù)的例子, 表名add_info, 字段名

picture，圖像文件名shuijing.bmp，保存到pub_id='0611'記錄 。

sp_textcopy @srvname = 'ServerName',

@login = 'Login',

@password = 'Password',

@dbname = 'add',

@tbname = 'add_info',

@colname = 'picture',

@filename = 'c:shuijing.bmp',

@whereclause = ' WHERE pub_id='0611' ',

@direction = 'I'