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 12»»

Linked AS400 stopped working Expand / Collapse
Author
Message
Posted Monday, August 17, 2009 11:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
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?



[fixed typo]


...

-- FORTRAN manual for Xerox Computers --
Post #772114
Posted Tuesday, August 18, 2009 8:23 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
Further info

I've reinstalled the IBM oledb components.

Tried to recreate the linked server and getting the same error.

Tried re-doing the provider



None of the above changed anything.


...

-- FORTRAN manual for Xerox Computers --
Post #772764
Posted Tuesday, August 18, 2009 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 10,215, Visits: 13,165
Have any PTF's been applied to the AS400? If so you may need an updated IBMDASQL driver.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #772992
Posted Tuesday, August 18, 2009 1:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
The AS400 folks have assured me again that no changes have been made.

...

-- FORTRAN manual for Xerox Computers --
Post #773007
Posted Tuesday, August 18, 2009 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 10,215, Visits: 13,165
I had to ask as I'd had this problem in the distant past. Let's see, have you tried OPENQUERY? Can you connect from a .NET application using the IBM OLE DB Provider?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #773021
Posted Tuesday, August 18, 2009 1:36 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
I get the same basic error when using openquery. I don't have a .net making a similar call (and I'm not a .net programmer, so there would be considerable ramp-up to try to create one to test this)



Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDASQL" for linked server "ASCO".


...

-- FORTRAN manual for Xerox Computers --
Post #773030
Posted Wednesday, August 19, 2009 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 20, 2009 12:06 PM
Points: 3, Visits: 7
Is it possible a Userid or password has been disabled?
Post #773728
Posted Friday, August 21, 2009 8:38 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
No, I tried with another known working account. Additionally this account is still working on our old SQL2000 machine (that one is ODBC).



...

-- FORTRAN manual for Xerox Computers --
Post #775116
Posted Thursday, August 27, 2009 9:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:37 AM
Points: 751, Visits: 1,917
Ok here is more info... and it gets strange.

I rebooted the SQL server (ugh, I don't like doing that)

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.





...

-- FORTRAN manual for Xerox Computers --
Post #778405
Posted Thursday, October 8, 2009 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 4, 2014 7:25 AM
Points: 10, Visits: 27
We're suffering from the same error now, albeit on sqlsrv2008 and on 64bit:

Msg 7340, Level 16, State 4, Procedure prcGETDATA, Line 39
Cannot create a column accessor for OLE DB provider "IBMDASQL" for linked server "i5v5r4".


Clicking on the properties of the linked server does indeed fix the problem for a while, which is mysterious, to say the least.

Dropping and recreating the linked server does not get any results, the error perists, so I assume it has got to be something in the way the OLEDB IBMDASQL driver is being initialized or used.

Another way to (temporarily) get rid of the error is to schedule an SSIS package, which transfers a few bits of dummy data in a data flow involving both the iSeries as well as the local SQL Server as data sources, but not using the linked server. This somehow initializes the IBMDASQL driver and gets the linked server working again.

I'm really looking for a way to permanently fix this without having to click on the properties or running dummy packages...
Post #800282
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse