Compare Table Contents SQL server and MYSQL

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

General SQL Server Forums

New to SQL Server Programming

Compare Table Contents SQL server and MYSQL

Author

Topic

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 12:43:06

Not sure if this is even possible but I have two completely different databases, one which I have collated on my PC using SQL server and a database on my webserver using MySql.

All I want to do is just compare the contents of each row of one column in SQL server with the equivalent in MySQL, And detail all, with a query in SQL Server, that do not match. Hope that makes sense?

Thanks any help in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 12:49:14

You've got a few options.

1. Create linked server on SQL Server pointing to MySQL, query directly using T-SQL
2. Import your data from MySQL into SQL Server using bcp/DTS/SSIS, query directly using T-SQL
3. Export all of the data to files, use a file comparison tool
4. Use whatever tools MySQL has to query SQL Server

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 13:34:28

Option 1 would be preferable - sorry if this is another obvious question but I am using Microsoft SQL Server Management Studio Express (maybe that’s not relevant), but how exactly do I create a linked server?

Found this post [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32456[/url] but don't believe it entirely relevant.

mflammia
Starting Member

44 Posts

Posted - 2008-07-21 : 16:48:46

Ok, having been doing some digging around and most of the literature I can find refers to Enterprise Manager with a nice Gui interface, which I may have read incorrectly somewhere, has been superseded with what I am using.

It looks like I need to create a linked server using sp_addlinedserver command which I have done as per below (IP address written as xxx)

EXEC sp_addlinkedserver
@server = 'ServName',
@srvproduct = 'MySQL',
@provider = 'MSDASQL',
@datasrc = 'xxx.xxx.xxx.xxx'
GO

You can use the below to view the details:

EXEC sp_dropserver 'ServName'

And the below to drop the linked server:

EXEC sp_dropserver 'ServName’

And the below to connect?

linked_server_name.catalog.schema.object_name

Well that’s as far as I get? Sure you have to create a username and password at some point – sorry if this is simple, your help is greatly appricated as always.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources