Is Copy Possible This Way?

Question

Post reply

Is Copy Possible This Way?

ibnzakariyyahh

SSC Enthusiast

Points: 197
More actions
December 23, 2008 at 8:40 am

#199384

case scenario is this: a database named OptCon exists on two different computers(i.e Computer A and Computer B) on the same network, and i need to copy a particular table from OptCon in Computer A into OptCon in Computer B. how can i get this solved? :-S
your quick response will highly be appreciated.
Thanks friends.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

You can use the import/export wizard to do this

or

create an SSIS package

or

Setup a Linked server and use INSERT INTO.

I would go with the SSIS package..

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

Those are all great options, you can also use OpenRowset with select into to avoid setting up a permanent linked server.

Also, you can look at a combination of SQL Compare and SQL Data Compare to copy it. The advantage of this one is that it will copy the entire structure to include constraints whereas most of other options mentioned so far won't.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 3

Thanks! but as you can see am a newbie, could you please give a highlight of what i'd need to do so that i can grasp it once and for all? av tried setting up a linked server, but it's failing. Kindly help with the highlights of the processes involved using a case scenario. Kip in mind that it involves two different computers.

I shall be very grateful if yu cud help with this.

regards.

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 4

Thanks! but as you can see am a newbie, could you please give a highlight of what i'd need to do so that i can grasp it once and for all? av tried setting up a linked server, but it's failing. Kindly help with the highlights of the processes involved using a case scenario. Kip in mind that it involves two different computers.

I shall be very grateful if yu cud help with this.

regards.

steveb. SSC-Forever Points: 46733 More actions · Answer 5

what is the error that you are getting when setting up a linked server?

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 6

it's asking me for product name, data source. and i dont know which service (from the various OLE DB Provider) to select. infact i dont understand the process.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

You want the OLEDB for SQL Server provider, input the second server name and then valid credentials to connect.

tosscrosby SSCoach Points: 18045 More actions · Answer 8

And while you don't seem to be quite there yet, a simple SQL script using the four part naming convention would be as follows:

--edit - delete computerB.OptCon.dbo.tablename - then the following....

insert into computerB.OptCon.dbo.tablename select * from computerA.OptCon.dbo.tablename

-- You can't be late until you show up.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 9

Check this link and it explains to step wise how to setup linked server

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 10

this is the error i get when i ran a query against the linked server i created. the name of the linked server is SERVERLINK.

error thus:

OLE DB provider "SQLNCLI" for linked server "SERVERLINK" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "SERVERLINK" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SERVERLINK"

tosscrosby SSCoach Points: 18045 More actions · Answer 11

I'm not familiar with SQL2005 but in SQL2000, when opening the properties on my linked server, there is a security tab. If compatible in 2005, what do you have listed on that pane? Mine connects as a specific security context and I supply the user and password on that pane.

-- You can't be late until you show up.

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 12

this is the error i get when i ran a query against the linked server i created. the name of the linked server is SERVERLINK.

error thus:

OLE DB provider "SQLNCLI" for linked server "SERVERLINK" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "SERVERLINK" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SERVERLINK"

tosscrosby SSCoach Points: 18045 More actions · Answer 13

run this and post what is returned. look it up in BOL.

sp_helplinkedsrvlogin

-- You can't be late until you show up.

ibnzakariyyahh SSC Enthusiast Points: 197 More actions · Answer 14

thanks. i ran and executed the sp_helplinkedsrvlogin, but it's only returning the different logins security i have on the linked server.what i need now is how to use the linked server to communicate to different databases on different servers.

thanks.