HOWTO: Setup SQL Server Linked Server to MySQL

Introduction

MS SQL servers always provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible way of achieving the same thing, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB datasource points to the specific database that can be accessed using OLEDB.
In this article we will be creating a MySQL linked server on SQL Server 2008 and querying a database [TestMove] table shown in the next listing . In reviewing the previous article it may be noticed that the Employees tables was moved to MySQL database TestMove. In running the commands from the mysql> prompt it is assumed that the MySQL Server has been started.

Listing 1: employees table in TestMove

mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees          |
+--------------------+
1 row in set (0.09 sec)

mysql>

Creating an ODBC DSN for MySQL

In the previous article on MySQL Servers cited earlier, a DSN was created for moving data. Essentially the same DSN can be used. Herein follows a brief review of the DSN MySQL_Link created along the same lines as in the previous referenced article. The ODBC driver used for creating this ODBC DSN is the one installed on the machine when the MySQL Server was installed as shown.

The final interactive window where you may test the connectivity is shown in the next figure. You may notice that the database Testmove has been named in the ODBC DSN. The name MySQL_LINK is the ODBC DSN.

When you close the window after clicking the OK button, a ODBC DSN item will be added to the System DSN tab of the ODBC wizard as shown.

Steps to create a linked server from Management Studio

Right click the Linked Servers node to display a drop-down menu as shown in the next figure.

Click on New Linked Server...item. This brings up the New Linked Server window as shown. The window is all empty except for a default Provider.

The very first thing to do is to provide a name for this linked server. Herein it is LINKED_ MYSQL. We will be using one of the providers [Microsoft OLE DB Provider for ODBC] supported by SQL Server 2008. You may access the list of OLE DB Providers in the Providers sub-folder of the Linked Servers. Provide the other details as shown in the next figure. Make sure they are entered exactly as shown or according to how you have created the database on MySQL Server.

Click on the Security list item under General in the left. This opens the 'Security' page of the New Linked Server wizard as shown. Change the login from the default "Be made without using a security context" to "Be made using this security context". Enter remote login. In this case it is "root" for the remote login and the password is the one used during the ODBC DSN (also the password for server authentication) creation.

Make no changes to the Server Options page. Click OK. This creates a linked server Linked_MySQL as shown expanded in the Linked Server's node as shown. You may need to right click and refresh the Linked Servers' node to see the new linked server. As you can see in the figure, the 'User' tables are not displayed.

Running Queries and reviewing results

Running system stored procedures can provide various levels of information and the database can be queried using the four part syntax and the openquery() method.

Information on the linked server

It is easy to find how the linked server is configured using system stored procedure sp_linkedsrvlogin on the SQL Server 2008. Open a Query window from File | New | Query Current Connection to open the query window and type in the following statement. The next figure shows the statement as well as the returned values. SQL Server 2008 querying has the intellisense report and this must be put to good use.

Exec sp_linkedsrvlogin

This shows all servers both local and remote as shown in the next figure.

Information about the tables on the remote server can also be accessed by running a stored procedure. Executing the stored procedure sp_tables_ex as shown in the next figure (which displays the statement and the result of executing the stored procedure) can be used to obtain table information.

Querying the table on the database

Data in the table on the linked server can be queried using the openquery() function. The syntax for this function shown next is very simple.

openquery ('linked server', 'query')

The next figure shows the result of running the openquery() function on the Linked_MySQL linked server.

Although it should be possible to query the linked server using the four part syntax as in:

Select * from LINKED_MYSQL...employees

The above statement returns an error. This is probably a limitation of a combination of MSDASQL and the ODBC driver which does not provide the schema information correctly(this is just the author's opinion).

Are Remote Procedure Calls (RPC) allowed?

The easiest way to test this is to send out a call by running the following query against the linked server.

Execute('Select FirstName, LastName from employees') at Linked_MYSQL

If the linked server is not configured for RPC, then the result you get by running the above query is as shown in the next figure.

Turn on RPC

Earlier on we skipped the Server Options page of the linked server. Back in the Management Studio right click linked server LINKED_MYSQL and from the drop-down choose to look at the properties at the bottom of the list. This brings up the LINKED_MYSQL properties window as shown. Click on Server Options. In the Server Options page change the values of RPC and RPCOUT to true, default for both being false.

Now run the query that produced the error previously. The result is displayed in the next figure.

You might have noted that only two columns were returned from the employees table. This was deliberate as trying to get all the column would produce an error due partly to the data types of data stored in the table and their compatibility with MSDASQL and the ODBC driver (Again, an author's opinion).

Creating Linked Server using TSQL

While the linked server can be created using the built-in wizard of the Management Studio, it can also be created using TSQL statements as in the following listing (run both statements, the first one creates the linked server and the second the logins).

Listing 2:

 Exec master.dbo.sp_addlinkedserver
@server=N'MySQlbyCode',
@srvprodcut=N'MySQL',
@provider=N'MSDASQL',
@datasrc=N'MySQL_link'

Exec master.dbo.sp_addlinkedserverlogin
@server=N'MySQlbyCode',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword=N'<your password>'
@rmtsrvname=N'localhost'

Summary

The article described the steps involved in configuring a MySql Linked server on SQL Server 2008 using the built-in New Linked Server wizard as well as TSQL. Method to query the linked server as well as enabling RPC were described.


source: https://www.packtpub.com/books/content/mysql-linked-server-sql-server-2008

How do I login as root?

Be aware that the root account is not active by default.
You will need to do:

sudo passwd root

Then go ahead to set password. Use the root user using

su root

The setup routines for the MySQL ODBC 5.2 ANSI Driver ODBC driver could not be loaded due to system error code 193

MySQL Connector/ODBC 5.2 -- Error 1918.Error installing ODBC driver MySQL ODBC 5.2 ANSI Driver, ODBC error 13: The setup routines for the MySQL ODBC 5.2 ANSI Driver ODBC driver could not be loaded due to system error code 193: .. Verify that the file MySQL ODBC 5.2 ANSI Driver exists and that you can access it.

Before running the x64 installer of "MySQL ODBC connector" on "Windows Server 2008 R2" you need to download an install "Microsoft Visual C++ 2010 Redistributable Package (x64)" from http://www.microsoft.com/en-us/download/details.aspx?id=14632

If it still can't run, you try to do the following

1. remove manually the "C:\Windows\System32\msvcr100.dll" and
2. Reinstall the program mentioned above (vcredist_x64).

detail: http://bugs.mysql.com/bug.php?id=70752
 

SqlBulkCopy.WriteToServer Method (DataTable)

Copies all rows in the supplied DataTable to a destination table specified by the

DestinationTableName property of the SqlBulkCopy object.

Namespace:  System.Data.SqlClient

Parameters

table

Type: System.Data.DataTable
A DataTable whose rows will be copied to the destination table.

Remarks

All rows in the DataTable are copied to the destination table except those that have been deleted.
While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.
The ColumnMappings collection maps from the DataTable columns to the destination database table.

Examples

The following Console application demonstrates how to bulk load data from a DataTable. The destination table is a table in the AdventureWorks database.
In this example, a DataTable is created at run time and is the source of the SqlBulkCopy operation.

Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

C#

VB

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a connection to the AdventureWorks database. 
        using (SqlConnection connection =
                   new SqlConnection(connectionString))
        {
            connection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoMatchingColumns;",
                connection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Create a table with some rows. 
            DataTable newProducts = MakeTable();

            // Create the SqlBulkCopy object.  
            // Note that the column positions in the source DataTable  
            // match the column positions in the destination table so  
            // there is no need to map columns.  
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = 
                    "dbo.BulkCopyDemoMatchingColumns";

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(newProducts);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            // Perform a final count on the destination  
            // table to see how many rows were added. 
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static DataTable MakeTable()
        // Create a new DataTable named NewProducts. 
    {
        DataTable newProducts = new DataTable("NewProducts");

        // Add three column objects to the table. 
        DataColumn productID = new DataColumn();
        productID.DataType = System.Type.GetType("System.Int32");
        productID.ColumnName = "ProductID";
        productID.AutoIncrement = true;
        newProducts.Columns.Add(productID);

        DataColumn productName = new DataColumn();
        productName.DataType = System.Type.GetType("System.String");
        productName.ColumnName = "Name";
        newProducts.Columns.Add(productName);

        DataColumn productNumber = new DataColumn();
        productNumber.DataType = System.Type.GetType("System.String");
        productNumber.ColumnName = "ProductNumber";
        newProducts.Columns.Add(productNumber);

        // Create an array for DataColumn objects.
        DataColumn[] keys = new DataColumn[1];
        keys[0] = productID;
        newProducts.PrimaryKey = keys;

        // Add some new rows to the collection. 
        DataRow row = newProducts.NewRow();
        row["Name"] = "CC-101-WH";
        row["ProductNumber"] = "Cyclocomputer - White";

        newProducts.Rows.Add(row);
        row = newProducts.NewRow();
        row["Name"] = "CC-101-BK";
        row["ProductNumber"] = "Cyclocomputer - Black";

        newProducts.Rows.Add(row);
        row = newProducts.NewRow();
        row["Name"] = "CC-101-ST";
        row["ProductNumber"] = "Cyclocomputer - Stainless";
        newProducts.Rows.Add(row);
        newProducts.AcceptChanges();

        // Return the new DataTable.  
        return newProducts;
    }
    private static string GetConnectionString()
        // To avoid storing the connection string in your code,  
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

Version Information

.NET Framework

Supported in: 4.5.1, 4.5, 4, 3.5, 3.0, 2.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Platforms

Windows Phone 8.1, Windows Phone 8, Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)
The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx

Import CSV file directly into MySQL

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.


To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile 'user.csv' into table tbluser fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(userid, username, rankid)

The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

Don't use .NET System.Uri.UnescapeDataString in URL Decoding

Don't use .NET System.Uri.UnescapeDataString in URL Decoding

URL Encoding should encode Space into "+" or "%20". URL Decoding should decode "+" or "20" into Space. However by design, System.Uri.UnescapeDataString doesn't decode "+" into Space.

The MSDN remark of Uri.UnescapeDataString says:

 “Many Web browsers escape spaces inside of URIs into plus ("+") characters; however, the UnescapeDataString method does not convert plus characters into spaces because this behavior is not standard across all URI schemes.”

The issue will rise when your web application has query string like:

http:// www.ms.com/default?Comment=just+do+it

If you use System.Uri.UnescapeDataString to decode the query string value "just+do+it", the result is "just+do+it" instead of "just do it".  When the downstream application need to URL encode the value again, it becomes "just%2bdo%2bit ". The final URL will looks like

http:// www.ms.com/default?Comment=just%2bdo%2bit

The spaces get lost and application could interpret the value as "just+do+it" instead of "just do it".

Detailed discussion:

RFC2396 defined reserved characters such as &, $, + and excluded characters such as space, %, < > must be escaped (URL encoded) when used as values in query string of URL in order to keep the original meaning of the character.

For example: to pass information such as

Products : Windows&Office Price: $200 Comment: In Stock Sign:+

The URL could be

http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In%20Stock&sign=%2b

or

http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In+Stock&sign=%2b

URL may be used as return URL value in other URL. In the case, the URL need to be encoded and already encoded characters will be double encoded.

http%3a%2f%2fwww.ms.com%2fdefault.aspx%3fProducts%3dWindows%2526Office%26Price%3d%2524200%26Comment%3dIn%2520Stock%26sign%3d%252b

or

http%3a%2f%2fwww.ms.com%2fdefault.aspx%3fProducts%3dWindows%2526Office%26Price%3d%2524200%26Comment%3dIn%2bStock%26sign%3d%252b

Characters	Single Encoded	Double Encoded
&	%26	%2526
$	%24	%2524
+	%2b	%252b
Space	%20, +	%2520, %2b
%	%25	%2525
<	%3c	%253c

Notice Space's single encoding can be "+" and double encoding can be "%2b" and + sign's single encoding is %2b.

If the function doesn't handle the encoding properly, the original meaning of the character could be lost in transaction.

The right encoding or decoding methods should do what the above table defines.

.NET encoding methods

Characters	HttpUtility.UrlEncode	System.Uri.EscapeDataString	System.Uri.EscapeUriString
&	%26	%26	&
$	%24	%24	$
+	%2b	%2B	+
Space	+	%20	%20
%	%25	%25	%25
<	%3c	%3C	%3C

Notice:

1. System.Uri.EscapeUriString doesn't encode RFC reserved characters

2. URLEncode encodes Space as "+" and EscapeDataString encode Space as "%20".

3. To encode the whole URL as return URL, EscapdeUriString should not be used.

.NET Methods	http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In+Stock&sign=%2b
URLEncode	http%3a%2f%2fwww.ms.com%2fdefault.aspx%3fProducts%3dWindows%2526Office%26Price%3d%2524200%26Comment%3dIn%2bStock%26sign%3d%252b
EscapeDataString	http%3A%2F%2Fwww.ms.com%2Fdefault.aspx%3FProducts%3DWindows%2526Office%26Price%3D%2524200%26Comment%3DIn%2BStock%26sign%3D%252b
EscapdeUriString (not right)	http://www.ms.com/default.aspx?Products=Windows%2526Office&Price=%2524200&Comment=In+Stock&sign=%252b

Or

.NET Methods	http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In%20Stock&sign=%2b
URLEncode	http%3a%2f%2fwww.ms.com%2fdefault.aspx%3fProducts%3dWindows%2526Office%26Price%3d%2524200%26Comment%3dIn%2520Stock%26sign%3d%252b
EscapeDataString	http%3A%2F%2Fwww.ms.com%2Fdefault.aspx%3FProducts%3DWindows%2526Office%26Price%3D%2524200%26Comment%3DIn%2520Stock%26sign%3D%252b
EscapdeUriString (not right)	http://www.ms.com/default.aspx?Products=Windows%2526Office&Price=%2524200&Comment=In%2520Stock&sign=%252b

There are two decoding methods in .NET

Encoded Characters	HttpUtility.UrlDecode	System.Uri.UnescapeDataString
%26	&	&
%24	$	$
%2b	+	+
%20	Space	Space
+	Space	+
%25	%	%
%3c	<	<

Notice that UrlDecode UnescapeDataString decode "+" differently. This will cause problem when decoding return URL which contains double encoded Space as "%2b".

For example:         "Comment%3dIn%2bStock" in encoded return URL should be double decoded into

Variable: "Comment"          Value: "In Stock"

Call UrlDecode twice on it

"Comment%3dIn%2bStock"  à "Comment=In+Stock" à "Comment=In Stock"

Call UnescapeDataString twice on it

"Comment%3dIn%2bStock"  à "Comment=In+Stock" à "Comment=In+Stock"

The original string "In Stock" is broken by UnescapeDataString.

If the downstream application assumes the URL string had be restored to not encoded format "In Stock" and use it as input to encode it again, the single encoding will become

"Comment=In+Stock" à "Comment%3dIn%2bStock"

Instead of

"Comment=In Stock" à "Comment=In+Stock"

Conclusion:

Since an application has no control of its upstream (use input or config), it can only assume the right encoding is in the URL query string: Single encoded special character as query string parameter value. Especially the Space can be "+" or "%20". When the URL needs to used as return URL in query string, it must be encoded again. Space will be double encoded as "%2b" or %2520".

When the receiving application received the encoded URL, if it uses method like UnescapeDataString for decoding, the "%2b" will not decoded into Space, Instead it becomes "+" as final result.

Developer should avoid encoding Space into "+" or double encoded into "%2b". It is recommended that when encode URL use "System.Uri.EscapeDataString", when decode URL use " HttpUtility.UrlDecode"

Tester should ensure that

1. Reserved and Excluded characters as defined by RFC2396 should be singled encode when used as value in query string of URL as next table. (URL as links, config values or test values).

2. If the URL is used in return URL or value of another query string, the Reserved and Excluded characters should be doubled encoded as next table.

Characters	Single Encoded	Double Encoded
&	%26	%2526
$	%24	%2524
+	%2b	%252b
Space	%20, +	%2520, %2b
%	%25	%2525
<	%3c	%253c

Two test URL can be

http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In%20Stock&sign=%2b

or

http://www.ms.com/default.aspx?Products=Windows%26Office&Price=%24200&Comment=In+Stock&sign=%2b

source: http://blogs.msdn.com/b/yangxind/archive/2006/11/09/don-t-use-net-system-uri-unescapedatastring-in-url-decoding.aspx