link server

Question

link server

s.pramiladevi

SSC Rookie

Points: 25
More actions
April 1, 2008 at 9:27 pm

#188104

Hi,
I would like to create link server for sql 2005.Actually i need to get data from one server two other server.Using link server is it possible to get data between two different servers.
Pls advise.we are using asp for programming.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Use this code to create the linked server (the example is for connecting to Oracle):

EXEC master.dbo.sp_addlinkedserver @server = N'ServerName', @srvproduct=N'MSDAORA', @provider=N'MSDAORA', @datasrc=N'connectstring'

Then your TSQL just needs to include the server name like this:

SELECT* FROM ServerName..Owner.Table

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 2

...or right-click on "Server objects | Linked Servers" and select "New Linked Server..." then complete the fields in the dialog box.

First of all, I usually set up a dedicated user and password such as 'linked_server_user' | 'somepassword' on the target server, assign that user permissions to the appropriate tables, views etc.

Secondly, on the "New Linked Server..." dialog box, configure the connection to "be made using this security context" - entering the username and password

Check the conection by connecting to the host server and querying the linked server using four part naming convention e.g:

SELECT * FROM \\LinkedServerName.DatabaseName.Schemaname.Tablename

🙂

s.pramiladevi SSC Rookie Points: 25 More actions · Answer 3

Hi,

Thanks for your kind informations.Still i got doubt on this.

How to create linked server for SQL SERVER DB.When am trying they rejected my link server name.

How should be give link server name.what is the format.It means server name or database name .

Where to give the server name,db name and table name.

Wonder if anyone help me in details.

Thanks All.

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 4

1. Click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.

3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

4. In the New Linked Server dialog box, on the General page, in Linked server, enter the name of the server you want to link to.

5. Under Server type, click SQL Server.

As you asked how to access table: So considering, your linked server name is SRV1 table name is TABLE1, database name is DB1 and owner is DBO

to select data you need to write query:

select * from SRV1.DB1.DBO.TABLE1

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Dugi SSCoach Points: 17998 More actions · Answer 5

So what about if you want to create a link to Access database!?

the link is working but when it creates, you can see what happened with hierarchy:

please see the attachment!

so the question is how to select the data ...we do not have schema there also it is under the default database ( for me little bit confusion)

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

Try

SELECT * FROM pkd...tbl

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dugi SSCoach Points: 17998 More actions · Answer 7

Grant Fritchey (4/4/2008)
Try
SELECT * FROM pkd...tbl

it works!...thnx!

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

s.pramiladevi SSC Rookie Points: 25 More actions · Answer 8

Thanks for your kind reply.

But when i try to create linked server,i got the below error,

"The linked server has been created but failed a connection test.Do you want to keep the linked server?"

Additional information:

An exception occured while execuring a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

The OLE DE provider "SQLNCLI" for linked server "xxx.xx.xx.xxx" reported error.

Authenication failed.

Pls advise,what is the mistake.

Could you tell me also how to give the permission for other server access thro' linked server.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

"Authentication failed" is a security error. Whatever login you're attempting to use doesn't have the correct access on the server to which you are linking.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 10

did you set up a SQL user specifically for the linked server (on the server to be linked) - with permissions on the tables, views etc that need to be accessed from the other server? Check my earlier post...:)