Stored Procedure to copy images

Question

Post reply

Stored Procedure to copy images

gisles

SSC Eights!

Points: 808
More actions
May 18, 2010 at 7:43 pm

#221772

I would like to create a stored procedure to read a file path form a table and copy the .jpg to another location. How would I do this? For example.
HouseID
servername/folder/123.jpg
I would like to copy the 123.jpg to another server and folder and rename it with the HouseID.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hi, if your table would not have millions and millions of record, then the following code would suffice your needs!

Code :

1. The test set-up ; Please look at how i set up the ready-to-use code so that people can utilize it and right away start coding your requirement

USE Scratch

GO

IF OBJECT_ID(N'dbo.Files_And_Folders',N'U') IS NOT NULL

DROP TABLE dbo.Files_And_Folders;

CREATE TABLE dbo.Files_And_Folders

(

FID INT IDENTITY(1,1),

SourceFolder VARCHAR(100),

SourceFile VARCHAR(100),

DestFolder VARCHAR(100),

DestFile VARCHAR(100)

);

INSERT INTO dbo.Files_And_Folders

(SourceFolder,SourceFile,DestFolder,DestFile)

SELECT '\\localhost\Test\','a.sql','\\localhost\Dest\','1.sql' UNION ALL

SELECT '\\localhost\Test\','b.sql','\\localhost\Dest\','2.sql' UNION ALL

SELECT '\\localhost\Test\','c.sql','\\localhost\Dest\','3.sql' UNION ALL

SELECT '\\localhost\Test\','d.sql','\\localhost\Dest\','4.sql' UNION ALL

SELECT '\\localhost\Test\','e.sql','\\localhost\Dest\','5.sql' UNION ALL

SELECT '\\localhost\Test\','f.sql','\\localhost\Dest\','6.sql' UNION ALL

SELECT '\\localhost\Test\','g.sql','\\localhost\Dest\','7.sql' UNION ALL

SELECT '\\localhost\Test\','h.sql','\\localhost\Dest\','8.sql'

2. Now for the code; I have utilized Dynamic SQL that executes copy.exe using xp_cmdshell; to learn about them please refer Books Online (free help tha along comes with Microsoft SQL Server; it is available over the internet too)

DECLARE @Query VARCHAR(4000)

SET @Query = ''

SELECT @Query = @Query + 'EXEC xp_cmdshell ''copy "'+SourceFolder+SourceFile +'" "' + DestFolder+DestFile + '"'' ,NO_OUTPUT' + CHAR(10)

FROM dbo.Files_And_Folders

PRINT @Query

--EXEC (@Query)

The above code will print out the execution codes; When u feel that what u wanted to perform, then uncomment the "EXEC" in the code and run the code.

Hope this helps! 😎

Tel us if the code did what you intended!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

T-SQL is not the best solution for this. You could write a .NET program that reads the paths from the database and moves the images or you could do it using PowerShell (uses the .NET framework).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Jack Corbett (5/19/2010)
T-SQL is not the best solution for this. You could write a .NET program that reads the paths from the database and moves the images or you could do it using PowerShell (uses the .NET framework).

Exactly Jack! I had coded a piece of CLR for this! was having some problems executing that due to connection string i guess! I have sought the help of Paul White (afaihs he only posts lot of CLRs). IF he fixes the error, then i shall post the CLR-based solution for this! Thanks for pointing it out , Jack!

gisles SSC Eights! Points: 808 More actions · Answer 4

Thanks, Guys. I'm not sure if I can write a .net program. ColdCoffee that would be great if you could post your solution. I think I'll look at your other solution also.