Ambiguous column name problem.

[jjjax64]

We were having some problems with slowness of some queries when going against linked servers and tried using open query instead of 4 part identifier to do select from linked server table. Seems to have helped in some areas but having a problem with an update query that is trying to update a table that is used in open query. This is just a quick test query to recreate problem and below that is the errors we get back. THANKS for any help!

UPDATE SQLSERVER2.Production2.dbo.labellog
set fldWork2 = fldStep,
fldWork3 = fldID
from sqlserver2.production2.dbo.dbll050
INNER JOIN OPENQUERY (SQLSERVER2, 'Select fldStep, fldID, fldWork from Production2.dbo.labellog') ON
PC_HI_ITEM_NUMBER = fldWork

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'fldStep'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'fldID'.

 

[SQLBill]

You are using two tables. Does fldStep and fldID exist in both tables? If so, you need to identify which table is being used. For example: labellog.fldStep, labellog.fldID

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

The problem is that you have fldStep and fldID in both tables (dbll050 and OPENQUERY). To resolve this problem, you should alias the OPENQUERY and use the aliases throughout your query. Something like this...

UPDATE SQLSERVER2.Production2.dbo.labellog
set fldWork2 = [!]Whatever.[/!]fldStep,
    fldWork3 = [!]Whatever.[/!]fldID
from sqlserver2.production2.dbo.dbll050     
INNER JOIN OPENQUERY (SQLSERVER2, 'Select fldStep, fldID, fldWork from Production2.dbo.labellog') [!]As Whatever[/!] ON
PC_HI_ITEM_NUMBER = fldWork

I think you may have other problems here once you fix the alias issue.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jjjax64]

Thanks but dbll050 does not have same field names as the open query. It's got to be with the updating of the same table as the open query. I think you got me in the right track because the following seems to have worked.

UPDATE x
set x.fldWork2 = x.fldStep,
x.fldWork3 = x.fldID
from sqlserver2.production2.dbo.dbll050
INNER JOIN OPENQUERY (SQLSERVER2, 'Select * from Production2.dbo.labellog') AS x ON
PC_HI_ITEM_NUMBER = x.fldWork