Running query against remote server

If you want to run a SQL Query against a remote server for example a different machine on the same network, you may want to use the flowing code. First link the server, then use OPENQUERY to run the query against the linked server. The two sql commands do not need to be executed in one batch. Example

Computer Name : MY-PC
Remote Server Name: SQLExpress1
Present Server Name: SQLExpress2

EXEC Sp_addlinkedserver
  @server="MY-PCSQLServer1",
  @srvproduct='SQL Server'

-- from SQL Server2 you run this query
SELECT *
FROM   Openquery("my-pcsqlserver1", 'select * from Students') 

Example 2 – Remote Query

-- 1. first link the server, no need to give SQL server name in case of default server
EXEC sp_addlinkedserver   
   @server=N'168.10.0.10', 
   @srvproduct=N'',
   @provider=N'SQLNCLI';

-- 2. Run the query against the linked server
select * from
openquery("168.10.0.10", 'Select * from sales')

Do not forget single quotes around the query
Do not forget “Select * from” before OpenQuery statement.

Advertisements
This entry was posted in SQL Server 2008, Uncategorized and tagged , , , , , . Bookmark the permalink.

2 Responses to Running query against remote server

  1. Pingback: Hammad Khan's Blog » Add a linked Server in SSMS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s