Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Is it possible to Copy files from one server to pc using SQL

I am running this batch to copy files, but I would like to perform the same function in SQL.  

Is it possible?  if yes, full credit will be given to expert providing fule TSQL Syntax.

The code below gets the first 3 characters of a file and creates a folder, then copies the files to it's destination.  We have four hospitals and we are sending the files to each hospitals pharmacy pc

@echo off
setlocal enabledelayedexpansion
set Source=C:\Temp\EMARTEST
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| find /n "[BACKUPDATA]"') do set DataStart=%%a
set /a TypeCount = 0
for /f "tokens=1* skip=%DataStart% delims= " %%a in ('type "%~f0"') do (
	set /a TypeCount += 1
	set SourceType[!TypeCount!]=%%a
	set Target[!TypeCount!]=%%b
)
for /l %%i in (1, 1, %TypeCount%) do (
	ECHO robocopy.exe "%Source%" "!Target[%%i]!" !SourceType[%%i]! /mov /r:2 /w:1
)

goto :eof
[BACKUPDATA]
MHG*.* \\10.X.X.20\emarbackup$
ELA*.* \\10.X.X.15\emarbackup$
CHH*.* \\10.X.X.30\emarbackup$
CPH*.* \\10.X.X.35\emarbackup$ 

Open in new window

0
epicazo
Asked:
epicazo
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can save the batch file on the sql server and call the batch in sql srever using xp_cmdShell


TSQL Statement

exec master..xp_cmdShell 'c:\test.bat'

One thing to note, by default xp_cmdShell is disabled, you should enable it in order to run this
0
 
epicazoAuthor Commented:
the problems is that I wont be able to monitor whether the files successfully or not.
0
 
lcohanDatabase AnalystCommented:
You can run the copy itself from SQL command not from the batch like


exec master..xp_cmdShell 'copy c:\test.bat \\new_box\my_folder\'

and you will get the results like:

"The system cannot find the file specified."

You can use even robocopy if it's from windows OS with results like below:

output
NULL
-------------------------------------------------------------------------------
   ROBOCOPY     ::     Robust File Copy for Windows                              
-------------------------------------------------------------------------------
NULL
  Started : Mon Jan 28 14:50:45 2013
NULL
2013/01/28 14:50:48 ERROR 53 (0x00000035) Getting File System Type of Destination \\new_box\my_folder\
The network path was not found.
NULL
   Source : c:\test.bat\
     Dest - \\new_box\my_folder\
NULL
    Files : *.*
     
  Options : *.* /COPY:DAT /R:1000000 /W:30
NULL
------------------------------------------------------------------------------
NULL
2013/01/28 14:50:48 ERROR 2 (0x00000002) Accessing Source Directory c:\test.bat\
The system cannot find the file specified.
NULL
0
 
epicazoAuthor Commented:
I wanted full tsql only and prevent batch, but I could use this for now.

thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now