Technical Article

Store a file and Retrieve it in SQL Server

,

to access the image sql server account should have permission to the folder.

Please write the image path on openrowset.

also give folder path if you want to retrieve it from sql server.

/* 
Created By : Vimal Lohani on 12-May-2015
*/--Create Table 
Use temp
Go
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

Rate

5 (2)

Share

Share

Rate

5 (2)