Select into another server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select into another server

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-05-19 : 04:39:35

Hi Gurus,

I have two servers - Development (SR002) and Live (SR001) installed with SQL Server Express, both contrains the identical database structures. Both can use same AD login or same SA account.

1. I may need to copy the some tables from live data and overwrite the Development server. (or vice versa)

I am thinking of using SQL Script, but I found no sample to query one SQL Server and insert to another SQL Server.

Please advise.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-19 : 05:05:42

Your searchword(s): linked server

No, you're never too old to Yak'n'Roll if you're too young to die.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-05-19 : 05:36:35

any better good reference?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-05-19 : 07:38:15

quote:
Originally posted by calvinfoo

any better good reference?
backup and restore....

Limuh
Yak Posting Veteran

94 Posts

Posted - 2011-05-19 : 07:39:03

Also consider REPLICATION

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-05-19 : 21:41:37

I am not doing replication. I only need to know how to copy some data from one server to another server.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-19 : 21:45:58

as Fred suggested, use Linked Server

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-05-20 : 00:18:11

I have three servers SR001, SR002, SR003. all these are Windows 2005 running in VMWare.

SR001 = application server
SR002 = web server
SR003 = development server

I am testing to select from one server and insert it to another by

1. select * from [sr002\sqlexpress].[myDB].[dbo].[mytable]

Msg 7202, Level 11, State 2, Line 1
Could not find server 'SR002\sqlexpress' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I then tried
1. select * from sys.servers in SR002 and SR003, it both returned:
name = SR001\SQLEXPRESS
data_source = SR001\SQLEXPRESS

I suspect it is because I duplicated the SR001 to SR002 and SR003 directly, and just renamed the server and given new IP Address.

What should I do? Must I reinstall the SQL Server?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-20 : 00:22:56

did you create the linked server ?

Follow the example here
Creating Linked Server

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-20 : 00:24:35

quote:
Msg 7202, Level 11, State 2, Line 1
Could not find server 'SR002\sqlexpress' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

The error message that you posted, already inform you of what you required.

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-05-20 : 00:33:22

Ok, I'll read that up, seems like I need some time to digest it. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources