0

I'm looking to join 2 tables from 2 different databases, the only problem is both databases have the same name (however with very different underlying data structures).

Does anyone know how I could join the DataTrue_Report Db from both of these servers?

I tried fully qualifying the name with the server name and mixing in some use statements but it doesn't want to play well with the two databases.

enter image description here

Query 1: 

SELECT [ClientId] ,
[TPId] ,
[Client_Rolling_Revenue] ,
[TP_Rolling_Revenue] ,
[Client_MTD_Revenue] ,
[TP_MTD_Revenue] 
FROM [DataTrue_Report].[dbo].[SF_REVENUE]

Query 2:

SELECT  [FeeId]
      ,[ChainId]
      ,[SupplierId]
      ,[Fees]
      ,[FeeMode]
      ,[IsNewspaper]
      ,[CalculateOn]
      ,[ActiveStartDate]
      ,[ActiveEndDate]
      ,[ChainIdentifier]
  FROM [DataTrue_Report].[dbo].[SBTServiceFees]

When connected to database 1, the 2nd query says no object found and vice versa.

tc_NYC
  • 192
  • 1
  • 2
  • 11
  • 2
    You have to use a linked server when querying across instances. https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine And just saying things like "doesn't play well" is not a good description of the problem. The error message would provide information. Also, the query you are trying would help. – Sean Lange Dec 05 '17 at 14:23
  • Query 1: SELECT [ClientId] ,[TPId] ,[Client_Rolling_Revenue] ,[TP_Rolling_Revenue] ,[Client_MTD_Revenue] ,[TP_MTD_Revenue] FROM [DataTrue_Report].[dbo].[SF_REVENUE] – tc_NYC Dec 05 '17 at 14:31
  • Query 2: SELECT [FeeId] ,[ChainId] ,[SupplierId] ,[Fees] ,[FeeMode] ,[IsNewspaper] ,[CalculateOn] ,[ActiveStartDate] ,[ActiveEndDate] ,[ChainIdentifier] FROM [DataTrue_Report].[dbo].[SBTServiceFees] – tc_NYC Dec 05 '17 at 14:31
  • The first query says invalid object name, when connected to the 2nd server. The two servers are linked, the problem seems to be that the engine doesn't go and try to find the table on the other server. – tc_NYC Dec 05 '17 at 14:33
  • 1
    Can you put those queries in your question? The comments suck for formatting. But neither of those queries is using a linked server. – Sean Lange Dec 05 '17 at 14:35
  • 3
    When querying over a linked server you have to use 4 part naming. [LinkedServer].[Database].[schema].[Table]. You are only using 3 part naming. – Sean Lange Dec 05 '17 at 14:43
  • 1
    Possible duplicate of [Joining tables from different servers](https://stackoverflow.com/questions/8752477/joining-tables-from-different-servers) – Tab Alleman Dec 05 '17 at 14:46

0 Answers0