Joining two tables from two different servers

[arodri]

Hello,

I need to join a table in my current server to a table from a linked server but I'm not sure of the sytnax. I've done stored procedures where I've done nested queries and linked tables all from the linked sever side, but I've never had to do both in one query. I was wondering if it was even possible, and if it is, what would the syntax be? I've tried several things but the sql syntax is a little different in the linked server since it's a firebird server.
Here is the stored procedure I'm working off of (this query is pulling from the linked sever) and I need to join a table from my SQL server called "Site" instead of the second query I have here.
So the top Query ("A") will be from the linked server, and the bottom query ("B") will be from my local SQL server, and I will be joining them by the column "Site."

Thanks!

@BeginDate          VarChar(12),
@EndDate            VarChar(12),
@ItemName            Varchar(25)

AS
BEGIN
declare @sql varchar(max)
set @sql='

 Select               
                      SiteName, 
                      Name, 
                      ItemName,
                      sum(Qty) as Qty,
                      sum(Amt) as Amt


FROM OPENQUERY
            ( 
            LinkedServer, ''
            
       Select    
                 B.SiteName,
	         A.Name,  
                 B.ItemName, 
                 B.Qty, 
                 B.Amt
       FROM               

( 
            SELECT    SALE.SITE as Site, 
                      SALEITEMS.ITEM as Item,
                      ITEM.NAME as Name, 
                      Sale.OBJID as SaleID,  
                      SALEITEMS.QTY as Qty, 
                      SALEITEMS.AMT as Amt 

FROM SALE INNER JOIN SALEITEMS ON (SALE.OBJID = SALEITEMS.SALEID) AND (SALE.SITE = SALEITEMS.SITE)
            INNER JOIN ITEM ON (SALEITEMS.ITEM = ITEM.OBJID)
WHERE (SALE.LOGDATE BETWEEN (''''' + CONVERT(char(10), @BeginDate, 101) + ''''') AND (''''' + CONVERT(char(10), @EndDate, 101) + ''''') ) 
AND Item.Name = ('''''+@itemname+''''')

) as A

,

(

            
            SELECT    SALE.SITE as Site, 
                      SITE.SITENAME as SiteName,
                      ITEM.NAME as ItemName,  
                      Sale.OBJID as SaleID,  
                      SALEITEMS.QTY as Qty, 
                      SALEITEMS.AMT as Amt

FROM SALE INNER JOIN SALEITEMS ON (SALE.OBJID = SALEITEMS.SALEID) AND (SALE.SITE = SALEITEMS.SITE)
            INNER JOIN SITE ON (SALE.SITE = SITE.ID)
            INNER JOIN ITEM ON (SALEITEMS.ITEM = ITEM.OBJID)

WHERE (SALE.LOGDATE BETWEEN (''''' + CONVERT(char(10), @BeginDate, 101) + ''''') AND (''''' + CONVERT(char(10), @EndDate, 101) + ''''') ) 
) as B

Where
A.Site = B.site and A.Saleid = B.saleid

 '')
 
 GROUP BY
  SITENAME,
  NAME,
  ITEMNAME
' 
 

Exec(@sql)
END

 

[djj55]

Access the linked table by using the server name

SELECT * FROM [LinkedServer].[database].[schema].[table]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[arodri]

Could you please explain that a little bit more?
I don't need to just access one link table, I need create a query (example above) from a linked server, and link that query to a local SQL table.
How would I use your syntax if I'm joining several tables from my linked server?


Thanks!

 

[djj55]

Using the AdventureWorks2008R2 database here is an example

SELECT E1.[BusinessEntityID]
      ,E1.[NationalIDNumber]
      ,E1.[LoginID]
      ,E2.[JobTitle]
      ,E2.[BirthDate]
-- Local table - the AdventureWorks2008R2 may not be needed for local
FROM [AdventureWorks2008R2].[HumanResources].[Employee] E1 
-- Linked server table 
INNER JOIN [OtherServer\Instance].[AdventureWorks2008R2].[HumanResources].[Employee] E2 
ON E1.[NationalIDNumber] = E2.[NationalIDNumber]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[arodri]

Thank you for that example! I was just doing a really simple test query to make sure it worked, and here's the error I got:

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "stwatch". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.


There's a very real possibility that the database name (MTW) is wrong (nobody can seem to figure out the correct database name), if that's the case, is that the only thing causing this error? In other words, assuming the database name is correct, is my syntax correct as well? Also, "stwatch" is the name of the linked server ...

Here is my query:

Select e1.Sitename, e2.ID

From  [localservername].[MTW].[dbo].[SITE] E1 Inner join 
stwatch.mtw.sale.site e2 ON e1.id=e2.site

Thanks!

 

[djj55]

Try

SELECT TOP 10 * FROM stwatch.mtw.sale.site

if that workers then the names are correct.

I am not much when it comes to error solving, but it looks like the link is not correct.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[arodri]

That didn't work - gave me the same error. Hopefully once I figure out the right name it'll work.

One more question - In this case, when I'm trying to get data from a linked server, why don't I have to use the 'open query' command? I was under the impression that when doing queries with linked servers we need to use the 'open query' command? Is that not the case?

Thanks!

 

[djj55]

The OpenQuery function "Executes the specified pass-through query on the specified linked server". Since you are accessing the table directly you do not have to do it through OpenQuery.

In SSMS can you see the tables in the object explorer? Look under {local instance}/Server Objects/Linked Servers/{linked server}/Catalogs/{database}/Tables the names within {...} should be replace with your names.

Hope this helps,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[arodri]

Thank you for your help!

For some reason I couldn't get it to work with that syntax but I was able to figure it out with the open Query format.