Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Unable to Initiate Linked Server without User Logged on Expand / Collapse
Author
Message
Posted Thursday, April 1, 2010 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:52 AM
Points: 43, Visits: 45
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.



Post #895194
Posted Thursday, April 8, 2010 5:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #899443
Posted Thursday, April 8, 2010 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:52 AM
Points: 43, Visits: 45
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.

Date 4/1/2010 4:00:00 AM
Log Job History (DownloadUpdates)

Step ID 1
Server S-SQL
Job Name DownloadUpdates
Step Name DownloadFromMAX
Duration 00:00:01
Sql Severity 16
Sql Message ID 7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

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.



Post #899585
Posted Thursday, April 8, 2010 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:52 AM
Points: 43, Visits: 45
Correction - I meant to say that "When I logged onto the server as the job starts."


Post #899587
Posted Thursday, April 8, 2010 11:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #900202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse