Post reply

running dos copy command from select statement

  • August 17, 2012 at 5:45 am

    #392973

    Here is my Query :

    --select item_number from item where item_number like '%Test%'

    For each item_number i would like to copy a .bmp file from the server and copy to my local drive.

    -- copy \\server1\Images\<Item_number>.bmp c:\temp

    Is it possible to run the above command in select statement somehow ?

  • August 17, 2012 at 6:02 am

    #1527041

    You may use dynamic query and xp_cmdshell, smth like this:

    --be sure you have permissions from DBA, and configured server:

    /*

    exec sp_configure 'show advanced options', 1

    reconfigure with override

    exec sp_configure 'xp_cmdshell', 1

    reconfigure with override

    */

    declare @sql nvarchar(max);

    declare @files table (fname sysname);

    insert @files values ('1'),('2'),('3');

    set @sql = (

    select replace('exec sys.xp_cmdshell ''copy \\server1\Images\<Item_number>.bmp c:\temp'';','<Item_number>',fname)

    from @files for xml path('')

    )

    print(@sql);

    --exec(@sql);

    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

Viewing 2 posts - 1 through 1 (of 1 total)

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