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 [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.

EDIT:

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

SQL Execution Error.

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.

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.

Please post full (or more of) the view creation script, as well as the code used to access the view. — David M, Mar 22 '10 at 16:51
555.555.555.555 is not a valid IP address. Each number can be a maximum of 255 and it needs to be a valid IP address. For example, you should be able to ping the IP address: ping 10.1.16.1. Often it makes more sense to use the server name rather than the IP address. — Ryan, Mar 23 '10 at 09:01
Thanks for your response. 555.555.555.555 is not the IP i am using. I have changed this deliberately. — Kashif, Mar 24 '10 at 08:07

score 25 · Accepted Answer · edited Aug 19 '14 at 17:18

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

SELECT * FROM [1.2.3.4].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    [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.

score 13 · Answer 2 · edited May 29 '12 at 14:22

13

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.

But if you use SQL Query:

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.

edited May 29 '12 at 14:22

Martijn Pieters

1,048,767
296
4,058
3,343

answered May 29 '12 at 09:21

Phil Choo

131
1
2

thanks a million for this. My linked server has a hyphen in the name which throws out Management Studio 2005. Your solution works around this. – Tim Richardson Nov 15 '12 at 10:40
That was the issue I was having. I was giving the 'Unresolved object' warning too much importance. Thanks Phil. – Vic Colborn Oct 14 '13 at 15:28

score 2 · Answer 3 · edited May 23 '17 at 12:26

2

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:

    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

edited May 23 '17 at 12:26

Community

1
1

answered Aug 24 '12 at 20:18

Hossein Sarshar

486
7
15

An IP works fine as long as you enclose the address in square brackets. – influent Jun 13 '13 at 19:29
This is a great solution to create de linked server with an alias when your DNS doesn't resolve names correctly. – EAmez May 08 '17 at 11:26

score 2 · Answer 4 · answered Mar 22 '10 at 16:19

2

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

answered Mar 22 '10 at 16:19

David M

71,481
13
158
186

score 1 · Answer 5 · edited Feb 12 '14 at 15:46

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.

score 0 · Answer 6 · answered Mar 22 '10 at 16:28

0

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.

answered Mar 22 '10 at 16:28

Simon

6,062
13
60
97

Dragging and dropping from a defined linked server into my view in management studio didn't work - the interface (Management Studio 2008) doesn't allow me to drag and drop. Maybe there's a setting in the linked server that prevents this? – David Conlisk Jan 17 '12 at 15:00

score 0 · Answer 7 · answered Mar 23 '10 at 09:25

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

Create View using Linked Server db in SQL Server

7 Answers7

Linked