move the tables data from one serve to another server

Question

0.00/5 (No votes)

See more:

HI,

Please help me out, my requirement is create procedure that procedure join two tables in server 1 and move it to server table how to solve this one and

not using ssis and any import and export data

Thanks

ram

Posted 24-Jul-12 20:56pm

ramesh4128

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

barneyman · Answer 1 · 2012-07-24T21:07:00

Solution 1

you should look at Linked Servers[^]

Posted 24-Jul-12 21:07pm

barneyman

Comments

ramesh4128 25-Jul-12 3:16am

how to create linked server in sql stored procedure can you please give the example

barneyman 25-Jul-12 3:35am

Example AND documentation - accessible from the link I gave you

I'd suggest you read about it rather than just copying someone else by rote - you might learn something that helps you solve that, or other problems

kishorekke · Answer 2 · 2012-07-24T21:18:00

Solution 2

You can use linked servers. Check below code

--Create Link to the Patron Server
@server = 'SERVER1',
@srvproduct = 'sqlserver',
@provider = 'SQLOLEDB',
@datasrc = '12.228.0.0' -- Server IP Address

--Create Login Credentitals to The Patron Server
EXEC Master.dbo.SP_ADDLINKEDSRVLOGIN 'SERVER1', 'false', null, 'sa', 'sa'

--Create RPC to Server
EXEC Master.dbo.sp_serveroption @server='SERVER1', @optname='rpc', @optvalue='true'
EXEC Master.dbo.sp_serveroption @server='SERVER1', @optname='rpc out', @optvalue='true'

EXEC SERVER1.<your databasename="">.dbo.<your procedure="" written="">
EXEC SERVER1.TempDB1.dbo.usp_GetDate

--Delete the Server link and Login Credentials
EXEC Master.dbo.sp_dropserver 'SERVER1', 'droplogins'

Posted 24-Jul-12 21:18pm

kishorekke

Comments

ramesh4128 25-Jul-12 3:21am

example here

server 1 :

source 1 table

source 2 table

join the both table and load it into

server 2:

destination table

kishorekke 25-Jul-12 3:59am

first connect to your server2 then write a procedure in ur server2 like below

@server = 'SERVER1',
@srvproduct = 'sqlserver',
@provider = 'SQLOLEDB',
@datasrc = '12.228.0.0' -- Server IP Address

--Create Login Credentitals to The Patron Server
EXEC Master.dbo.SP_ADDLINKEDSRVLOGIN 'SERVER1', 'false', null, 'sa', 'sa'

--Create RPC to Server
EXEC Master.dbo.sp_serveroption @server='SERVER1', @optname='rpc', @optvalue='true'
EXEC Master.dbo.sp_serveroption @server='SERVER1', @optname='rpc out', @optvalue='true'

-- Your query
SELECT * FROM SERVER1.TempDB1.dbo.Source1 S1 INNER JOIN SERVER1.TempDB1.dbo.Source2 S2 ON S1.ID=S2.ID

--Delete the Server link and Login Credentials
EXEC Master.dbo.sp_dropserver 'SERVER1', 'droplogins'