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

No comments:

Post a Comment