SQL Server - Linked Servers

SQL Server - Linked Servers

Hope most of SQL Server DBA or Developer knows how to create link server in SQL Server.according to Microsoft  "the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server. " then you are eligibale to create link server between two data sources. This following picture will explain great view of link servers,(this picture used from MSDN)

• How to create link server between two SQL Servers,

USE master
GO
--MSSQL
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP'; -- provide the linking server name
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN
EXEC master.dbo.sp_addlinkedserver
@server=@LINKED_REMOTE_SERVER_NAME,
@srvproduct='SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,
@useself='False',
@locallogin=NULL,
@rmtuser='sa',
@rmtpassword='<sa password>';--you can used any user name and pwd with sysadmin role
EXEC master.dbo.sp_serveroption
@server=@LINKED_REMOTE_SERVER_NAME,
@optname=N'query timeout',
@optvalue=N'36000';

 EXEC master.dbo.sp_serveroption
  @server=@LINKED_REMOTE_SERVER_NAME,
  @optname=N'remote proc transaction promotion',
  @optvalue=N'true'; 

END ELSE
BEGIN
PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';
END
BEGIN TRY
EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;
PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';
END TRY
BEGIN CATCH PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();
END CATCH

• How to create link server between SQL Server & Oracle,

USE master

GO

--ORACLE

DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'ORA_DATABASEUSERGROUP'; -- provide the linking server name

IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN

EXEC master.dbo.sp_addlinkedserver 

@server = @LINKED_REMOTE_SERVER_NAME, 

@srvproduct=N'Oracle', 

@provider=N'OraOLEDB.Oracle', 

@datasrc=@LINKED_REMOTE_SERVER_NAME;

EXEC master.dbo.sp_addlinkedsrvlogin 

@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,

@useself=N'False',

@locallogin=NULL,

@rmtuser='ORA_USER1',

@rmtpassword='<password>';

EXEC master.dbo.sp_serveroption 

@server=@LINKED_REMOTE_SERVER_NAME,

@optname=N'query timeout',

@optvalue=N'36000';

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;

END ELSE

BEGIN

PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';

END

BEGIN TRY

EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;

PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';

END TRY

BEGIN CATCH

PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();

END CATCH

• Drop Link Server

USE master
GO
--DROP LINK SERVER
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP' -- provide the linking server name
IF EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN EXEC master.dbo.sp_dropserver
@server=@LINKED_REMOTE_SERVER_NAME,
@droplogins='droplogins';
END

Well! hope above script will help you to manage SQL Server link servers.

Cheers...

and Most welcome your comments and ideas...