May 6, 2004 at 1:19 pm
I have three stored procedures that do the flowing tasks:
1. connect to and add logins for 12 remote (WAN) SQL servers
2. Drop data on the local server and re-add it table at a time from each remote server, one at a time, for 5 tables.
3. Disconnect from the sites.
These stored procedures work fine when run from Query Analyzer, but when I schedule them to run automatically via an agent job, the job fails on procedure #2 which copies the data. I get an error message of "Msg 17, Sev 16: SQL Server does not exist or access denied. [SQLSTATE 42000]" in the error log, which seems like a rights issue to me, but I don't know where to look to correct it. Can anyone point me to any documentation that will explain how the agent executes jobs?
Thanks,
Erik
May 7, 2004 at 9:27 am
Erik,
Are you using the same account to run the job from the Agent as you are from the Query Analyzer? To check, Open EM, check the Job Properties and make sure the job owner is a fully qualified domain account rather than the local system account. Hope this helps.
My hovercraft is full of eels.
May 7, 2004 at 9:51 am
sswords:
Thank you for your reply. Yes, I am running the job from the same account that I use in QA, but it is not a domain account, because the local SQL server is not on a domain. The remote SQL servers are not on a domain either. All machines are standalone. I do have an account with the same username and password on each of the remote SQL servers, though, and that is the account that I am using to run the job.
I have tried adding output to my stored procedure using commands
like EXEC master..xp_cmdshell 'echo server1 copy ok > c:\out.txt' but I
cannot find the output file anywhere, and do not know if it was even
written. Do you know of a better way to write status output so I can figure out where specifically in the copy procedure the job is failing?
Thanks,
Erik
May 7, 2004 at 1:01 pm
Erik,
If you're not using a domain, then the remote server will need the fully qualified local Windows name. Assuming that the remote server has an Admin user named user1 then you'd need to log on from your server with something like:
REMOTESERVERNAME1\user1
If you're doing this for several servers, you'd have to log on to each one specifying the server and user name each time.
As to your output file question, if the job is unable to log in remotely it could be erroring out before the file even gets a chance to create. You can test by exiting to a command prompt and using osql issuing something like:
osql /Usa /P(sa password) /Sservername /ic:\(path and name of your job file) /oc:\joberr.txt
Note that the switches are case sensitive. Hope this helps.
My hovercraft is full of eels.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply