Solved

OPENQUERY in sql server linked server

Posted on 2014-03-17
7
2,704 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 250 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 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