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: 200

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

Related posts about sql-server-2000

Related posts about four-part-name