Create View using Linked Server db in SQL Server

Question

How can I create View on Linked Server db. For Example I have a linked server [0.0.0.0] on [1.1.1.1]. Both db servers are SQL Sserver 2005. I want to create View on [1.1.1.1] using table on linked server.

EDIT:

On creating using full name, [0.0.0.0].db.dbo.table, I am getting this error.

SQL Execution Error.

Executed SQL statement: SELECT * FROM 0.0.0.0.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.

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.

Thanks.

Answer 1

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].database.schema.table

Answer 2

You need to use the four part qualified name: linkedserver.database.schema.table

SELECT * FROM [0.0.0.0].Northwind.dbo.Customers

Here is an MSDN article about accessing object names.

You might want to try manually creating the view, rather than using the SQL Management tools:

CREATE VIEW [dbo].[sywx]
AS
    SELECT  *
    FROM    [0.0.0.0].Atia.dbo.IpPbxDCR
GO

I also recommend that you use a name, if possible, for the linked server rather than using the IP address (0.0.0.0 is reserved for the default network, and in your example 555.555.555.555 is not a valid IP address. Each number is an octet with a maximum of 255 and 255.255.255.255 is used for broadcasts.)

Answer 3

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.

Answer 4

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