Solved

Select data from different servers in MS SQL

Posted on 2006-10-31
11
211 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

828 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