CODEWITHSUNDEEP
sqlsql-server-2000sql-update
Mark Hurd
Mark Hurd

Reputation: 10931

How do I UPDATE a Linked Server table where "alias" is required, in SQL Server 2000?

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! :-) )

Upvotes: 3

Views: 24290

Answers (2)

Roushan Kumar
Roushan Kumar

Reputation: 11

Yes, its's working fine.

I have used this. You can try it..

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 mentation all insert, update and select by using SQL Linked server.

Upvotes: -2

AdaTheDev
AdaTheDev

Reputation: 147224

How about:

UPDATE ls
SET ls.val=u.val
FROM LinkedServer.database.user.tablename ls
    JOIN localtable u ON ls.ID = u.ID

Upvotes: 10

Related Questions