How do I UPDATE a Linked Server table where "alias" is required, in SQL Server 2000?
Posted
by Mark Hurd
on Stack Overflow
See other posts from Stack Overflow
or by Mark Hurd
Published on 2010-05-07T10:22:32Z
Indexed on
2010/05/07
10:28 UTC
Read the original article
Hit count: 225
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! :-) )
© Stack Overflow or respective owner