Showing posts with label SQLSERVER. Show all posts
Showing posts with label SQLSERVER. Show all posts

Friday, January 22, 2010

How to references mutilple databases in SQL Server 2005

Today, when working with SQL Server 2005, I have a issue. This situation as "I have a select statement that references to multiple tables that reside in 2 different database". After finding in internet and helping from my team leader, I found a solution as below;

+ Using Synonyms in SQL Server 2005 for taking references to other database (other database server)
USE "Database using references";
GO
IF OBJECT_ID('dbo.OrgUnit', 'SN') IS NOT NULL
DROP SYNONYM dbo.OrgUnit;
GO
CREATE SYNONYM dbo.OrgUnit FOR ["name of database referenced"].security.orgunit.OrgUnit;

select * from dbo.OrgUnit


Note: I you want to know about "name of database referenced", using as
select * from sys.servers


+ Adding:
USE master;
GO
EXEC sp_addlinkedserver
'"IP Address"',
N'SQL Server'
GO


+ And now you can select the table that reside in the other location as

USE "Database using references";
GO;
select * from dbo.OrgUnit


For example:

Assume we have [Client] database at local and [Security] database at the remote side

USE master;
GO
EXEC sp_addlinkedserver
'SECURITY_DATABASE_ADDRESS',
N'SQL Server'
GO

select * from sys.servers

USE Client;
GO
IF OBJECT_ID('dbo.OrgUnit', 'SN') IS NOT NULL
DROP SYNONYM dbo.OrgUnit;
GO
CREATE SYNONYM dbo.OrgUnit FOR [SECURITY_DATABASE_ADDRESS].security.orgunit.OrgUnit;

select * from dbo.OrgUnit