Select Query across 3 table and 2 servers

I'm trying to create a stored procedure that retrieves data from 3 tables on 2 different servers. This is the select statement that I have.

SELECT InvoiceLine.BranchNo, InvoiceLine.Type_IN_CR, InvoiceLine.Docket,Invoice开发者_如何转开发Line.ProductCode, 
   InvoiceLine.Inv_Price * (select OuterUnits From server.PopTables.dbo.Products Inner Join InvoiceLine ON server.PopTables.dbo.Products.ProductCode = InvoiceLine.ProductCode Where server.PopTables.dbo.Products.ProductCode = InvoiceLine.ProductCode) AS PricePT,
    InvoiceLine.Inv_Quantity * (select OuterUnits From server.PopTables.dbo.Products Inner Join InvoiceLine ON server.PopTables.dbo.Products.ProductCode = InvoiceLine.ProductCode Where server.PopTables.dbo.Products.ProductCode = InvoiceLine.ProductCode) AS QunatityPT,
    InvoiceLine.Inv_Total, InvoiceHeader.InvoiceDate

It is returning the Error below 4 times.

Msg 4104, Level 16, State 1, Procedure IMFertiliserRebates, Line 7 The multi-part identifier "PROGRAMS.PopTables.dbo.Products.ProductCode" could not be bound.

I'm trying to multiply two columns in one table by a column in another table on another server based on the product code.

I'm very lost!

Any help would be appreciated

Thanks.

You need to use tables aliases on your linked tables

e.g.

SELECT invoiceline.branchno, 
       invoiceline.type_in_cr, 
       invoiceline.docket, 
       invoiceline.productcode, 
       invoiceline.inv_price * (SELECT outerunits 
                                FROM   serverb.table3.dbo.products p 
                                       INNER JOIN invoiceline 
                                         ON p.productcode = 
                                            invoiceline.productcode 
                                WHERE  p.productcode = invoiceline.productcode) 
       AS 
       pricept, 
       invoiceline.inv_quantity * (SELECT outerunits 
                                   FROM   serverb.table3.dbo.productsinner ps 
                                          INNER JOIN invoiceline 
                                            ON ps.productcode = 
                                               invoiceline.productcode 
                                   WHERE  p.productcode = 
                                          invoiceline.productcode) AS 
       qunatitypt, 
       invoiceline.inv_total, 
       invoiceheader.invoicedate 

Because your original SQL is kinda large here are the changes that I made

Without Alias

• ON server.PopTables.dbo.Products.ProductCode = invoiceline.productcode
• WHERE server.PopTables.dbo.Products.ProductCode...
• ON serverb.table3.dbo.productsinner = invoiceline.productcode
• WHERE serverb.table3.dbo.productsinner.productcode = invoiceline.productcode

With alias

• ON p.ProductCode = invoiceline.productcode
• WHERE p.ProductCode...
• ON serverb.table3.dbo.productsinner = invoiceline.productcode
• WHERE serverb.table3.dbo.productsinner.productcode = invoiceline.productcode

It seems that your are using linked servers. As others noted, use an alias for the table or view from the linked server or you will get a "multi-part identifier cannot be bound" error.

This is the 4 part naming convention limit. For example server.PopTables.dbo.Products.ProductCode has five parts and will give an error. However, SELECT products.ProductCode FROM server.PopTables.dbo.Products AS products will work fine.

Remember that if the linked server is an instance, make sure you bracket the name. For example [SERVERNAME\INSTANCENAME]. This was a real hassle for me the first time it happened.

I would have one sp returning the data you need from the linked server:

select OuterUnits From ServerB.Table3.dbo.Products 
       where ServerB.Table3.dbo.Products.ProductCode = @ProductCode

and either use that result directly from the sp in your join, or store the result somewhere on serverA and use that in your join. I wouldn't query a linked table any more than I had to.