?
Solved

Modify SQL Stored Procedure to use OPENQUERY

Posted on 2013-05-20
1
Medium Priority
?
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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