How to work on datamigration with different database present in different server locations

Question

0.00/5 (No votes)

See more:

Hii to all
i want to get or copy tables data present in remote server, from or to my local database,preferrably by writing procedures or triggers.For example there is a table called emp2 in database called db2 present in different server at some x location.I want to get,copy this emp2 tables data from my local database called db1 to the table called emp1.Simply how can i perform all database operations by communicating with same are different kind of dtabases(sqlserver and mysql or any two)present in different locations by writing procedures and triggers.Please help me how can i go with this...thanx in advance

Posted 21-Feb-13 0:26am

Ram7 from Hyderabad

Updated 21-Feb-13 1:36am

v2

Add a Solution

Comments

CHill60 21-Feb-13 7:24am

Have a look at the solution to this question - haven't posted it as a solution (yet) as it's similar to what you need but worded differently
http://www.codeproject.com/Answers/541402/SynchronizationplusInplusSqlplusServer#answer1

1 solution

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)

Khorshed Alam, Dhaka · Accepted Answer · 2013-02-21T04:42:00

Solution 1

There are several ways you can do this.

1. Use linked server to connect the remote server.
2. Use distributed query in sql server like OPENROWSET For example

SQL

SELECT a.* INTO dbo.TestDepartment
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

For more information have a look on:
http://msdn.microsoft.com/en-us/library/ms190312.aspx[^]

Posted 21-Feb-13 4:42am

Khorshed Alam, Dhaka

Comments

Ram7 from Hyderabad 22-Feb-13 1:04am

hii Khorshed thanks for ur responce,i have tried with linked server,but i didn't reached my requirement.Iam getting error with connection.can u also please guide me how to work on linked server or to provide any link to go with this.Iam trying to create linked server to mysql db from sqlserver db.............

Khorshed Alam, Dhaka 22-Feb-13 1:12am

Dear Ram7,
Please have a look on the link, it may help you.
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

Ram7 from Hyderabad 22-Feb-13 1:22am

Thanks a lot khorshed, i will go with
this.......