Copy external files to different locations

Hi,

I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.

I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.

Many thanks in advanceMark,

this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.

As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).

You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.

Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.

Regards,

hmscott|||Thanks for getting back to me.

What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.

I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations

To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.

If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.

Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.

Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).

Regards,

hmscott

Copy Database Wizard Error- SQL Server Agent cannot execute the SSIS package

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Where did you run your package?|||The Copy Database Wizard ran the SSIS package automatically, at the end of the wizard.|||The Copy Database Wizard ran the SSIS package automatically.|||Oops. I thought you were using transfer database task in SSIS. I think you are at the wrong forum. I assume you initiated copy database wizard from sql management studio.Try posting your issue at the right forum:
SQL Management studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597172&SiteID=1
SMO: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

Also, before doing that you may want to check if both the source and destination servers support remote connections.|||Thanks for pointing me to the fourm. Yes I am using Management Studio for the Copy Database Wizard. Could you please tell me how I can check to see if the source and the destination servers allow remote connections? Is that a Windows Server2003 setting or a SQL Server2005 setting?
Thanks!|||1. Open "SQL Server 2005 Surface Area Configuration" (in Programs-> Microsoft SQL Server 2005 -> Configuration Tools)
2. Click on "Surface Area Configuraiton for Services and Connections"
3. Select "Remote Connections" option for the instance you are interested in.
4. Select "Local and Remove connections"

In SQL Server Management studio, right click on the instance, select properties, go to connections page and select "Allow remote connections tot his server".

I do not know what the default settings are. So, check both.|||When I click on Remote Connections, I get a message saying"You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services"

Do you know how/where I can do that?
Thanks again!

Copy Database Wizard Error

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An

error has occurred while establishing a connection to the server. When

connecting to SQL Server 2005, this failure may be caused by the fact

that under the default settings SQL Server does not allow remote

connections. (provider: Named Pipes Provider, error: 40 - Could not

open a connection to SQL Server)

Have you verified that both servers have the remote connections allowed. Have a look at this KB Article. Remember that Remote connections is turned off by default on new installes of sql server 2005.

|||Do you know how I can set SQL Server2005 to allow remote connections on a cluster?