How to Create Database Link in sql server 2005

Database link can be done in oracle(click here to see how).But if we want to do it in SQL server 2005 we should use linked servers.

Permissions to connect linked sever:

Port: 1433 must be open.

Create linked server:

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

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

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

Create New Linked Server in SQL server

4. Click General, choose SQL Server option ,type the name of sql server in Linked server field

General->choose SQL Server option

5. Click Security , Choose Be made using the security context, fill Remote login and With password fields and click OK

Security->Be made using the security context option(SQL server)

6. The syntax to query data from linked server is the following:

select *
from [server name].[database name].[owner name].[table name]

–In our case

select *
from [LINKED_SQLSERVER_NAME].[database name].[owner name].[table name]
About these ads

3 Responses to How to Create Database Link in sql server 2005

  1. Thank you! This post saves my time. And also I did this code on my end.

    SELECT *
    FROM [MYCOMPUTER_NAME].[database name].[owner name].[table name] A
    LEFT JOIN [REMOTESERVER_NAME].[database name].[owner name].[table name] B ON
    A.id=B.id

    This works like a charm! Thank you so much!

  2. Web Site says:

    Thanks meant for supplying this kind of amazing write-up.

  3. Rashmi says:

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: