Solved

Stored Procedure to Copy files from one server to another server

Posted on 2004-09-17
7
589 Views
Last Modified: 2011-04-14
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
0
Comment
Question by:CochiseCounty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
7 Comments
 
LVL 9

Accepted Solution

by:
apirnia earned 84 total points
ID: 12087097
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12087128
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
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 83 total points
ID: 12087262
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
 
LVL 5

Assisted Solution

by:ajitanand
ajitanand earned 83 total points
ID: 12111347
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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question