SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked AS400 stopped working


Linked AS400 stopped working

Author
Message
jay-h
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
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 --
jay-h
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
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
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42893 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
jay-h
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
The AS400 folks have assured me again that no changes have been made.

...

-- FORTRAN manual for Xerox Computers --
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42893 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
jay-h
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
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
pk400i
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 7
Is it possible a Userid or password has been disabled?
jay-h
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
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
jay-h
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 2375
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
aulmer
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 29
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search