How to read data from a different Server in SQL Server?

Posted Date: 08 Jun 2004	Resource Type: Articles	Category: Databases
Author: Puneet	Member Level: Silver
Rating:	Points: 4

Introduction

To Read data from different server. Suppose you are connected to server A and you want to execute a query on Server B.

SYNTAX


SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=anyServerName;User ID=uid;Password=pwd').DatabaseName.dbo.TableName/ViewName

Where
- 'anyservername' is server name u need to connect to.
- 'uid' = user id of the server
- pwd = password of the server
-'DatabaseName' = Name of the database u need to connect to on the different server
-'Tablename/viewname' = Tablename/ Viewname for which u need to display/ read the data

For Example if you need to connect to the Categories Table on the Northwind Database on the ServerB with userid=sa and password=sa then your query will look like.


SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=ServerB;User ID=sa;Password=sa').Northwind.dbo.Categories

Using this technique we may execute queries on the excel files also, The syntax for that is as follows:


SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="Path of your excel file/ need a absolute path";User 
ID=uid;Password=pwd;Extended properties=Excel 5.0'

Thanks Puneet

Responses

Author: critic 08 Jun 2004 Member Level: Bronze Points : 0

Did you mean reading data from different servers or from different databases in one server? Can you add some clarification to it ?

Author: Puneet 08 Jun 2004 Member Level: Silver Points : 0

I have modified the content on the article, Now if you read it you will be clear.
Thanks for making your comments

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Benefits of using Stored Procedures
	Previous Resource: How to change the owner of the Objects(table,view etc) in a SQL Server Database?
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

More Resources