Urgent Pls:How to copy column between two servers

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Urgent Pls:How to copy column between two servers

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2006-06-07 : 13:12:31

Hi guys,

How can i copy the column data between two servers which are having
the same database.

i.e., server A-database A-column 1
to
Server B-database A-Column 1

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 13:17:44

What do you mean copy? Does the column already exist? Do you just want to update the data?

Tara Kizer
aka tduggan

venkath
Posting Yak Master

202 Posts

Posted - 2006-06-07 : 13:32:47

The scenario is as follows

I have a databaseB which is restored from databaseA and stored in
other server to reduce the burdent on the server.

By mistake, my developer updated all the data in a column to null.

now i want to copy the data from the un affected database.

Thanks

quote:
Originally posted by tkizer

What do you mean copy? Does the column already exist? Do you just want to update the data?

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 13:36:37

Add a linked server onto serverA. Then use the four part naming convention to perform the update:

UPDATE b
SET YourColumn = a.YourColumn
FROM serverB.databaseB.dbo.YourTable b
INNER JOIN databaseA.dbo.YourTable a
ON b.SomeColumn = a.SomeColumn

Tara Kizer
aka tduggan

venkath
Posting Yak Master

202 Posts

Posted - 2006-06-07 : 13:54:46

could u pls tell me the steps in adding the linked server...pls.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 13:59:19

You can find the steps in SQL Server Books Online. You can find them in Enterprise Manager if you click on Security..Linked Servers. Just right click and add one (make sure to select SQL Server as the server type).

Tara Kizer
aka tduggan

venkath
Posting Yak Master

202 Posts

Posted - 2006-06-07 : 14:16:31

ok, i added the linked server and when i execute the above query
i am getting the following message..

Login failed for user 'sa'.

quote:
Originally posted by tkizer

You can find the steps in SQL Server Books Online. You can find them in Enterprise Manager if you click on Security..Linked Servers. Just right click and add one (make sure to select SQL Server as the server type).

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 14:23:04

So the sa passwords must not be the same on both database servers. Go to the second tab in the linked server, select the 4th radio button, type in sa and the remote sa's password. You could also change the sa password temporarily so that they match.

Once you are done with this, you'll want to delete this linked server, otherwise other with access to serverA will have sysadmin access on serverB.

Tara Kizer
aka tduggan

venkath
Posting Yak Master

202 Posts

Posted - 2006-06-07 : 14:35:45

Thanks a lot

I done it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources