Creating a csv on the network from SQL job
-
I have found on the forums this code to output a .sql query (I have modifed for my needs):
sqlcmd -S ECOM-VS-01 -i "d:\sqlcode\backorder.sql" -o "\\temp-shed-40\public\backorder.csv" -s"," -h-1 -W
This command is called from a batch file. I want to have this automatically run daily.
I tried running from a SQL job and received the error "the system cannot find the file specified."
I tried running this from a Windows Scheduled task and nothing happened.
Any ideas would be appreciated.
ECOM-VS-01 is the SQL Server running SQL Server 2005.
D: is a local drive to the SQL server.
temp-shed-40 is a network server.
Jason Johnson
-
the issue you are running into is the account SQL server is running under is not an account that has access to teh network. jobs run under the security context of a specific account, and not the person who created the job, or even who is running it manually.
you'll want to go to Control Panel >>Administrative Tools Services>> The specific SQL Server Instance(might be more than one)
You'll want to change the screen below to run under a login you create that would have access to the machine in question...so if you create a new user named SQLAdmin, and that user has access, that user could be used to start up the account and jobs run under that context would perform as expected.
Jason (9/24/2008)
I have found on the forums this code to output a .sql query (I have modifed for my needs):sqlcmd -S ECOM-VS-01 -i "d:\sqlcode\backorder.sql" -o "\\temp-shed-40\public\backorder.csv" -s"," -h-1 -W
This command is called from a batch file. I want to have this automatically run daily.
I tried running from a SQL job and received the error "the system cannot find the file specified."
I tried running this from a Windows Scheduled task and nothing happened.
Any ideas would be appreciated.
ECOM-VS-01 is the SQL Server running SQL Server 2005.
D: is a local drive to the SQL server.
temp-shed-40 is a network server.
Jason Johnson
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I'm not very familiar with Active Directory.
SQL Server is running under a domain account on the network. This account should have access to network resources.
How can I be sure?
Jason
-
Try mapping a drive to the \\temp-shed-40\public\location as the user account (i.e. 'connect using a different user name' and see if you can manually copy files there.
Also, where is your sql file actually stored? Is d:\sqlcode\ a valid location on the server running the SQL agent?
-
I am unable to copy to the \\temp-shed-40 location either as a mapped drive or direct connection as a different domain user.
D: is a local drive to the SQL server and SQL Agent is running on that same server.
?
-
That's your issue, the account running the job needs write access to the location you are writing to.
-
My account, ECOMSA01, is member of Domain Users.
Domain Users has write-access to this network location.
Am I missing something?
-
Since the account that SQL Agent is a member of a group that has write access to the network location, the SQL job should be able to write to the file.
Any ideas?
Jason Johnson
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply