Givens:
吉文斯:
- One SQL Server is named:
DevServerA
- 一个SQL服务器被命名为:DevServerA
- Another is named:
DevServerB\2K5
- 另一个名字:DevServerB \ 2 k5
Problem:
问题:
From DevServerA
, how can I write a query that references DevServerB\2K5
?
从DevServerA,我如何编写引用DevServerB\2K5的查询?
I tried a sample, dummy query (running it from DevServerA
):
我尝试了一个示例,哑查询(从DevServerA运行):
SELECT TOP 1 *
FROM DevServerB\2K5.master.sys.tables
And I get the error:
我得到了误差:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\.'.
However, I know my syntax is almost correct, since the other way around works (running this query from DevServerB\2K5
):
但是,我知道我的语法几乎是正确的,因为另一种方法也可以工作(从DevServerB\2K5运行这个查询):
SELECT TOP 1 *
FROM DevServerA.master.sys.tables
Please help me figure out how to reference DevServerB\2K5
from DevServerA
. Thanks.
请帮助我了解如何从DevServerA引用DevServerB\2K5。谢谢。
3 个解决方案
#1
16
In 4 part names, the first part if the name of a linked server (ie. a metadata object), not the name of a server (ie. a host name). So you can name your linked server FOO and have him point at the host BAR, or at the instance FOO\BAR. And even if you name the linked server object to contain a slash, you can still use it in a multi-part name by simply quoting the name:
在4部分名称中,第一部分是链接服务器的名称。元数据对象),而不是服务器的名称。主机名)。所以你可以给你的链接服务器命名为FOO,并让它指向主机栏,或者实例FOO\栏。即使您将链接的服务器对象命名为包含斜线的,您仍然可以使用它作为多部分的名称,只要简单地引用名称:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
#2
6
Try using square brackets:
试着用方括号:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
#3
0
On SQL SERVER 2005, the following happened: Entry of:
在SQL SERVER 2005上,发生了以下情况:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
Is changed to
更改为
SELECT TOP 1 *
FROM DevServerB\2K5.master.sys.tables
by SQL Server system and you still get the error message: Incorrect syntax near '.'.
通过SQL Server系统,您仍然会得到错误消息:“.”附近的语法不正确。
I tried it with a linked server named in two different ways: '[DevServerB\2K5]' and 'DevServerB\2K5'
我尝试用两个不同的方式命名一个链接服务器:'[DevServerB\2K5]'和'DevServerB\2K5'
Does anybody have any other ideas?
有人有其他想法吗?
Thank you Alan Robertson
谢谢你艾伦·罗伯逊
CORRECTION added the next day: I was wrong, partially. When one tries to create a view using a SQL statement like:
第二天更正:我说错了,部分是错的。当您尝试使用SQL语句创建视图时,如:
SELECT *
FROM [DevServerB\2K5].TestDB.dbo.tables
then the [ and ] are removed and one cannot save the view, BUT if one just writes a query using the same SQL string then it works correctly.
然后删除[和],不能保存视图,但是如果只使用相同的SQL字符串编写查询,那么它就可以正常工作。
I was also able to execute a SQL statement like:
我还可以执行如下SQL语句:
INSERT INTO [DevServerB\2K5].TestDB.dbo.tables ( ... ) ...
I can do what I wanted, but it would have been much better if I could have saved a view and used a view that would then be used for SELECT, INSERT and UPDATE of the table in the [DevServerB\2K5].TestDB database from the original server where I tried and failed to create a view because of the '\' .
我可以做我想做的事情,但是如果我能保存一个视图并使用一个视图,然后在[DevServerB\2K5]中选择、插入和更新表,那就更好了。来自原始服务器的TestDB数据库,由于“\”的原因,我尝试创建一个视图,但失败了。
-ASR-
asr -
#1
16
In 4 part names, the first part if the name of a linked server (ie. a metadata object), not the name of a server (ie. a host name). So you can name your linked server FOO and have him point at the host BAR, or at the instance FOO\BAR. And even if you name the linked server object to contain a slash, you can still use it in a multi-part name by simply quoting the name:
在4部分名称中,第一部分是链接服务器的名称。元数据对象),而不是服务器的名称。主机名)。所以你可以给你的链接服务器命名为FOO,并让它指向主机栏,或者实例FOO\栏。即使您将链接的服务器对象命名为包含斜线的,您仍然可以使用它作为多部分的名称,只要简单地引用名称:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
#2
6
Try using square brackets:
试着用方括号:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
#3
0
On SQL SERVER 2005, the following happened: Entry of:
在SQL SERVER 2005上,发生了以下情况:
SELECT TOP 1 *
FROM [DevServerB\2K5].master.sys.tables
Is changed to
更改为
SELECT TOP 1 *
FROM DevServerB\2K5.master.sys.tables
by SQL Server system and you still get the error message: Incorrect syntax near '.'.
通过SQL Server系统,您仍然会得到错误消息:“.”附近的语法不正确。
I tried it with a linked server named in two different ways: '[DevServerB\2K5]' and 'DevServerB\2K5'
我尝试用两个不同的方式命名一个链接服务器:'[DevServerB\2K5]'和'DevServerB\2K5'
Does anybody have any other ideas?
有人有其他想法吗?
Thank you Alan Robertson
谢谢你艾伦·罗伯逊
CORRECTION added the next day: I was wrong, partially. When one tries to create a view using a SQL statement like:
第二天更正:我说错了,部分是错的。当您尝试使用SQL语句创建视图时,如:
SELECT *
FROM [DevServerB\2K5].TestDB.dbo.tables
then the [ and ] are removed and one cannot save the view, BUT if one just writes a query using the same SQL string then it works correctly.
然后删除[和],不能保存视图,但是如果只使用相同的SQL字符串编写查询,那么它就可以正常工作。
I was also able to execute a SQL statement like:
我还可以执行如下SQL语句:
INSERT INTO [DevServerB\2K5].TestDB.dbo.tables ( ... ) ...
I can do what I wanted, but it would have been much better if I could have saved a view and used a view that would then be used for SELECT, INSERT and UPDATE of the table in the [DevServerB\2K5].TestDB database from the original server where I tried and failed to create a view because of the '\' .
我可以做我想做的事情,但是如果我能保存一个视图并使用一个视图,然后在[DevServerB\2K5]中选择、插入和更新表,那就更好了。来自原始服务器的TestDB数据库,由于“\”的原因,我尝试创建一个视图,但失败了。
-ASR-
asr -