0

I'm trying to read data out of a from a linked server and the join is bringing back only the first match. If I ran the query on the directly on the remote server I'd get multiple rows back. Both the local and remote servers are Azure Managed Instances. @@Version shows:

Microsoft SQL Azure (RTM) - 12.0.2000.8   May 15 2020 00:47:08   Copyright (C) 2019 Microsoft Corporation

The query is as follows:

DECLARE @Id INT = 15;

SELECT 'LinkedServerMI lnkSvr w/join' fromSrc, *
FROM LinkedServer.DatabaseName.dbo.CustomerInfoPhone cip
   INNER JOIN dbo.CustomerInfo ci ON cip.CustomerInfoId = ci.CustomerInfoId -- Simple join
WHERE ci.CustomerInfoId = @Id; -- No other filters
-- One row comes back, expected 2 or more

SELECT 'LinkedServerMI lnkSvr select only' fromSrc, *
FROM LinkedServer.DatabaseName.dbo.CustomerInfoPhone cip
WHERE cip.CustomerInfoId = @Id;
--For the customer id in question 2 rows come back.

The Linked Server is created like so:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'SQL', @provider=N'MSOLEDBSQL', @datasrc=N'<servername>', @provstr=N'Data Source=<servername>;User ID=<remoteuser>;ApplicationIntent=ReadOnly'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser='<remoteuser>',@rmtpassword='########';

Query plan was to big to share. This link should allow you to see the file hosted on Google: Linked Server Query Plan

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
user2197446
  • 1,065
  • 3
  • 15
  • 31
  • 1
    Try changing your INNER JOIN to a LEFT JOIN. If you get multiple rows back, you may have a data integrity issue between your CustomerInfoId columns. – critical_error Jul 01 '20 at 21:22
  • 1
    Run linked server select only query next to `CustomerInfo ` select only query for the same ID, see how many results you get from both queries. – Alex Jul 01 '20 at 23:32
  • LEFT JOIN still brought back only 1 result. RIGHT JOIN brought back all results. Switching the base table, order that the tables appear in the query brought back all results. – user2197446 Jul 02 '20 at 15:22
  • Selecting against CustomerInfo brought back the expected 1 row/result set. – user2197446 Jul 02 '20 at 15:24

0 Answers0