Store a file & Retrive it in Sql Server


/* 
Created By : Vimal Lohani on 12-May-2015
*/
--Create Table 
create table #FileSaveTest (Files varbinary(max), name varchar(200))





--Insert file & information
insert into #FileSaveTest (Files, name)
select img.*, 'abc.jpg'
from openrowset(bulk 'D:\b\abc.jpg', Single_Blob) img

--Show table data
select *
from #FileSaveTest







--Configure advance options
sp_configure 'show advanced options', 1;
go

reconfigure;
go

sp_configure 'Ole Automation Procedures', 1;
go

reconfigure;
go

--Retrieve file at a location (Path)   
declare @FileData varbinary(max);

select @FileData = (
  select convert(varbinary(max), Files, 1)
  from #FileSaveTest
  );

declare @Path nvarchar(200)

select @Path = 'D:\a a\';

declare @Filename nvarchar(1024);

select @Filename = (
  select name
  from #FileSaveTest
  );

declare @FullPathToOutputFile nvarchar(2048);

select @FullPathToOutputFile = @Path + '\' + @Filename;

declare @ObjectToken int

exec sp_OACreate 'ADODB.Stream', @ObjectToken output;

exec sp_OASetProperty @ObjectToken, 'Type', 1;

exec sp_OAMethod @ObjectToken, 'Open';

exec sp_OAMethod @ObjectToken, 'Write', null, @FileData;

exec sp_OAMethod @ObjectToken, 'SaveToFile', null, @FullPathToOutputFile, 2;

exec sp_OAMethod @ObjectToken, 'Close';

exec sp_OADestroy @ObjectToken;



















sp_configure 'Ole Automation Procedures', 0;
go

reconfigure;
go

sp_configure 'show advanced options', 0;
go

reconfigure;
go

--Drop table
drop table #FileSaveTest

No comments:

Post a Comment