?
Solved

Select data from different servers in MS SQL

Posted on 2006-10-31
11
Medium Priority
?
216 Views
Last Modified: 2008-02-01
I have:

Server1:  England          Database:   London       Table: Sample
Server2:  Germany         Database:   Berlin         Table: Sample

1) I wish to select data from Server1, table Sample.....Should I write' Select * from England.London.Sample'??

2) I wish to copy The table 'Sample' from Server 1 to Server2.  Please advise?

Please advise.

Thank you very much.
0
Comment
Question by:woo_kh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Accepted Solution

by:
HuyBD earned 180 total points
ID: 17847275
first, create link server then user OPENQUERY to query

select * from openquery(LinkedServer, 'Select * from England.London.Sample')
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17847287
if you are in server 2, create a link server to server1,

insert into Sample select * from openquery(LinkedServer, 'Select * from England.London.Sample')
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17847332
u can use this query

SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=server-name;User ID=id;Password=pwd'
                   ).DBNAme.dbo.TableNAme
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17847345

insert into sample SELECT *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=server-name;User ID=id;Password=pwd'
                   ).DBNAme.dbo.TableNAme
0
 
LVL 11

Expert Comment

by:regbes
ID: 17847940
Hi woo_kh,

if you have added eact server as linked servers

Select * from England.London..Sample

HTH

R.
0
 
LVL 11

Expert Comment

by:regbes
ID: 17849580
woo_kh,
> 2) I wish to copy The table 'Sample' from Server 1 to Server2.  Please advise?
 you can use DTS or SSIS for this
0
 

Author Comment

by:woo_kh
ID: 17854839
Dear all,

When I have a look again on the SQL server, what I have is not 'linked server'.

When I clicked on the SQL Server Group, there are 2 servers, one is called 'local' and another is called 'database'.  Both servers have databases such as 'Northwind','Pub',etc.  The only difference is that the 'local' server has a database called 'sample' but not in the 'database' server.

I would like to copy the database 'sample' from server 'local' to 'database' using T-SQL? Any idea?

Please advise.  Please accept my apologise for the wrong questions.

Thank you.

Cheers,
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17855416

if u just wanna copy the database without values

1) u can generate script of ur sample db form ur local using EM and run this script in ur other server

or else

u can use 3 rd party tool red gate tools

for comparing and synching data
0
 

Author Comment

by:woo_kh
ID: 17855532
Yes, with values!

I need to copy all the databases from server 'Database' to server 'local'.

Any idea?
0
 
LVL 11

Expert Comment

by:regbes
ID: 17855905
in EM use the copy database wizard
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17857956
1) u can use copy database wizard in enterprise manager as regbes suggested.


right click the source server in EM

ALL Tasks --> Copy database wizard
and follow the steps in the wizard.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question