CODEWITHSUNDEEP
sqlsql-serversql-server-2008
asfsdf
asfsdf

Reputation: 175

The multi-part identifier could not be bound using a linked server with ODBC connection to a DB2

when I run this simple query 

select [B00BF4CR].[IWSE4S8].[SCTRN].[STCOMP], 
[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STDATE],
[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STUNM],
[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STQTY] 
FROM [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN] 
WHERE [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STCOMP]='51' 
AND [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STDATE] = 20140211 
AND [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STVOID] = 'N' 
ORDER BY [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STTCKT]

in my sql I got the below errors

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STCOMP" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STDATE" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STVOID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B00BF4CR.IWSE4S8.SCTRN.STCOMP" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STDATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STUNM" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STQTY" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "EPAK.B00BF4CR.IWSE4S8.SCTRN.STTCKT" could not be bound.

when I do double clic on the 1st one it highlight this section

FROM [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN] 
WHERE [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STCOMP]='51' AND

the 2nd and the 3rd ones 

[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STDATE] = 20140211 
AND [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STVOID] = 'N' ORDER BY

4, 5 and 6

select [B00BF4CR].[IWSE4S8].[SCTRN].[STCOMP], 
[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STDATE],
[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STUNM],

7

[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STQTY]

8

[EPAK].[B00BF4CR].[IWSE4S8].[SCTRN].[STTCKT]

Upvotes: 0

Views: 7632

Answers (1)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

At most, you can have a four part notation. Your query is invalidating that rule for the column names. A table alias comes in handy to avoid this issue.

[LINKEDSERVER].[DATABASE].[OWNER].[OBJECT]

Here is a rewrite of the query.

-- Use a table alias
SELECT
    S.[STCOMP], 
    S.[STDATE],
    S.[STUNM],
    S.[STQTY] 
FROM 
    [EPAK].[B00BF4CR].[IWSE4S8].[SCTRN] AS S
WHERE
    S.[STCOMP]='51' AND
    S.[STDATE] = 20140211 AND
    S.[STVOID] = 'N' 
ORDER BY 
    S.[STTCKT]

There are some issues with updates and deletes using Distributed Query Processing (DQP) through DB2OLEDB.

See article below for warning about this as well as examples of pass thru query vs linked server.

http://support.microsoft.com/kb/222937

Upvotes: 7

Related Questions