Solved

Select data from different servers in MS SQL

Posted on 2006-10-31
11
213 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 60 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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