An Alternative to Sync Data from SQL Server to MySQL
So I downloaded and installed MySQL ODBC Connector for my Windows 7. I set up an ODBC data source and then a linked server, but I got this error message:
I googled and googled and could not fixed this issue, even after I re-installed SQL Server (Anybody would be much appreciated if letting me know how to fix it by sending email to to_scottleo@yahoo.com).
I have to turn to a get-around.
My idea is write a trigger to call a CLR stored procedure which calls a web service. The web service will perform MySQL data manipulation.
Let’s start.
Step 1: Download and install MySQL Connector/.Net for Windows. Create a class library in Visual Studio 2008. Let’s name it MySqlDataManipulation.
using System;
using MySql.Data.MySqlClient;
namespace MySqlDataManipulation
{
public class MySqlData
{
private MySqlConnection _conn;
public MySqlData(string sConnStr)
{
_conn = new MySqlConnection(sConnStr);
}
public int ExecuteNonQuery(string sSqlStmt)
{
_conn.Open();
MySqlCommand cmd = new MySqlCommand(sSqlStmt, _conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sSqlStmt;
int returnCode = cmd.ExecuteNonQuery();
return returnCode;
}
}
}
Build it and copy the DLL file to the web bin directory for our web service to use it.
Step 2: Create an ASP.Net web service.
using System;
using System.Web.Services;
using MySqlDataManipulation;
using System.Web.Configuration;
[WebService]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebSyncService : System.Web.Services.WebService {
public WebSyncService () {
}
[WebMethod]
public void SyncMySQLData(string sSqlStmt)
{
String sMySqlConnStr = WebConfigurationManager.ConnectionStrings["SyncMySQL"].ToString();
MySqlData d = new MySqlData(sMySqlConnStr);
d.ExecuteNonQuery(sSqlStmt);
return;
}
}
Step 3: Use Visual Studio 2008 to build a CLR stored procedure assembly
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MySQLRealTimeSync.com.xxxxx.www;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SaveData(String sSqlStmt)
{
WebSyncService sync = new WebSyncService();
sync.SyncMySQLData(sSqlStmt);
}
}
Before compiling, set the project build output to generate serialization assembly to “On”. After compiling we’ll get two files: MySQLRealTimeSyncStoredProcedure.dll and MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll.
Then in SQL Server Management Studio, register new assemblies MySQLRealTimeSyncStoredProcedure,MySQLRealTimeSyncStoredProcedure.XmlSerializers against these two DLLs respectively. Remember to set "Permission set" to "External access". It is also probably needed to run following codes depending on the database settings.
EXEC sp_configure 'clr enabled', 1
reconfigure;
GO
EXEC ('ALTER DATABASE dbname SET TRUSTWORTHY ON')
GO
Step 4: Write a regular stored procedure.
CREATE PROCEDURE MySqlDataSync
@sSqlStmt nvarchar(255)
AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;
GO
Step 5: Write a table trigger
USE MyDatabase
IF OBJECT_ID('MySchema.TR_SyncToMySQL', 'TR') IS NOT NULL
DROP TRIGGER MySchema.TR_SyncToMySQL
GO
SET NOCOUNT ON
GO
CREATE TRIGGER MySchema.TR_SyncToMySQL ON MySchema.MyTable
FOR INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE
@InsertCount integer,
@DeleteCount integer,
@TableName varchar(255),
@MySQLTableName varchar(255),
@FieldName nvarchar(255),
@IdentityColName nvarchar(255),
@IdentityColVal nvarchar(255),
@OldValue nvarchar(max),
@NewValue nvarchar(max),
@ColCount bigint,
@ColTotal bigint,
@SqlString varchar(max),
@SqlStringTemp varchar(max),
@HasModifiedCol bit
SET @MySQLTableName = 'MySQLTableName'
SET @InsertCount = (SELECT Count(*) FROM INSERTED)
SET @DeleteCount = (SELECT Count(*) FROM DELETED)
SELECT @TableName = OBJECT_NAME(PARENT_OBJ)
FROM SYSOBJECTS
WHERE id = @@PROCID
SELECT @ColTotal = COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
SET @ColCount = 0
SELECT @IdentityColName = name
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND
COLUMNPROPERTY(id, name, 'IsIdentity') = 1
IF OBJECT_ID('tempdb..#myTemp') IS NOT NULL
DROP TABLE #myTemp;
CREATE TABLE #myTemp (FieldValue nvarchar(max));
IF @InsertCount > @DeleteCount -- insert action
BEGIN
DECLARE @ValueString nvarchar(max)
WHILE ((SELECT @ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted1') IS NOT NULL
DROP TABLE #myInserted1;
SELECT * INTO #myInserted1 FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myInserted1';
EXEC(@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF @SqlString IS NULL
SET @SqlString = 'INSERT ' + @MySQLTableName+ ' (';
IF @ValueString IS NULL
SET @ValueString = ') VALUES ('
SET @SqlString = @SqlString + @FieldName + ', '
SET @ValueString = @ValueString + '''' + @NewValue + ''', '
END
EXEC dbo.MySqlDataSync REPLACE(@SqlString + @ValueString + ')', ', )', ')')
END
ELSE IF @InsertCount < @DeleteCount -- delete action
BEGIN
WHILE ((SELECT @ColCount) <= @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName <> @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myDeleted1') IS NOT NULL
DROP TABLE #myDeleted1;
SELECT * INTO #myDeleted1 FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @IdentityColName + ' AS nvarchar(max)) FROM #myDeleted1';
EXEC(@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
END
EXEC dbo.MySqlDataSync 'DELETE FROM ' + @MySQLTableName+ ' WHERE ' + @IdentityColName + ' = ''' + @IdentityColVal + ''''
END
ELSE IF @InsertCount = @DeleteCount -- update action
BEGIN
SET @HasModifiedCol = 0
WHILE ((SELECT @ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted') IS NOT NULL
DROP TABLE #myInserted;
SELECT * INTO #myInserted FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @IdentityColName + ' AS nvarchar(max)) FROM #myInserted';
EXEC(@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myInserted';
EXEC(@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF OBJECT_ID('tempdb..#myDeleted') IS NOT NULL
DROP TABLE #myDeleted;
SELECT * INTO #myDeleted FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myDeleted';
EXEC(@SqlStringTemp);
SET @OldValue = (SELECT FieldValue FROM #myTemp);
IF @OldValue IS NULL SET @OldValue = ''
IF @SqlString IS NULL
SELECT @SqlString = 'UPDATE ' + @MySQLTableName+ ' SET '
IF @NewValue <> @OldValue
BEGIN
SET @HasModifiedCol = 1
SET @SqlString = @SqlString + @FieldName + ' = ''' + @NewValue + ''', '
END
END
-- remove the last comma
SELECT @SqlString = LTRIM(REVERSE(@SqlString))
SELECT @SqlString = REVERSE(SUBSTRING(@SqlString, 2, LEN(@SqlString)))
SELECT @SqlString = @SqlString + ' WHERE ' + @IdentityColName + ' = ''' + @IdentityColVal + ''''
IF @HasModifiedCol = 1
BEGIN
PRINT @SqlString
EXEC dbo.MySqlDataSync @SqlString
END
END
END
GO
Here it is supposed that the MySQl table has the same structure and table fields with the SQL Server table. And a catch here about the trigger is that it can be used for any table without any other changes except for the table names.
"