C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Reviews   Communities   Interview   Jobs   Projects   Training   Your Ad Here    
Silverlight Games | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Polls | Revenue Sharing | Lobby | Gift Shop |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...






Resources » Articles » Databases »

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


Posted Date: 08 Jun 2004    Resource Type: Articles    Category: Databases
Author: PuneetMember Level: Silver    
Rating: 1 out of 5Points: 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 2004Member 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 2004Member 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  
Sign In to add 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



dotNet Slackers

About Us    Contact Us    Privacy Policy    Terms Of Use