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'
@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'
^_^,这可是一种很有用的学习方法哦!
或批处理文件。
NULL
为什么?
另:
请教大力,你往数据库里导文件的时候没有出现错误??
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: FALALY
debug: Login: sa
debug: Password: 123
debug: Database: Test_Data
debug: Table: ImageTable
debug: Column: ImageBin
debug: Where clause: WHERE Id=1
debug: File: F:\Games\Test.xls
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'FALALY' Message 5701: 已将数据库上下文改为 'master'。
SQL Server 'FALALY' Message 5701: 已将数据库上下文改为 'Test_Data'。 (Concerning line 1)
debug: Query: select ImageBin from ImageTable WHERE Id=1
***ERROR: Text or image pointer and timestamp retrieval failed.***
NULL
能解释一下产生错误的原因所在吗??如何解决??
1,文件路径及文件名没有问题
2,如果direction参数为'O'时没有问题
go
insert aa values(1,0x) ---这步你有吗?
gosp_textcopy....
insert aa values(1,0x) 这步我没有做...
:)
现在可以了
再次谢谢大力