SQL with Manoj

Linked Servers provides access to external datasources be it another databases like Oracle, MySQL, or Excel files.

Advantages:
– Remote server access.
– The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
– The ability to address diverse data sources similarly.

MSDN Links on Linked Servers:
Info: http://msdn.microsoft.com/en-us/library/ms188279(v=SQL.90).aspx
Configure: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

My idea here is to query MySQL tables in MS SQL Server as I’m more comfortable with MS SQL queries and semantics. I don’t know if or how I can use Ranking functions, case statements, etc in MySQL DB. Plus I’m also not comfortable in writing queries in DOS like editor or any other freeware tool.

Linked Server to MySQL:

Before creating a Linked Server for MySQL you need to install the MySQL ODBC connector.
Download MySQL ODBC Client: http://dev.mysql.com/downloads/connector/odbc/

Now we need a DSN that will act as a bridge between for creating the Linked Server:
Create a System DSN:
– On Control Pannel -> Admin Tools -> Data Sources (ODBC), Select System DSN tab, click ADD, Selct “MySQL ODBC 3.51 Driver”, Click Finish.
– A new pop-up will come up, “Connector/ODBC 3.51.27 – Configure Data Source Name”.
– On Login Tab: Set fields, Click Test.
– On Advanced Tab, go to following tabs and check the options:
– Flag1: Return Matching Rows, Allow Big Results, Use Compressed Protocol, Change BIGINT columns to Int, Safe
– Flag2: Don”t Prompt Upon Connect, Ignore # In Table Name
– Flag3: Return Table Names for SQLDescribeCol, Disable Transactions
– Click “OK”

Create a New Linked Server:
– On SSMS under Object Explorer go to “Server Objects” -> “Linked Servers”, Richt Click and select “New Linked Server”
– Set an appropriate name on “Linked Server”, like: MYSQL_LINK
– Server Type = Select “Other Data Source” radio button.
– Set Provider = Microsoft OLE DB Provider for ODBC Drivers
– Set the “Product Name” & “Data Source” field you set on configuring the DSN.

This can also be setup by following SQL statements:

-- Create New Linked Server
EXEC sp_addlinkedserver
   @server = 'MYSQL_LINK',
   @srvproduct = 'MySQLDatabase',
   @provider = 'MSDASQL',
   @datasrc = 'MySQLKayako'

-- Pull list of all Servers
select * from sys.servers
EXEC sp_linkedservers

-- Drop the Linked Server
EXEC sp_dropserver 'MYSQL_LINK'

Now you can query the tables and other objects of MySQL database by using OPENQUERY function as shown below:

-- Select a table or view
SELECT * FROM OPENQUERY(MYSQL_LINK, 'select * from MySQL_Table')
-- Execute a function
SELECT * FROM OPENQUERY(MYSQL_LINK, 'EXEC MySQL_Proc param1, param2')

OPENQUERY() also helps in selecting a Stored Procedure result just like a table.

MSDN Ref: http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/8a18fdc1-4738-4029-ba40-d4eae483720a/