Stored Procedure to Copy files from one server to another server

I have files in intranet site that I want to copy to internet site.
Here is my stored procedure. when I execute it, it says the path not found. I know that it couldn't fine the path of the internet site, it is \\ccinter\InetPub\wwwroot\BOS\MeetingMinutes\This is a test file.htm. But I don't know how to change the code. Please help. thanks

CREATE PROCEDURE [sp_Minute] AS
DECLARE @IntranetFile varchar(255),@InternetFile varchar(255),@cmd varchar(255)

SET @IntranetFile = '"C:\InetPub\wwwroot\This is a test file.htm"'
SET @InternetFile = '"\\internet\InetPub\wwwroot\This is a test file.htm"'

select @cmd = 'move ' + @IntranetFile + ' ' +  @InternetFile


        exec master..xp_cmdshell  @cmd
CochiseCountyAsked:
Who is Participating?
 
apirniaCommented:
Just a note:

the stored proc can only see local drives to the SQL Server, for drives on other PC it would be an UNC path (\\workstation\C$\), for which the (admin) permissions need to be given
0
 
Eugene ZConnect With a Mentor Commented:
make sure the NT account that you r using has NTFS permissions on souce and destination folders


When you go in start->run on sql server and type \\internet\InetPub\wwwroot\
can you open the folder?
0
 
ajitanandConnect With a Mentor Commented:
I believe the stored proc is getting in trouble because of spaces in the file name.

The stored procedure will work if you specify the filename in an old fashioned format as:

SET @IntranetFile = '"C:\InetPub\wwwroot\Thisis~1.htm"'
SET @InternetFile = '"\\internet\InetPub\wwwroot\Thisis~1.htm"'

-------------------------------
Just give a try and let me know if that helps.

Another reason could be NTFS permissions/sharename. To fix that you can do this:

Step 1. Map the share name (\\internet\InetPub\wwwroot\) as a Network Drive (say H:), and specify alternate the username/password of a user who has rights to access the folder while mapping the drive.

Step 2. Move the file using the location as:
SET @InternetFile = '"H:\This is a file.htm"'
or
SET @InternetFile = '"H:\Thisis~1.htm"'

-------------------------------------------
rgds,
Ajit Anand

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.

All Courses

From novice to tech pro — start learning today.