create table # (a text)insert # values('asdfasdfiewrhjtoigk;jhdsaloihvbdiouvdsigdfgttyjudgufyufyudrtyesryreyluhglseiuhtldskhglksdhguidsfvbludshgkdhf')DECLARE @ptrval varbinary(16),@len int SELECT @ptrval = TEXTPTR(a),@len=datalength(a)-1 FROM # READTEXT #.a @ptrval 1 @len drop table #
我要把binary变量赋值给text呢?
我在存储过程中建了临进表有Text字段,我想从表中读取text字段的内容到临时表中。
TRY: drop table #a drop table #b create table #a (a text) create table #b (a text)insert #a values('asdfasdfiewrhjtoigk;jhdsaloihvbdiouvdsigdfgttyjudgufyufyudrtyesryreyluhglseiuhtldskhglksdhguidsfvbludshgkdhf') insert #b select a from #a select * from #b
我详细点说 declare @hello text//这个在存储过程中不行 后 declare @hello binary(16) 读取表a的content为Text类型 select @hello=TEXTPTR(content) from a 创建临时表B,有个字段content也为Text类型 insert into 表B(content) values(@hello)//说binary不能转换成Text
SELECT @ptrval = TEXTPTR(a),@len=datalength(a)-1 FROM #
READTEXT #.a @ptrval 1 @len
drop table #
drop table #a
drop table #b
create table #a (a text)
create table #b (a text)insert #a values('asdfasdfiewrhjtoigk;jhdsaloihvbdiouvdsigdfgttyjudgufyufyudrtyesryreyluhglseiuhtldskhglksdhguidsfvbludshgkdhf')
insert #b
select a from #a
select * from #b
declare @hello text//这个在存储过程中不行
后
declare @hello binary(16)
读取表a的content为Text类型
select @hello=TEXTPTR(content) from a
创建临时表B,有个字段content也为Text类型
insert into 表B(content) values(@hello)//说binary不能转换成Text
create table aaa(a int,b text)
goCREATE 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
/* 这是使用textcopy工具将文件插入到数据库中,如果有前台工具可以用前台开发工具将文件插入,这里为了演示 */
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
goinsert aaa values(1,'') --''是必须的不是null
sp_textcopy 'daliserver','sa','element','test','aaa','b','c:\tt.txt','where a=1','I' --注意条件是 a=1
go
drop table aaa
go
drop proc sp_textcopy前提c:\下有tt.txt文件,如果报textcopy不是可执行文件的话,你就到
C:\Program Files\Microsoft SQL Server\MSSQL\Binn
目录下拷备 textcopy.exe到:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn