RE: Linked 400 stop working

Ok here is more info... and it gets strange.

I rebooted the SQL server.
 
Tried a couple of jobs and they ran fine and assumed things were all ok.

However this morning a couple of other jobs failed with a 7340 error (
Cannot create a column accessor for OLE DB provider "IBMDASQL" for linked server "linkname".) instead of 7302. Further research showed that those using openquery() succeeded, where as those that simply queried the linked server such as below, failed with the 7340 error (At the time these jobs were created I was unsure if there was a performance difference with the openquery() method. It does not appear to be any significant difference)

FROM Linkname.servername.catalog.table AS derivedtbl_1


I also noticed in the history that these jobs had first failed with 7340 before failing with 7302.

I did some searching and found this link:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277518&wa=wsignin1.0

where a user points out that simply opening the properties on the link 'fixes' the 7340 error. I opened then closed the properties window and the 'broken' jobs started to work. Obviously there is something going on here, but it is a bit mysterious.



--- On Sun, 8/23/09, Adam West <adamster@xxxxxxxxx> wrote:


From: Adam West <adamster@xxxxxxxxx>
Subject: RE: Linked 400 stop working
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: Sunday, August 23, 2009, 2:08 PM







No, I tried with another known working account. Additionally this account is still working on our old SQL2000 machine (that one is ODBC
 
 
 
- On Wed, 8/19/09, Chris Bipes <chris.bipes@xxxxxxxxxxxxxxx> wrote:


From: Chris Bipes <chris.bipes@xxxxxxxxxxxxxxx>
Subject: RE: Linked 400 stop working
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: Wednesday, August 19, 2009, 3:38 PM


The user profile on the iSeries has an expired password or is just
disabled?



Chris Bipes
Director of Information Services
CrossCheck, Inc.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Adam West
Sent: Wednesday, August 19, 2009 8:09 AM
To: Midrange Systems Technical Discussion
Subject: Linked 400 stop working

We have a linked AS400, connected through OLEDB (IBMDASQL). Up until
last Thursday it was working fine. We are now getting the error:

Cannot create an instance of OLDDB provider "IBMDASQL" for linked server
"[link name]" (Microsoft SQL Server, Error 7302)

Now I know that the normal cause of this error is that 'allow inprocess'
is disabled, but that is NOT the case here (and indeed we have been
running just fine til this incident)

What else can possibly cause this problem?