May 4, 2012 at 6:58 am
hello, i have an sql 2008 r2 instance which has transactional replication to a sql 2005 instance.
i have two sparse columns in which i created in one of my tables at my database in the sql 2008 r2 instance to be replicated to sql 2005 instance database.
my question is i was able to add that table with sparse columns to be replicated to my sql 2005 instance database without any errors.
when i check the properties of my sql 2008 r2 table i see the following for the properties for the sparse column:
unit345 (Sparse,varchar(max),null)
in my replicated table at my sql 2005 instance database, for the properties for the sparse column:
unit345 (varchar(max),null)
i had no errors when i added the article in my transactional replication, am i going to have any data corruption problems in the future? i thought it would throw me an error when trying to add this table to replication?
May 4, 2012 at 7:02 am
You should have explicitly inidicated NOT to replicate the sparse attribute during article creation. Here's a quote from BOL:
The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in SQL Server Management Studio. Earlier versions of SQL Server do not support sparse columns. If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.
May 4, 2012 at 7:16 am
is this something that the sp_addarticle has a default in sql 2008? I did not specify that when adding the article, this is the script i executed:
exec sp_addarticle @publication = N'DISTRIB', @article = N'TablewithSparseData', @source_owner = N'dbo', @source_object = N'TablewithSparseData', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @identityrangemanagementoption = N'manual', @destination_table = N'TablewithSparseData', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTablewithSparseData', @del_cmd = N'CALL sp_MSdel_dboTablewithSparseData', @upd_cmd = N'SCALL sp_MSupd_dboTablewithSparseData'
GO
May 4, 2012 at 7:21 am
It's a bitmask for the @schema_option param. The value of the sparse switch is: 0x20000000000.
May 4, 2012 at 7:33 am
thank you so much!, so once i specify that schema option, the column in my subscriber will still be replicated just without the sparse?
May 4, 2012 at 7:47 am
thanks again 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply