Displaying posts categorized under

Replication

SQL Server compatibility error in replication

SQL Server compatibility error in replication From last replication setup on my sandbox , i found wondering error. Error is like this Error messages: Source: Microsoft.SqlServer.Smo Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean) Message: Script failed for Table ‘Caregiver.SpecialAvailability’. Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, [...]

Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Error messages: Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544) Get help: http://help/544 Solution : You will get this type of error when you try to insert identity column in replication. You need to update column with not for replication. This option [...]

Partition Existing Replicated Table

Partition Existing Replicated Table. To create partition in existing replicated table you must have to remove article from replication. The reason is, to create partiiton in existing table you must have to recreate primary key and you can’t recreate primary key replicated table. so, remove table from replication is first step. Note : Before you [...]

Remove Article From Replication

Remove Article From Replication In transnational, replication you have to remove article from Subscription list and drop an Article. Step1 : Remove article from subscription. Step2 : Drop article. Step3 : Refresh Subscription. Step4 : Start snapshot sgent in replication to apply changes in snapshot. –> Replication Monitor. –> Expand Publisher and select Subscription. –> Goto [...]

Add Partitioned Table in Replication

Add Partitioned Table in Replication. Step1 : Create Partition Table. Step2 : Script to add Partition Table in existing Replication. Step3 : Refresh Subscription. Step4 : Start snapshot sgent in replication to apply changes in snapshot. –> Replication Monitor. –> Expand Publisher and select Subscription. –> Goto Agent Tab. –> Right click on Snaphot and [...]

Replication Issue – Duplication of Primary Key

Replication Issue – Duplication of Primary Key. Replication synchronizes data and article on subscriber same as it is on publisher. Changing and inserting data directly at subscriber cause to break replication. Please look the following error. Error Message Command attempted:   if @@trancount > 0 rollback tran (Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1) Error messages: [...]

Add article in Replication without initializing

Add article in Replication without initializing By this way you are only creating a snapshot only for newly added article and no need to reinitialize  all subscription. Step1 : Create New Table on Publisher. Step2 : Add this table in Article. First check Article exist or not in a replication. Run this to add “ReplicationTest” [...]

Script to get Replication Latency

Script to get Replication Latency. I have created monitoring script to get latency information for replication and status of replication health. I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database. It gives you same information which you can get from Replication Monitor –> Subscription Wath List. In SQL Server 2012 you just need to pass value only [...]

Script to get replication Undistributed Commands

Script to get replication Undistributed Commands Script to Monitor Replication by checking last refresh time and pending commands Regards, Nirav Gajjar

SSIS Package to Backup Replication Script using SMO

SSIS Package to Backup Replication Script. Hi, This package willl use some SMO reference and generates SQL Server replication backup script same as you do it manually like, Right click on Publication –> Generate Scripts…. I have created SSIS package that automatically creates backup folder for current date and save your all replication publication script [...]