'MSDASQL' error on linked servers

[hanzelmans]

I've seen several posts on linking an AS/400 to a SQL 2000 server.

I have created the link, I can use DTS packages to get data to/from the AS/400 to the SQL Server. However, I cannot write any SQL statements against the AS/400. Here is a basic one that doesn't work...

Select * from openquery(jdedwardspy, 'Select * from mhscrp.f0006')

If you look quickly enough, you can see that it does return a row or so, but then that is replaced by this error...


[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]

We're running Client Access V5R2, SQL Server 2000 SP3 and MDAC 2.7.1.

Any suggestions?

[rmiao]

Which ole db provider did you use? Did you install Client Access on sql server?

[hanzelmans]

Yes, we have the Client Access V5R2 on the server.

Is there something specific in the DSN settings that I should be watching for? I saw on another forum that I need to turn on journaling for that table. That doesn't sound correct to me, but I could be wrong.

Thanks,
Steve

[MAK]

I believe
something like "two phase commit" "XA" support and "msdtc" should be enabled in your DSN driver setting.

Something similar to the below link (this is mainframe DB2 and sql server)

http://www.databasejournal.com/featu...le.php/1756161

[rmiao]

Did you try Microsoft's ole db provider for odbc drivers?