Post reply

Stored Procedure to copy images

  • 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

  • May 19, 2010 at 5:41 am

    #1169340

    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!

  • May 19, 2010 at 1:48 pm

    #1169706

    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

  • May 19, 2010 at 8:25 pm

    #1169834

    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!

  • May 19, 2010 at 8:52 pm

    #1169838

    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.

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

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