Wednesday, April 22, 2009

Create mutiples linked servers

When you need to work with multiple databases from Oracle, for example: 100 databases with name like that DB01, DB02, ..., DB100. All of them use the same user name and password to query data such as scott/tiger. Your job is to connect them from SQL Server. First, you need to create 100 linked servers. It takes much time to create 100 linked servers like that by using create linked server screen.
You can do this by this script.


DECLARE @LINK_NAME VARCHAR(10)
DECLARE @SERVER_NAME VARCHAR(50)
DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < 101
BEGIN
SET @LINK_NAME = 'DB' + RIGHT('00' + CAST(@COUNT AS VARCHAR),2);
SET @SERVER_NAME = @LINK_NAME + '{Some name}';
EXEC master.dbo.sp_addlinkedserver @server = @LINK_NAME
, @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=@SERVER_NAME;
EXEC sp_addlinkedsrvlogin @LINK_NAME, 'false', 'domain\user', 'scott', 'tiger';

SET @COUNT = @COUNT + 1;
END

No comments:

Post a Comment