How to Create Database Link in sql server 2005

January 17, 2011 4 Comments

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]
Advertisement

Filed under DB Tagged with ,

How to Create Database Link in Oracle

January 13, 2011 Leave a comment

— Add the following entry in tnsnames.ora file:

SERVICENAME1=
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = hostname1)(PORT = 1521))
       )
       (CONNECT_DATA = (SERVICE_NAME = SERVICENAME1))
      )

–Connect to the database where you want to create db link and run the following command:

CREATE DATABASE LINK link_name
CONNECT TO myusername IDENTIFIED BY mypassword
USING 'SERVICENAME1';

Note:
myusername->is an username by which you can connect SERVICENAME1 database
mypassword->is myusername‘s password

–Check if it works:

SELECT sysdate
FROM dual@link_name

If it returns sysdate, means it works:)

Filed under DB Tagged with ,