Sometimes you want to query to the remote SQL server (Server which is in network). Following statement will help you to fire a query to the remote server:

Select * from [ServerName].[DatabaseName].[Scheme].[TableName]

Before you fire query, you are required to link to the server on which you want to query.  The ‘sp_addlinkedserver’is a system store procedure which can be used to add a link server to the server you are sitting.

EXEC sp_addlinkedserver

@server = ‘SourceServer’

, @Srvproduct = ”

, @Provider = ‘SQLNCLI’

, @datasrc = ‘Remote SQL Server instance name’

@server variable accepts the name which will be used by query.

@SrvProduct will be empty if the remote database server is SQL Server. You can still give the name but it requires some more detail, please see http://technet.microsoft.com/en-us/library/ms190479.aspx

@Provider will be ‘SQLNCLI’ in case of remote database server is SQL Server. There are more providers available depending upon type of Database Server. See http://technet.microsoft.com/en-us/library/ms190479.aspx

@datasrc will be the remote database server instance name. normally people use to put default name ‘local’ but verify.

The ‘sp_addlinkedserver’ accepts more parameters, please see http://technet.microsoft.com/en-us/library/ms190479.aspx

The ‘sp_addlinkedserver’ adds a row into sys.servers system table. You can fire a query to check whether the server is already linked or not.

After a linked server is created by using sp_addlinkedserver, the server can be accessed by using the following:

• Distributed queries that access tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements by using a linked server-based name.
• Remote stored procedures executed against the linked server by using a four-part name.
• An EXECUTE statement that takes an arbitrary parameterized pass-through command by using the AT linked_server_name extension. This command includes data definition language (DDL) and data manipulation language (DML) statements, or commands that return more than one result.

The ‘sp_dropserver’ stored procedure used to drop the linked server from sys.servers table. Once it get drop you will not be able to fire a query on remote server.

EXEC sp_dropserver ‘Provide remote server name’, ‘droplogins’;

For more information about sp_dropserver see http://msdn.microsoft.com/en-us/library/ms174310.aspx

Normally i recommend that you should put if condition to see whether linked server is already added or not before you add or drop.

To add the linked server

declare @Number int

set @Number = (SELECT count(*) FROM sys.servers where name = ‘Linked server name’)

PRINT @Number

IF @Number <= 0

BEGIN

EXEC sp_addlinkedserver

@server = ‘Linked server name’

, @Srvproduct = ”

, @Provider = ‘SQLNCLI’

, @datasrc = ‘Remove database server instance name’

PRINT ‘Added Linked Server——————————————’

END

GO

 

To drop the linked server

declare @Number int

set @Number = (SELECT count(*) FROM sys.servers where name = ‘Linked server name’)

IF @Number > 0

BEGIN

EXEC sp_dropserver ‘Linked server name’, ‘droplogins’;

PRINT ‘Removed Linked Server —————————–’

END

Comments are closed