在 SQL Server 中使用链接服务器数据库创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2493866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create View using Linked Server db in SQL Server
提问by Kashif
How can I create View on Linked Server db. For Example I have a linked server [1.2.3.4] on [5.6.7.8]. Both db servers are SQL Sserver 2005. I want to create View on [5.6.7.8] using table on linked server.
如何在链接服务器数据库上创建视图。例如，我在 [5.6.7.8] 上有一个链接服务器 [1.2.3.4]。两个数据库服务器都是 SQL Sserver 2005。我想在 [5.6.7.8] 上使用链接服务器上的表创建视图。
EDIT:
编辑：
On creating using full name, [1.2.3.4].db.dbo.table, I am getting this error.
在使用全名 [1.2.3.4].db.dbo.table 创建时，出现此错误。
SQL Execution Error.
SQL 执行错误。
Executed SQL statement: SELECT * FROM 1.2.3.4.db.dbo.table (YOu can see brackets are not there.)Error Source: .Net SqlClient Data Provider Error Message: Incorrect syntax near '0.0'. ---> part of IP address.
执行的 SQL 语句：SELECT * FROM 1.2.3.4.db.dbo.table （您可以看到括号不在那里。）错误来源：.Net SqlClient 数据提供程序错误消息：'0.0' 附近的语法不正确。---> IP 地址的一部分。
I am just creating this in ManagementStudio, not using it because it is not created yet. I Have changed IP. In image you can see there are not brackets around IP but I given it and on error these brackets are removed.
我只是在 ManagementStudio 中创建它，没有使用它，因为它尚未创建。我已经改变了IP。在图像中，您可以看到 IP 周围没有括号，但我给出了它，并且错误地删除了这些括号。
Thanks.
谢谢。
回答by Ryan
You need to use the four part qualified name: linkedserver.database.schema.table
您需要使用四部分限定名称：linkedserver.database.schema.table
SELECT * FROM [1.2.3.4].Northwind.dbo.Customers
Here is an MSDN article about accessing object names.
这是一篇关于访问对象名称的 MSDN 文章。
You might want to try manually creating the view, rather than using the SQL Management tools:
您可能想尝试手动创建视图，而不是使用 SQL 管理工具：
CREATE VIEW [dbo].[sywx]
AS
SELECT *
FROM [1.2.3.4].Atia.dbo.IpPbxDCR
GO
I also recommend that you use a name, if possible, for the linked server rather than using the IP address.
如果可能，我还建议您为链接服务器使用名称，而不是使用 IP 地址。
回答by Phil Choo
Its a SQL Management Studio Issue. If you try to create the view using management studio NEW VIEW then you get that error incorrect syntax.
它是一个 SQL Management Studio 问题。如果您尝试使用管理工作室 NEW VIEW 创建视图，则会收到错误语法错误。
But if you use SQL Query:
但是如果你使用 SQL 查询：
CREATE VIEW [dbo].[viewname]
AS
SELECT *
FROM [0.0.0.0].database.dbo.table
GO
then it will work.
然后它会起作用。
To test you will see the view created when you refresh views. If you just do a select query from the view you will see the view return results.
为了进行测试，您将看到刷新视图时创建的视图。如果您只是从视图中执行选择查询，您将看到视图返回结果。
But if you try to go into design mode for that view and try executing the design query the error will pop up again even though the view was successfully created.
但是，如果您尝试进入该视图的设计模式并尝试执行设计查询，即使视图已成功创建，错误也会再次弹出。
回答by Hossein Sarshar
your main problem is naming your Link Server with numbers (IP address). The only way that worked for me was using an alphabetical name for my Link Server without any dot '.' You can use these lines of code to add your link server and authentication:
您的主要问题是用数字（IP 地址）命名您的链接服务器。对我有用的唯一方法是为我的链接服务器使用字母名称，不带任何点 '.' 您可以使用以下代码行添加链接服务器和身份验证：
EXEC sp_addlinkedserver
@server='TEST_LINK',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='tcp:0.0.0.0'
EXEC sp_addlinkedsrvlogin
@useself='FALSE',
@rmtsrvname='TEST_LINK',
@rmtuser='user',
@rmtpassword='secret'
You can find the original answer here
你可以在这里找到原始答案
回答by David M
If the linked server is set up, you just reference tables on it using a four-part qualified name:
如果设置了链接服务器，您只需使用四部分限定名称引用其上的表：
linkedserver.database.schema.table
So if your linked server name is [0.0.0.0], you can reference a table as:
因此，如果您的链接服务器名称是 [0.0.0.0]，您可以将表引用为：
[0.0.0.0].database.schema.table
回答by Rx_
For linked servers using the periods for a web service name - this won't work.
The code details and steps of the difference is posted at:
http://www.access-programmers.co.uk/forums/showthread.php?t=260764
e.g. FROM [V2.EGG.COM]..[NAT_DBA].[NV_WELLS]
In SSMS 2008, the square brackets around the [V2.EGG.COM].. are removed by the editor. Then the update failes because it is more than 4 parts.
Have searched in vain to find a work-around.
The Script that does work is posted at the link above.
对于使用句点作为 Web 服务名称的链接服务器 - 这将不起作用。
区别的代码细节和步骤发布在：
http: //www.access-programmers.co.uk/forums/showthread.php?
t =260764e.g. FROM [V2.EGG.COM]..[NAT_DBA].[NV_WELLS]
在 SSMS 2008 中，编辑器删除了 [V2.EGG.COM].. 周围的方括号。然后更新失败，因为它超过4个部分。
徒劳地寻找解决方法。
有效的脚本发布在上面的链接中。
回答by Simon
You can also just drag and drop the table/view object from the linked server into your view and Management Studio will create the four part reference for you.
您也可以将表/视图对象从链接服务器拖放到您的视图中，Management Studio 将为您创建四部分参考。
回答by devio
You need to define a Linked Server before you can access it, since the linked server entry also contains the authentication details.
您需要先定义链接服务器，然后才能访问它，因为链接服务器条目还包含身份验证详细信息。
After creating a linked server, you can access its databases and their objects using the dot notation servername.database.[owner].object
创建链接服务器后，您可以使用点符号 servername.database.[owner].object 访问其数据库及其对象
