How do I update a Linked Server table where a "nickname" is required in SQL Server 2000?
In SQL Server 2005, tablename can be used to distinguish the table you are 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 "tablename" column prefix does not match the table name or alias used in the query.
Attempt
UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE LinkedServer.database.user.tablename.ID=u.ID
leads to
Server: Msg 117, Level 15, State 2 The
numeric name "LinkedServer.database.user.tablename" has 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
leads to
Server: Msg 209, Level 16, State 1
Ambiguous column name 'ID'.
(Actually searching for "Number name contains more than the maximum number of prefixes. The maximum is 3." I found the answer, but I typed this question and I'm going to post it :-))
a source to share
Yes, his work is wonderful.
I have used this. You can try.
select * from [FIRSTLINK].job.dbo.student
select * from openquery ([FIRSTLINK],'exec job.dbo.sp1')
select * from openquery ([FIRSTLINK],'insert into student values (5,'mohit','uttam nagar','New delhi')job.dbo.sp1')
INSERT INTO firstlink.job.dbo.student VALUES (6,'Public Relations', 'Executive General and Administration','delhi');
INSERT OPENQUERY (firstlink, 'SELECT * FROM job.dbo.student') VALUES (9,'prabhakar','Environmental Impact', 'Engineering');
UPDATE firstlink.job.dbo.student SET student.name='rousan' WHERE student.ID=4
I have an opinion on insert, update and select using SQL Linked Server.
a source to share