Solved

Modify SQL Stored Procedure to use OPENQUERY

Posted on 2013-05-20
1
477 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
Comment Utility
;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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now