Solved

OPENQUERY in sql server linked server

Posted on 2014-03-17
7
2,640 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 142

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 142

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 142

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Count where two id types exist in column 8 27
Webservices in T-SQL 3 30
T-SQL: "HAVING CASE" Clause 1 23
transaction in asp.net, sql server 6 31
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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

813 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

11 Experts available now in Live!

Get 1:1 Help Now