I know, no post for a while. As I was wondering the country I let this one slip. However, last week was not without an interesting challenge. One of my attempts last week was to connection a MS SQL Server 2000 (source) database to a MySQL Database (target). More specifically, I wanted to insert values into my target based on a trigger in my source. Let me tell you, without help from this post by Ben Margolin. I will not repeat, but do a summary of what worked for me.
This I took from Ben's recommendation regarding adding a linked server to your MSSQL Database Server.
- Install Connector/ODBC for MySQL on your MSSQL server.
- Depending on your configuration, you may need to open ports between your servers (in my case the MySQL server was in a completely different domain controlled by another company).
- Create a system DSN on the MSSQL server then configure and test for connectivity to the MySQL server.
- Open Query Analyzer for MSSQL and execute the following stored procedure (see NOTE 1 for details). This is possible via the Enterprise Manager, but I prefer the SQL directly.
EXEC sp_addlinkedserver @server='LinkedAlias', @srvproduct='MySQL', @provider='MSDASQL', @datasrc='DsnName'
- Map target username and password using the following stored procedure. Please note that this may change if you prefer a different connection method (again, you can configure through Enterprise Manager if you prefer).
EXEC sp_addlinkedsvrlogin @rmtsrvname='LinkedAlias', @useself='false', @locallogin=NULL, @rmtuser='MySqlUsername', @rmtpassword='MySqlPassword'
- Confirm your connection by either refreshing Enterprise Manager (looking in Security - Linked Servers) or executing this stored procedure:
EXEC sp_helplinkedsrvlogin @rmtsrvname='LinkedAlias'
For the trigger/insertion part, I chose to create a stored procedure and call it from the trigger instead of putting all the code in the trigger. Either way is acceptable, but I wanted the option of calling the stored procedure from other locations (rather than only being able to use the trigger).
Here is the code for the stored procedure:
CREATE PROCEDURE [mysp_MySqlPush]
@id varchar(50)
AS
DECLARE @param1 varchar(100), @param2 varchar(100), @creationDate smalldatetime
DECLARE myCursor CURSOR FOR SELECT dbParam1, dbParam2 FROM myTable WHERE id = @id
OPEN myCursor
FETCH NEXT FROM myCursor INTO @param1, @param2
SELECT @creationDate = GetDate()
IF @@FETCH_STATUS = 0
BEGIN
INSERT INTO OPENQUERY(LinkedAlias, 'SELECT param1, param2, creationDate FROM MySqlTable WHERE 0=1') (param1, param2, creationDate) VALUES (@param1, @param2, @creationDate)
END
CLOSE myCursor
DEALLOCATE MyCursor
Next, I created the following trigger (see NOTE 2):
CREATE TRIGGER [trigMySqlPush] ON [dbo].[MyTable]
FOR INSERT
AS
SET XACT_ABORT ON
DECLARE @id varchar(50)
DECLARE trigCursor CURSOR FOR SELECT inserted.id FROM inserted
OPEN trigCursor
FETCH NEXT FROM trigCursor INTO @id
IF @@FETCH_STATUS = 0
BEGIN
exec mysp_MySqlPush @id
END
CLOSE trigCursor
DEALLOCATE trigCursor
NOTE 1: More information about the stored procedure can be found via books online or on MSDN. This article will discuss things such as the provider being MSDASQL for all ODBC connections.
NOTE 2: There are several noteworthy things. First of all, the call to XACT_ABORT was a necessity for me. You will want to review what XACT_ABORT does to see if you want to set it to ON. You can do this server wide instead of in the trigger, but I opted to only do it in the trigger. The other noteworthy thing for those that don't know, inserted is a system controlled table that holds the records of the most recent insert. Another thing is the statement for OPENQUERY. Without going into much detail, the 0=1 makes sure the remote server isn't queried for data (which would add to processing time) but the select statement is required to map columns. Last, there are several places in here to improve error trapping, etc., but this is a starting point.
Well, that's all. Enjoy this (and I hope it helps someone). Once again, proper thanks should go to Ben.
