Please visit our sponsor
UNKNOWN
--**************************************
-- Name: Procedure to Export/Import Images In/From SQL SERVER
-- Description:the procedure is used as an Interface to the TextCopy Utility supplied with SQL SERVER in order to simplify handling of BLOBS (images/docs)
-- By: Eli Leiba
--
--
-- Inputs:@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server to load
@db varchar(50), -- database to load
@usrvarchar(50), -- login user
@pwdvarchar(50), -- login password
@tblvarchar(50), -- table to load/unload
@colvarchar(50), -- column to load/unload
@whrvarchar(200), -- where clause
@filvarchar(100), -- filename including path
@modchar(1) -- I for Load into Sql
--
-- Returns:none
--
--Assumes:-- usage (assuming TextCopy.exe is in c:\)
create table pic (pic_id int,picture image)
insert into pic values (1,null)
update pic set picture = 'xx'
-- Inserting image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
-- 'PCN1943',
-- 'PUBS',
-- 'sa',
-- 'sa',
-- 'pic',
-- 'picture',
-- '"where pic_id = 1"',
-- 'c:\pic.jpg',
-- 'I'
-- Extracting Image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
-- 'PCN1943',
-- 'PUBS',
-- 'sa',
-- 'sa',
-- 'pic',
-- 'picture',
-- '"where pic_id = 1"',
-- 'D:\pic.jpg',
-- 'O'
--
--Side Effects:Import/Export of BLOBS
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.498/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************
Create Procedure sp_imp_exp_images
(@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server to load
@db varchar(50), -- database to load
@usrvarchar(50), -- login user
@pwdvarchar(50), -- login password
@tblvarchar(50), -- table to load/unload
@colvarchar(50), -- column to load/unload
@whrvarchar(200), -- where clause
@filvarchar(100), -- filename including path
@modchar(1)) -- I for Load into Sql , O FOR output from SQL
AS
declare @cmd varchar(1000)
set @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' +
-- @col + ' /W ' + @whr +
-- ' /F ' + @fil+ ' /' + @mod
exec Master..xp_cmdShell @cmd
GO