Modify SQL Stored Procedure to use OPENQUERY
Posted on 2013-05-20
I have a SP that no longer works with the remote DB2 database after a updated. I have found that I can query the SQL linked server with OPENQUERY and not simple select statements. Can anyone assist in how this SP can be modified to work with OPENQUERY?
UPDATE ap_invoice SET Check_Number = v.CHECK_NUMBER, Check_Date = v.CHECK_DATE
from ap_invoice i
inner join LINKEDSERVER.database.schema.table v on i.INVOICEKEY = v.INVOICEKEY
where i.Check_Number = '0'
for reference here are my test select statements...
select * from OPENQUERY (LINKEDSERVER, 'select * from schema.table') --- This works fine
Select * from LINKEDSERVER.database.schema.table -- this returns "OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - SYSCOLUMNS in QSYS2 type *FILE not found.".