February 8, 2008 at 8:43 am
I'm trying to run the following query and I'm getting this error. I'm running the query from the server X1ghqw0688 and have setup a Linked Server to X1ADCDRILSQL1 and the connection tests OK.
ERROR
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.CP" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.LOCN" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.INVOICE" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.VENDOR" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.AMOUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.YEAR" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.PD" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.GROUP" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "X1ADCDRILSQL1.DTM.dbo.t_DTM_DATA.GROUP" could not be bound.
QUERY
UPDATE
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING
SET
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.[Group] = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].[GROUP]
FROM
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING
LEFT OUTER JOIN
[X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA]
ON
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.OpCo_Number = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].CP
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Store_Number = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].LOCN
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Invoice_Number = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].INVOICE
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Vendor_Number = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].VENDOR
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Cost = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].AMOUNT
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Year = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].YEAR
AND
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING.Period = [X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].PD
WHERE
([X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].[GROUP] IS NOT NULL)
AND
([X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA].[GROUP] <> '')
February 8, 2008 at 11:22 am
This is what fixed the issue.
Posted by SQL-Pro in the Microsoft Forums.
"I would recommend aliasing your tables in the FROM clause and trying again
QUERY
UPDATE
A
SET
A.[Group] = B.[GROUP]
FROM
[X1ghqw00688].AP.DSDNDSD.REPORT_DATA_STAGING A
LEFT OUTER JOIN
[X1ADCDRILSQL1].[DTM].[dbo].[t_DTM_DATA] B
ON
A.OpCo_Number = B.CP
AND
A.Store_Number = B.LOCN
AND
A.Invoice_Number = B.INVOICE
AND
A.Vendor_Number = B.VENDOR
AND
A.Cost = B.AMOUNT
AND
A.Year = B.YEAR
AND
A.Period = B.PD
WHERE
(B.[GROUP] IS NOT NULL)
AND
(B.[GROUP] <> '')"
February 10, 2008 at 3:39 pm
Thanks for posting the solution you found. I figured it would be something like that, but now we all know!
March 23, 2010 at 1:48 pm
I've never commented on a forum in my life but this solution saved me a ton of work, I almost pulled all of the data to the local server to perform my query, instead, I just aliased the tables 🙂
Thanks!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply