Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 740
Home > Forums > Sql Server >

Linking 2 servers and copying Table data from Server1 to Server2 [Resolved]

Posted by Ashokn under Sql Server on 12/2/2010 | Points: 10 | Views : 5373 | Status : [Member] | Replies : 9
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Hi,

I have few tables(with data) in Server1 and I want have these tables in Server2
(1) First linking both server2 with Server1
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'Server2\SQLEXPRESS', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'Pass123'
(2) Copying/Transfering data
select * into 'Server2\SQLEXPRESS'.MyDB.dbo.aspnet_Membership from 'Server1\SQLEXPRESS'.dbo.aspnet_Membership

But getting error as --> Incorrect syntax near 'Server2\SQLEXPRESS'.

Please check whole query and provide me the solution.

Thanks in advance...



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Ashokn on: 12/3/2010 [Member] Starter | Points: 50

Up
0
Down

Resolved
Yes I got my work done.
Now being in Server2, I can copy table and its contents present in Server1.

Thanks lot for your replies...

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Vuyiswamb on: 12/2/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Looking at your Query i think there is some misunderstanding on how the Select into works 


select * into 'Server2\SQLEXPRESS'.MyDB.dbo.aspnet_Membership from 'Server1\SQLEXPRESS'.dbo.aspnet_Membership


Let us agree on Something before i comment Further.

1) The table "aspnet_Membership" does not Exists in the Server Server2\SQLEXPRESS ?

2) Is Server2\SQLEXPRESS the name of the Linked Server or the Physically name of your server?

Thank you for posting at dotnetfunda

Vuyiswa Maseko

Please note that


Thank you for posting at Dotnetfunda
[Administrator]

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: T.saravanan on: 12/2/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Following links also discuss with your problem...

http://www.codeproject.com/Answers/104857/How-to-get-a-value-from-a-table-in-different-serve.aspx#answer1

Cheers :)

Thanks,
T.Saravanan

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ashokn on: 12/2/2010 [Member] Starter | Points: 25

Up
0
Down
aspnet_Membership does not exist ,that is one problem.But server1(or 2)/SqlExpress-error is at this point now.How can we mention <serverName>.<DB name>.TableName without syntax error?

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Vuyiswamb on: 12/2/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Tell me why dont you manually add a link server ?

try this

Step 1 

sp_addlinkedserver 'Server1', '', 'SQLNCLI', NULL, NULL, '192.168.14.16'', NULL



sp_addlinkedsrvlogin Server1', 'false', NULL, 'sa', 'ooops'


Step 2

Refresh your SQLManament Studio Objects and your should see the linked Server , when you do the Queries just drag it from the and drop it where you will write your queries and do something like this 

<serverName>.<DB name>.TableName



Thank you for posting at Dotnetfunda
[Administrator]

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: PandianS on: 12/2/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

1.First, Validate the registered or not
IF EXISTS(SELECT 1 FROM master.dbo.sysservers WHERE srvname ='Server2\SQLEXPRESS')

PRINT 'The Server Not Registered Yet'

ELSE

PRINT 'The Server has been Registered'

2. Register the Server2 in your Server1 first:
Sp_AddLinkedServer 'Server2\SQLEXPRESS'
3. Grand access permission for the Registered Server:
Sp_AddLinkedSrvLogin 'Server2\SQLEXPRESS', False, 'sa', 'sa', 'Pass123'
4. Validate the Registered Server is accessible/Not from Server1 
Sp_TestLinkedServer N'Server2\SQLEXPRESS'
It should return as 'Command(s) completed successfully.'

Now you are ready to reach the Server2 from Server1.

You can only Create a copy of table into Other database within the same instance, Not to Other instance..

But you can access the objects from other server / instance.

When try to perform your task here, You will get an Error

The object name [Server2\SQLEXPRESS].MyDB.dbo.aspnet_Membership' contains more than the maximum number of prefixes. The maximum can be 2.

The meaning is, You an use only 2 Dots, Not Morethan 2 when creating an object.

What is 2 Dots :- DatabaseName.OwnerName.ObjectName

What is 3 Dots :- ServerName.DatabaseName.OwnerName.ObjectName

So, you con't create an object in other Server or instance.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ashokn on: 12/3/2010 [Member] Starter | Points: 25

Up
0
Down
From below query I'm getting error (more than 2 prefixes used!!)

SELECT * INTO [Server2\SQLEXPRESS].MyDB.dbo.aspnet_Membership from MyDB.dbo.aspnet_Membership

How can I change above query?

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: PandianS on: 12/3/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

The meaning is "You can not create an Objects to another Instance"

If you want to create an Object in [Server2\SQLEXPRESS] Instance then, You have to login into this server physically and Create it.

(Or)

You have an alternative....

Login into [Server2\SQLEXPRESS] and do the following
SELECT * INTO aspnet_Membership FROM  [Server1\SQLEXPRESS].MyDB.dbo.aspnet_Membership
Because, You can access the objects from other instance..., But not Create...

Now the table will be created in [Server2\SQLEXPRESS].

Note: You have to configure Linked server in [Server1\SQLEXPRESS] instance for [Server2\SQLEXPRESS]

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Vuyiswamb on: 12/3/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Please mark your answer as "Resolved"


Thank you for posting at Dotnetfunda
[Administrator]

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts