Re: T-SQL to write a BLOB to disk

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: oj (nospam_ojngo_at_home.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 10:33:27 -0800

You can use TextCopy.exe in the \bin folder.

-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder 

--
------------------
--TEXTCOPY IN
------------------
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @sql varchar(255),
 @fname varchar(100),
 @path varchar(50),
 @user sysname,
 @pass sysname
set @user='myuser'
set @pass='mypass'
--specify desired folder
set @path='c:\winnt\'
set @sql='dir ' + @path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @fname
while @@fetch_status=0
begin
 set @sql='textcopy  /s"'+@@servername+'" /u"'+@user+'" /p"'+@pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @fname + '''"'
 set @sql=@sql + ' /f"' + @path + @fname + '" /i' + ' /z'
 print @sql
 exec master..xp_cmdshell @sql ,no_output
 fetch next from cc into @fname
end
close cc
deallocate cc
go
select * from tmp
go
-------------------
--TEXTCOPY OUT
-------------------
declare @sql varchar(255),
 @fname varchar(100),
 @path varchar(50),
 @user sysname,
 @pass sysname
set @user='myuser'
set @pass='mypass,'
--specify desired output folder
set @path='c:\tmp\'
set @sql='md ' + @path
--create output folder
exec master..xp_cmdshell @sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @fname
while @@fetch_status=0
begin
 set @sql='textcopy  /s"'+@@servername+'" /u"'+@user+'" /p"'+@pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @fname + '''"'
 set @sql=@sql + ' /f"' + @path + @fname + '" /o' + ' /z'
 print @sql
 exec master..xp_cmdshell @sql ,no_output
 fetch next from cc into @fname
end
close cc
deallocate cc
set @sql='dir ' + @path + '*.bmp /c /b'
exec master..xp_cmdshell @sql
go
drop table tmp
go
-- 
-oj
http://www.rac4sql.net
"Alex Thomas [DIDG]" <alex@didg.com> wrote in message
news:1A423727-86CC-462E-AD31-ABF1E88CF164@microsoft.com...
> Thanks Stefan.
>
> A great idea, but in this particular scenario performance (ie
responsiveness) is the prime requirement, transactions volumes are miniscule
and contention not an issue. I would add your suggestion as comments into
the sp so that in the future it could be refactored, but I would still
prefer the immediacy (and relative simplicity) of a T-SQL solution if
possible.
>
> That's the challenge.

Relevant Pages

  • Re: Insert into Image field
    ... You can use TextCopy.exe in the \bin folder. ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: How to store/retrieve image/file in sql db
    ... You can use TextCopy.exe in the \bin folder. ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: t-sql to insert/update image from file
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: BLOB
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.programming)
  • Re: How to create file using field from a table.
    ... -- exporting files out to folder ... exec master..xp_cmdshell @sql ... for select fname from tmp ... fetch next from cc into @fname ...
    (microsoft.public.sqlserver.server)