OPENQUERY in sql server linked server

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
sqldba2013Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sqldba2013Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sqldba2013Author Commented:
Hi Again,

Please find the attached full script and help me to add openquery command.
script.sql
0
 
sqldba2013Author Commented:
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
 
sqldba2013Author Commented:
--
0
All Courses

From novice to tech pro — start learning today.