Solved

OPENQUERY in sql server linked server

Posted on 2014-03-17
7
2,587 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

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

Creating and Managing Databases with phpMyAdmin in cPanel.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

19 Experts available now in Live!

Get 1:1 Help Now