Solved

Modify SQL Stored Procedure to use OPENQUERY

Posted on 2013-05-20
1
483 Views
Last Modified: 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.".
0
Comment
Question by:acconsultant
1 Comment
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 39182779
;with cte_oq as (select * from OPENQUERY (LINKEDSERVER, 'select * from schema.table'))

UPDATE ap_invoice SET Check_Number = v.CHECK_NUMBER, Check_Date = v.CHECK_DATE
      from ap_invoice i
      inner join cte_oq v on i.INVOICEKEY = v.INVOICEKEY
      where i.Check_Number = '0'
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question