如何更新“别名”所在的链接服务器表在 SQL Server 2000 中是必需的吗？

发布于 2024-08-31 20:35:51 字数 893 浏览 4 评论 0原文

在 SQL Server 2005 中，表名可用于区分您所引用的表：

UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE tablename.ID=u.ID

在 SQL Server 2000 中，这会导致

服务器：消息 107，级别 16，状态 2
列前缀“tablename”与查询中使用的表名或别名不匹配。

尝试

UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE LinkedServer.database.user.tablename.ID=u.ID

结果为

服务器：消息 117，级别 15，状态 2
号码名称“LinkedServer.database.user.tablename”包含的前缀数量超过最大数量。最大值为 3。

当然，

UPDATE LinkedServer.database.user.tablename
SET val=u.val
FROM localtable u
WHERE ID=u.ID

结果是

服务器：消息 209，级别 16，状态 1
列名“ID”不明确。

（事实上，搜索“号码名称包含的前缀数量超过最大数量。最大数量为 3。”我找到了答案，但我已经输入了这个问题，我将发布它！:-)）

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤凫 2024-09-07 20:35:51

怎么样：

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

How about:

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

回复收藏 0 原文

一向肩并 2024-09-07 20:35:51

是的，它运行良好。

我用过这个。你可以尝试一下..

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

我已经介绍了使用 SQL Linked 服务器进行所有插入、更新和选择。

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.

回复收藏 0 原文

~没有更多了~