?
Solved

OPENQUERY in sql server linked server

Posted on 2014-03-17
7
Medium Priority
?
2,805 Views
Last Modified: 2014-03-19
Hi Experts,

Please help me how to use OPENQUERY command in below query to fetch remote server data.

SELECT distinct PRA.inq_num,[PO Number]
FROM [localDB].dbo.ps_requests_asr PRA with(nolock) left join [PRODLinkedServer\DB1].[DB1].dbo.CPOA with(nolock) ON cast(Inquiries as NVARCHAR(450)) = '[' + PRA.inq_num + ']'
WHERE PRA.inq_num is not null
0
Comment
Question by:sqldba2013
[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
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39936251
why do you want to use openquery?
this should do it, not sure
 SELECT distinct PRA.inq_num, db1.[PO Number]
FROM [localDB].dbo.ps_requests_asr PRA with(nolock) 
left join openquery( [PRODLinkedServer\DB1], 
   'select [PO Number], Inquiries  
      FROM [DB1].dbo.CPOA  '
 ) db1
    ON cast(db1.Inquiries as NVARCHAR(450)) = '[' + PRA.inq_num + ']'
WHERE PRA.inq_num is not null  

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39936299
Thanks Guy Hengel for your help.

The above query is working perfect. Could you please advise how to add openquery command for below script (the below query is part of my SP).

;WITH temp AS (
            SELECT cast(Inquiries as NVARCHAR(450)) As Inquiries,[PO Number], max(Revision) AS MaxRevision
            FROM [PRODLinkedServer].[DB1].dbo.CPOA CPON WITH(NOLOCK)
            INNER JOIN #temp on cast(CPON.Inquiries as NVARCHAR(450)) = '[' + #temp.[Inquiry Number] + ']'
            and  CPON.[PO Number]=#temp.[CPO Number] GROUP by cast(Inquiries as NVARCHAR(450)),[PO Number])
            
            
UPDATE      #temp SET [SO Number]= CUST.[SO] FROM #temp INNER JOIN  [PRODLinkedServer].[DB1].dbo.CPOA CPON WITH(NOLOCK) ON cast(CPON.Inquiries as NVARCHAR(450)) = '[' + #temp.[Inquiry Number] + ']'          
            INNER JOIN [PRODLinkedServer].[DB1].[BCAMDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE #temp.[CPO Number] + '%'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39936423
can you please explain the use of the WITH TEMP AS (cte expression), as you don't seem to use that in the update ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sqldba2013
ID: 39936475
Hi Again,

Please find the attached full script and help me to add openquery command.
script.sql
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 39936504
before working, I please request you to answer:
* why do you want to use openquery?
* why do you put those ;WITH .... statements, they are not used?


in regards to the last update:

 UPDATE	t 
   SET [SO Number]= CUST.[SO] 
FROM #temp t
INNER JOIN OPENQUERY([PRODLinkedServer\DB1], 'select Inquiries FROM [db1].dbo.CPOA   ' ) CPON
   ON cast(CPON.Inquiries as NVARCHAR(450)) = '[' + t.[Inquiry Number] + ']'    	
INNER JOIN OPENQUERY([PRODLinkedServer\DB1], 'select [SO], [Customer PO] from [db1].dbo.BI_CUSTMON ' ) CUST 
  ON CUST.[Customer PO] LIKE t.[CPO Number] + '%' 

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39936580
Hi,

<<* why do you want to use openquery?>>
When I run query with below format, the given query is taking very long time.

select table_name from linked_server_name.DB_Name.Table_name

But, when I use openquery command, this will give very quick output.

select table_name from openquery(........).

To avoid performance issue, I want to use openquery command in given script.

<<why do you put those ;WITH .... statements, they are not used?>>
The given query is giving correct output as per my requirement and I don't want to modify existing logic of query.

I am new to TSQL scripting and I don't have in depth knowledge on this and need your suggestion/help on this and I will increase points from 250 to 500 for your help.

Thanks in advacnce,
0
 

Author Closing Comment

by:sqldba2013
ID: 39939246
--
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

764 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