August 11, 2009 at 4:51 am
I have scheduled a job which runs a stored procedure every 30 minutes. In the stored procedure it connects to Oracle Linked server and pulls some data and updates in SQL tables. If I am running this SP, in query window, it never fails. But thru SQL jobs, many times it is running perfectly but sometimes it is giving following error:
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSDAORA" for linked server "" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". [SQLSTATE 01000] (Error 7412). The step failed.
Has anybody faced & resolve this issue...
By Mistake I have posted same thing under 2000 - SQL Agent
August 11, 2009 at 6:04 am
I had the same issue with AS/400 and I noticed that mostly depends on how the provider is loaded.
I suggest you load the provider out of process and always use the same account to perform operations on the remote server. I set up an account specifically for this.
Another thing I can suggest is using OPENQUERY whenever possible.
4-part names sintax works definitely slower: avoid it whenever OPENQUERY is possible.
If none of these advice takes you to a solution, just add a retry count in the job step.
Hope this helps
Gianluca
-- Gianluca Sartori
August 11, 2009 at 6:50 am
Thanks for your prompt reply.
1) I could not understand what U mean by "load the provider out of process".. I tried by unchecking "allow in process" property for the provider MSDAORA. But it gave me following error..
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "PSHRPROD" reported an error. The provider ran out of memory.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "select distinct
2) We are not using 4 part syntax.. We are using openquery only..
3) As per your last suggestion, I have added retry attempts. Will monitor & update on this.
Meanwhile, please provide some information on loading the provider out of process...
Thanks...
August 11, 2009 at 7:06 am
Running the provider out of process is exactly what you did: uncheck allow in process.
The main difference is that when SQLServer loads the provider in process, takes the provider inside its process memory space and does not spawn other processes dedicated to the provider. This worked for me, but does not mean it will work for you.
Regards
Gianluca
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply