How do I UPDATE a Linked Server table where "alias" is required, in SQL Server 2000?
- by Mark Hurd
In SQL Server 2005 tablename can be used to distinguish which table you're referring to:
UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE tablename.ID=u.ID
In SQL Server 2000 this results in
Server: Msg 107, Level 16, State 2
The column prefix 'tablename' does not match with a table name or alias name used in the query.
Trying
UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE LinkedServer.database.user.tablename.ID=u.ID
results in
Server: Msg 117, Level 15, State 2
The number name 'LinkedServer.database.user.tablename' contains more than the maximum number of prefixes. The maximum is 3.
And, of course,
UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE ID=u.ID
results in
Server: Msg 209, Level 16, State 1
Ambiguous column name 'ID'.
(In fact searching on "The number name contains more than the maximum number of prefixes. The maximum is 3." I found the answer, but I've typed up this question and I'm going to post it! :-) )
