Replicating a View from MSSQL to MYSQL part II

The stored procs are basically the same as when they were used for table to table replication.

One of the stored proc that we need to change is the ‘sp_addarticle’:

exec sp_addarticle @publication = N’repl_test_view’

, @article = N'repl_test_view'

, @source_owner = N'dbo'

, @source_object = N'repl_test_view'

, @type = N'indexed view logbased'

, @pre_creation_cmd = N'none'

, @ins_cmd = N'SQL'

, @del_cmd = N'SQL'

, @upd_cmd = N'SQL'

, @schema_option =0x8000000

, @status = 24

As you can see the major difference is at the @type variable, whereas we chose ‘logbased’ for the table to table replication. For indexed view replication it should say ‘indexed view logbased’, which really only makes sense.  I also had to change the @schema_option because we wanted to replicate a view, not a table.

I also added the stored proc of ‘sp_replicationdboption’

exec sp_replicationdboption @dbname= N'databasename'

, @optname = N'publish'

, @value = N'true'

GO

Easy enough, this saves the step of having to tell the MSSQL server that the database is indeed eligible for replication via the GUI.

Since I have not figured out a way for the initial replication to create the destination tables (I haven’t looked into it yet), I was forced to create the MYSQL tables to accept the replicated data. This brought me to another point of having to change the ‘datetime’ datatype from MSSQL to a ‘smalldatetime’ so that I would be able to then replicate over to MYSQL. 

For example, if I had a column named “date_turned_in”, and I wanted that as a ‘smalldatetime’ instead of the normal ‘datetime’, I would use CAST(table.date_turned_in AS smalldatetime) AS date_turned_in, I had to change the data types before replication, since the “allow data transformation” is set to false for the publication.

After all this was said and done, we are now replicating five views from a MSSQL server to 5 tables in a MYSQL server.

I am sure that there is more to come.