November 12, 2002 at 6:51 pm
I have a SQL Server 2000 Enterprise Edition running on the Windows 2000 Advanced Server with the latest patches. One Developer had given me a database to migrate to my Server and he has a 2 Stored Procs which connect to a Database in US through a leased line. This connection is apparently slower and the proc could take anywhere between 10 to 40 min to execute. The SP works fine when run from the Query Anaylser (sa account), but the Scheduled Job fails with the Error
"OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312). The step failed."
The funny part is that after executing the SP in Query Analyser, it works fine when I manually start tje Job. But fails at other times. Could someone shed somelight into this.
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
November 13, 2002 at 4:40 am
How is you connectio to the Oracle server defined in the Job? Have you tried executing the Job manually qithout doing QA first? Could be you connection has problems with Agents permissions in someway or the connection timeout or some other latency issue is causing this.
November 13, 2002 at 5:53 am
It is probably a connection timeout, the connection is established when you run in QA and then run the Job.
Increase the timeout.
The other option is the security of the linked server. How is it confirgured. If you start the job manually it can run under your account, whereas when it does it automatically it runs under the account of the SQL Agent. Check the SQL Agent service account has access to see the server over the leased line. not sure how Oracle works in terms of netbios and tcp/ip
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 5:58 pm
Hi,
Thanks for the reply.
When I execute the Job, it fails. After I execute it from QA (it works) and then if I execute the Job from EM immediately, it works. After sometime executing the Job from EM does not work.
As for the Security, for any undefined accounts I have specifed an account to use.
I have now scheduled the Job to run every 3 hours on another test database and I reconfigured the Step in such a way as to retry the Job 5 times if it fails. Looks like the job had failed but suceeded in the subsequent attempts. So this could be a Timeout Error. From what I heard from the DB guys, they told me that during a particular time there was too much of activity on the particular database and so have asked us to use a different timing.
I have changed the remote login timeout value from 20 to 40. Will wait for another day to see how the Job is being Executed and update this post.
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
November 17, 2002 at 9:23 pm
Here is the Update. I have scheduled the Job to run at 9 AM with a 5 retry attempts when the Job Fails.
When I view the History Information, it fais the first time (always) with the following message
OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312)
at 9:01 Am
OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312). The step succeeded.
But it says the Step Suceeded. The data has been populated. But why the error message.
If you have any ideas please let me know. I am not sure if I can close this issue or if I should keep working on this to eliminate that error message also in the retry attempt.
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply