Linked AS400 stopped working

  • jay-h

    SSCoach

    Points: 18801

    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 "" (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 --

  • jay-h

    SSCoach

    Points: 18801

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

  • Jack Corbett

    SSC Guru

    Points: 184296

    Have any PTF's been applied to the AS400? If so you may need an updated IBMDASQL driver.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • jay-h

    SSCoach

    Points: 18801

    The AS400 folks have assured me again that no changes have been made.

    ...

    -- FORTRAN manual for Xerox Computers --

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • jay-h

    SSCoach

    Points: 18801

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

  • pk400i

    SSC-Addicted

    Points: 409

    Is it possible a Userid or password has been disabled?

  • jay-h

    SSCoach

    Points: 18801

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

  • jay-h

    SSCoach

    Points: 18801

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

  • aulmer

    SSC Enthusiast

    Points: 104

    We're suffering from the same error now, albeit on sqlsrv2008 and on 64bit:

    [font="Courier New"]

    Msg 7340, Level 16, State 4, Procedure prcGETDATA, Line 39

    Cannot create a column accessor for OLE DB provider "IBMDASQL" for linked server "i5v5r4".[/font]

    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...

  • tim.hulse

    SSC Veteran

    Points: 257

    Thanks! Worked like a charm.

  • Rick Fisher-471776

    Valued Member

    Points: 67

    Does anyone know if this was ever resolved? I can consistantly reproduce the error with 2008 R2 by restarting the server, and resolve by opening the Linked Server Object window with Management Studio. I am using IBMDA400 on a non-clustered server if these are significant.

    The system (not clustered) is being used for manually initiated tests only, so I do not know if the error could occur at other times.

    Rick Fisher

  • jay-h

    SSCoach

    Points: 18801

    The highly questionable 'technique' of opening the properties worked. Since that time the issue has not recurred.

    ...

    -- FORTRAN manual for Xerox Computers --

  • Rick Fisher-471776

    Valued Member

    Points: 67

    thanks for response. did you mean management studio "open", or one of the 'code' suggestions. IF Micorsoft or IBM will ne resolve, I need an automatic solution, either at startup or perhaps after error detected.

  • John Cooper

    SSC-Addicted

    Points: 454

    Did you ever find a working solution for this? I am having the same issue 🙁

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply