We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server. It's been working great for a long time, but over the weekend they upgraded their system, and now one of our jobs is failing.
The linked server still connects fine, meaning that if I look at the server under Server Objects->Linked Servers->servername it shows up, and I can see all the tables.
When I run the query through an OpenQuery it works fine:
Select * From OpenQuery(GLDATA, 'Select WPCPKG, WPCBAC, WPCDEN, WPCPRT from S782D480.GLDATA.GPCKCTL') --This works
But when I use the four part naming that is needed to do an insert into that table, it fails:
select WPCPKG, WPCBAC, WPCDEN, WPCPRT from GLDATA.S782D480.GLDATA.GPCKCTL -- fails, but used to work last week
The error says:
OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - SYSCOLUMNS in QSYS2 type *FILE not found.".
Msg 7311, Level 16, State 2, Line 7
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "MSDASQL" for linked server "GLDATA". The provider supports the interface, but returns a failure code when it is used.
Per this article I've tried dropping and recreating the linked server, and turning "allow inaccess" off and on in the provider, but that didn't work.
The error is vague enough that I'm not finding much information to try, and I suspect that it is a config problem on their end, but unless I give them the solution on a platter they are very slow to fix things.