Link to another database system

Question

Link to another database system

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
October 21, 2013 at 11:57 am

#284064

Hi all
I am very new to SQL Server, and I am maybe jumping in too deep water too soon. The reason I started with SQL Server is I would like to create a link with another database system.
At the moment I use mySQL Workbench to work with data that is generated by a custom made program. The reason I started with SQL Server is that gives me more possibilities for (pivot etc) queries.
I don't know the name of this program (SQL?) but for each "type of data" there are 3 files with the same names except their extensions.
These extensions are tablename.FRM, tablename.MYD and tablename.MYI. Maybe this rings a bell for some.
With mySQL (which is on a borrowed computer) I just plug in to read / query the data. This way I always get the last information, without the need to do any extra update work.
My question: is this possible to do with SQL Server, and if yes, how can I create this "dynamic" link.
Thanks in advance
Hein
PS
I hope this is the right forum to post this question

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

I'm not quite sure what you mean by a link to another system. Are you looking to query MySQL, Oracle, or some other system from within SQL Server?

You can look at Linked Servers inside SQL Server to do this.

George M Parker SSCertifiable Points: 5412 More actions · Answer 2

hjvegt1960 (10/21/2013)
Hi all
These extensions are tablename.FRM, tablename.MYD and tablename.MYI. Maybe this rings a bell for some.

These are MySQL database files. The .FRM is the table structure, the .MYD is the table data and the .MYI should be the indexes.

What specifically are you trying to accomplish? You could potentially create a linked server in SQL Server that points to the MySQL database if you want to be able to query it from SSMS.

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 3

Thank you Steve for your answer / help

I found the Linked Server you are referring to.

With your suggestion I found the Microsoft Technet site for more information.

What I find is a lot of technical "stuff".

I am not familiair with a lot of it, so I will have to dig in and read a lot.

EG, how to find the product name?

Do you know of a (good) site, in this forum / Sql Server Central or otherwise that explains all I need to know and/or fill in?

Anyway thanks for putting me on the right track! Thumbs up!

Hein

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Thanks George for your reply,

Between Steve's reply and yours I have answered Steve's.

In understand now what the .frm, .myd and .myi extensions are.

What I apparently want to do is the establish a linked server between SQL Server and mySQL.

So I will dig into the theory behind the creation of this linked server and hopefully will be able to create this.

I will get back if / when I have some more questions (if I may)

Hein

George M Parker SSCertifiable Points: 5412 More actions · Answer 5

You'll have to install the appropriate MySQL ODBC driver on your SQL Server first.

Here's a snippet of a linked server creation script to give you an idea of how it's setup, at least in our environment (You'll need to replace the values as appropriate):

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@server = N'TestServerName',

@srvproduct=N'MySQL',

@provider=N'MSDASQL',

@provstr=N'DRIVER={MySQL ODBC 5.1 Driver};SERVER=TestServerName;option=512;USER=user_login;PASSWORD=intentionally_removed;OPTION=3'

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 6

Very helpful George 🙂

Thank you for it.

I am sure this will help me on my way / quest.

Hein