SQLTeam.com | Weblogs | Forums

Select Query with where exists clause on a linked server


#1

I am trying to run a query over two servers.
Server 1 is running the query

the query I have so far is
SELECT a.[ReportId]
,a.[UserNumber]
,a.[XMLText]
,a.[GenerationDate]
,a.[ReportType]
,a.[DownloadDate]
, 0 as Processed
FROM [SERVER2].[DB1].[dbo].[Reports] a
WHERE DATEDIFF(d, a.DownloadDate, Getdate()) = 0

Server 1 data
SELECT [statuscode]
,[new_sun]
FROM [MSCRM].[dbo].[new_Oaccounts]

What I need to do is only return the records From [SERVER2].[DB1].[dbo].[Reports where
1 a.[UserNumber] exists in new_Oaccounts.new_sun and new_Oaccounts.statuscode = 1

or if someone has a better way of getting the relevant records , I'm all ears. The Query MUST run on Server 2


#2
SELECT a.[ReportId]
,a.[UserNumber]
,a.[XMLText]
,a.[GenerationDate]
,a.[ReportType]
,a.[DownloadDate]
, 0 as Processed
FROM [SERVER2].[DB1].[dbo].[Reports] a
JOIN MSCRM.dbo.new_Oaccounts o ON a.UserNumber=o.UserNumber
WHERE DATEDIFF(d, a.DownloadDate, Getdate()) = 0

#3

Hi Jotorre,
Thanks for the help. I had come up with something similar, however there are complications

  1. there can be multiple records in new_Oaccounts with the same user_number
    Running a straight query on reports I get 165 records, however the join query returns duplicate records (1777) records

I am only looking to return the records from Reports where

  1. the downloaddate is the current date
    2 new_oaccounts contains a matching userNumber and that record I'm Oaccounts has a status record of 1

#4
SELECT a.[ReportId]
,a.[UserNumber]
,a.[XMLText]
,a.[GenerationDate]
,a.[ReportType]
,a.[DownloadDate]
, 0 as Processed
FROM [SERVER2].[DB1].[dbo].[Reports] a
JOIN (SELECT UserNumber MSCRM.dbo.new_Oaccounts GROUP BY UserNumber) o ON a.UserNumber=o.UserNumber
WHERE DATEDIFF(d, a.DownloadDate, Getdate()) = 0

#5

I think you are going to struggle with that if you have a JOIN to a table on the local server. There is a risk that SQL will pull all the data locally and then do the JOIN.

I doubt SQL is going to treat that as SARGable, which may compound the problem.

Try changing that to

WHERE a.DownloadDate = DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

If [DownloadDate] has datatype DATE, rather than DATETIME, then just use

WHERE a.DownloadDate = CONVERT(Date, GetDate())

How many rows in MSCRM.dbo.new_Oaccounts (with Status=1)? If small enough I would generate a list of the UserNumber and include that in a query using OpenQuery() which will force it to run on SERVER2