April 1, 2010 at 12:25 pm
I admit that this is new to me.
I have a SQL Server 2005, where I created a linked server to an Oracle database(Remote). I can view the catalogs on the remote. I have a job set up, to initiate the linked server, to download certain records at night.
While I am logged onto the server, sometimes even if I am "disconnected", the jobs just fine.
However, If I log off the server, the job will launch, but within 1 second, it fails, with error message saying
"Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server..."
The SQL Agent is running as a domain user account.
Never seen this before. Any help you can offer is appreciated.
April 8, 2010 at 5:54 am
YCH (4/1/2010)
"Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server..."
post the complete error and query you are using in job.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 8, 2010 at 8:00 am
Ok. Here's the complete error message. Remember, if I am logged onto the job as the jobs starts, the job would finish fine, without any error at all.
Date4/1/2010 4:00:00 AM
LogJob History (DownloadUpdates)
Step ID1
ServerS-SQL
Job NameDownloadUpdates
Step NameDownloadFromMAX
Duration00:00:01
Sql Severity16
Sql Message ID7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: PNPS\sqlservice. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MAX". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSDASQL" for linked server "MAX" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". [SQLSTATE 01000] (Error 7412) OLE DB provider "MSDASQL" for linked server "MAX" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". [SQLSTATE 01000] (Error 7412) OLE DB provider "MSDASQL" for linked server "MAX" returned message "[Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener". [SQLSTATE 01000] (Error 7412). The step failed.
April 8, 2010 at 8:01 am
Correction - I meant to say that "When I logged onto the server as the job starts."
April 8, 2010 at 11:47 pm
I havent got much help from these deatils but it looks like the agent user doen't have access to the linked server.
Also refer this http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply