SQL Query to the remote SQL server

By , September 25, 2009

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

13 Responses to “SQL Query to the remote SQL server”

  1. Woman of Alien

    Ideal operate you have finished, this website is basically amazing with excellent facts. Time is God’s strategy for maintaining every thing from taking place directly.

  2. Charlie says:

    .

    ñýíêñ çà èíôó!

  3. corey says:

    .

    ñïñ çà èíôó.

  4. Norman says:

    .

    ñïàñèáî!!

  5. Virgil says:

    .

    áëàãîäàðñòâóþ!

  6. Francisco says:

    .

    ñïñ çà èíôó.

  7. Gene says:

    .

    ñïñ çà èíôó.