SQL server 2005 Replication Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL server 2005 Replication Problem

Author

Topic

n.param
Starting Member

12 Posts

Posted - 2008-07-09 : 01:47:55

We are having two servers one is SQL server2000 (A) and another one is SQL server2005 (B). We are using transactional replication and push subscription.

Database SQL 2005

B
Data base SQL 2005

A

B has a Table in Name
Webbid
1 Slno (Primary Key) and Indentity
2 Amount
3. date

A has a Table in Name
Webbid
1. Slno (Primary Key)
2. Amount
3. Date

B has another Table in Name
Webbid_temp
1 Slno
2 Amount
3. date

A has another Table in Name
Webbid_temp
1 Slno (Primary Key) Indentity
2 Amount
3 Date

Configuration
• B Webbid table is replicating to A Webbid table
• A webbid_Temp is replicating to B Webbid_temp
• B Webbid_temp holds a Insert trigger which will insert records into B Webbid table

Scenario 1:
Inserting record thru insert statement on B Webbid table
Result: the data gets replicated to A Webbid table

Scenario 2:
Inserting Records to A weebid_Temp table
Result: The data gets replicated to B Webbid_temp table

Scenario 3:
Inserting Records to A weebid_Temp table since we have a Insert trigger
the Insert trigger is fired and data gets inserted into B Webbid table, but the inserted data is not replicated to A webbid table. Error occurs replication fails

Command attempted:
{CALL sp_MSins_dboWebBid_EAuction (16, 4927, 1050, 1, 40, 2008-06-25 11:54:10.537)}
(Transaction sequence number: 0x000372A300000417000500000000, Command ID: 1)

Error messages:
Explicit value must be specified for identity column in table 'WebBid' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
Get help: http://help/545

For the above error we have used a procedure which Turns off the Not For Replication Option

use B
GO
exec sp_msforeachtable @command1='
declare @int int
set @int = object_id("?")
exec sys.sp_identitycolumnforreplication @int , 0'

No error after the execution of the above statement

Problem:

But after the execution of the Inserttrigger to B Webbid the replication has to get happen which is not happening

We are able to see the data from Publisher to distributor and Not from distributor to subscriber

Note:
In all the case the Scenario 1 is working perfectly and the above said senario works in Sql server 2000 perfectly

Thanks
Regards
Param

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-10 : 13:42:38

not sure if it works but try restarting the service .. if its not a production system..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-10 : 13:59:16

btw, I did not find the proc sys.sp_identitycolumnforreplication in BOL.. am guessing its an undocumented proc..
the proper way would be to use ALTER TABLE... ALTER COLUMN.. SET NOT FOR REPLICATION.. you can generate the script for tables with identity columns..

something like this:

select 'ALTER TABLE ' + so.name + ' ALTER COLUMN ' + sc.name + ' SET NOT FOR REPLICATION'
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
where so.type = 'u'
and sc.is_identity = 1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

n.param
Starting Member

12 Posts

Posted - 2008-07-11 : 01:09:21

Thanks Dinakar for the response.... I have totally removed the Identity column... I have set only as primary Key then also We are not able to replicate

n.param
Starting Member

12 Posts

Posted - 2008-07-11 : 01:14:58

This is not a Production system... We have restarted the server... then Also we have the same Problem

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-12 : 00:03:16

How do you set replication method in article properties? Use sp or column list?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-12 : 02:14:14

if you can script out all the three tables + the trigger + couple of INSERT scripts that are causing the error I can try to replicate it on my system and see whats going on..I really cant think of anything else until I see it in action..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

n.param
Starting Member

12 Posts

Posted - 2008-07-15 : 08:40:22

Note: For Testing purpose I am configuring the replication on same server.Step 1 Create Database A:

Step 1 Create Database A

CREATE DATABASE [A] ON PRIMARY
( NAME = N'A', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\A.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'A_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\A_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'A', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [A].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [A] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [A] SET ANSI_NULLS OFF
GO
ALTER DATABASE [A] SET ANSI_PADDING OFF
GO
ALTER DATABASE [A] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [A] SET ARITHABORT OFF
GO
ALTER DATABASE [A] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [A] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [A] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [A] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [A] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [A] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [A] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [A] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [A] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [A] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [A] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [A] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [A] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [A] SET READ_WRITE
GO
ALTER DATABASE [A] SET RECOVERY FULL
GO
ALTER DATABASE [A] SET MULTI_USER
GO
ALTER DATABASE [A] SET PAGE_VERIFY CHECKSUM
GO
USE [A]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [A] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Step 2 Create Table Table2 on A:
USE [A]
GO
/****** Object: Table [dbo].[Table2] Script Date: 07/14/2008 10:03:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table2](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ANo] [int] NOT NULL,
[CNo] [int] NOT NULL,
[LNo] [int] NOT NULL,
[Amount] [bigint] NOT NULL,
[BDate] [datetime] NOT NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Step 3 Create Database B
CREATE DATABASE ON PRIMARY
( NAME = N'B', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\B.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'B_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\B_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'B', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC .[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE SET ANSI_NULLS OFF
GO
ALTER DATABASE SET ANSI_PADDING OFF
GO
ALTER DATABASE SET ANSI_WARNINGS OFF
GO
ALTER DATABASE SET ARITHABORT OFF
GO
ALTER DATABASE SET AUTO_CLOSE OFF
GO
ALTER DATABASE SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [B] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [B] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [B] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [B] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [B] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [B] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [B] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [B] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [B] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [B] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [B] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [B] SET READ_WRITE
GO
ALTER DATABASE [B] SET RECOVERY FULL
GO
ALTER DATABASE [B] SET MULTI_USER
GO
ALTER DATABASE [B] SET PAGE_VERIFY CHECKSUM
GO
USE [B]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [B] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

[b]Step 4 Create Table Table1 on B

USE [B]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AccNo] [int] NOT NULL,
[CNo] [int] NOT NULL,
[LNo] [int] NOT NULL,
[Amount] [bigint] NOT NULL,
[BDate] [datetime] NOT NULL,
CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

[b]Step 5 Configure Distirbution

/****** Scripting replication configuration for server KCP-TECH-SK01. Script Date: 7/14/2008 10:18:38 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server KCP-TECH-SK01 as a Distributor. Script Date: 7/14/2008 10:18:38 AM ******/
use master
exec sp_adddistributor @distributor = N'KCP-TECH-SK01', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'KCP-TECH-SK01', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

[b]Step 6 Create Publication on A
use [A]
exec sp_replicationdboption @dbname = N'A', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [A]
exec sp_addpublication @publication = N'PublicationfromAtoB', @description = N'Transactional publication of database ''A'' from Publisher ''KCP-TECH-SK01''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'PublicationfromAtoB', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [A]
exec sp_addarticle @publication = N'PublicationfromAtoB', @article = N'Table2', @source_owner = N'dbo', @source_object = N'Table2', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Table2', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTable2', @del_cmd = N'CALL sp_MSdel_dboTable2', @upd_cmd = N'SCALL sp_MSupd_dboTable2'
GO

[b]Step 7 Create Publication On B

use [B]
exec sp_replicationdboption @dbname = N'B', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [B]
exec sp_addpublication @publication = N'PublicationfromBtoA', @description = N'Transactional publication of database ''B'' from Publisher ''KCP-TECH-SK01''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'PublicationfromBtoA', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [B]
exec sp_addarticle @publication = N'PublicationfromBtoA', @article = N'Table1', @source_owner = N'dbo', @source_object = N'Table1', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Table1', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTable1', @del_cmd = N'CALL sp_MSdel_dboTable1', @upd_cmd = N'SCALL sp_MSupd_dboTable1'
GO

[b]Step 8 Create Push Subscription to A from B
-----------------BEGIN: Script to be run at Publisher 'KCP-TECH-SK01'-----------------
use [B]
exec sp_addsubscription @publication = N'PublicationfromBtoA', @subscriber = N'KCP-TECH-SK01', @destination_db = N'A', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PublicationfromBtoA', @subscriber = N'KCP-TECH-SK01', @subscriber_db = N'A', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20080714, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'KCP-TECH-SK01'-----------------

[b]Step 9 Push Subscription to B from A

-----------------BEGIN: Script to be run at Publisher 'KCP-TECH-SK01'-----------------
use [A]
exec sp_addsubscription @publication = N'PublicationfromAtoB', @subscriber = N'KCP-TECH-SK01', @destination_db = N'B', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PublicationfromAtoB', @subscriber = N'KCP-TECH-SK01', @subscriber_db = N'B', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20080714, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'KCP-TECH-SK01'-----------------

[b]Step 10 Create Trigger on Table2 on Database B

USE [B]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TRIGG_Table1_Insert]
ON [dbo].[Table2]
FOR INSERT
AS
DECLARE @ID INT
SET @ID = (SELECT ISNULL(MAX(ID),0)+1 FROM Table1)
set identity_insert Table1 on

INSERT INTO Table1(ID,ACCNO,CNO,LNO,AMOUNT,BDATE)
SELECT @ID,I.ANO,I.CNO,I.LNO,I.AMOUNT,I.BDATE FROM INSERTED I

=================================================================

[b]Note:

My Scenario is, I insert the a Record into the Table2 on Database A
through the script "Insert on A Database.sql"

[b]Result:

The Data is replicating to the Table2 on Database B
The Trigger is firing and inserting the replicated data on Table1 on Database B
But, The data is not replicating to the Table1 on Database A.

When I Query the SP_BROWSEREPLCMDS on Distribution Database
I am able to see the transaction

0x00000019000000D90009 KCP-TECH-SK01 A 1 30 0 1 NULL NULL 0x00000000000000000000 {CALL [sp_MSins_dboTable1] (1,111,11,111,120,2008-07-14 11:20:27.567)} 1

Even though the data is not replicating to the Database A.

Thx & Rgds,

n.param
Starting Member

12 Posts

Posted - 2008-07-17 : 04:27:09

Is there anyone to solve this problem

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-17 : 17:09:06

There is something wrong with your scripts.. I was able to replicate the issue with the scripts.. so I deleted the subscribers.. reset them up through the GUI and it worked perfectly fine for me..
With your scripts, when you look under articles being published for publication "PublicationFromAtoB" you will notice both tables 1 and 2 are being published..which is incorrect.. only Table2 should be published..I fixed this through the GUI and it worked like you expected it to.. I inserted a row in A.Table2. IT got replicated to B.TAble2. The trigger on B.Table2 inserted the row into B.Table1 which got replicated into A.Table1.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

n.param
Starting Member

12 Posts

Posted - 2008-07-18 : 04:14:45

Thanks alot for the reply. Great that U have done with the problem. Can U post your script.

For a double confirmation What you had tried is in sql server 2005 enterprise edition of the same server am I correct

Param

quote:
Originally posted by dinakar

There is something wrong with your scripts.. I was able to replicate the issue with the scripts.. so I deleted the subscribers.. reset them up through the GUI and it worked perfectly fine for me..
With your scripts, when you look under articles being published for publication "PublicationFromAtoB" you will notice both tables 1 and 2 are being published..which is incorrect.. only Table2 should be published..I fixed this through the GUI and it worked like you expected it to.. I inserted a row in A.Table2. IT got replicated to B.TAble2. The trigger on B.Table2 inserted the row into B.Table1 which got replicated into A.Table1.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-18 : 16:49:22

I have removed everything now. I have SQL Server build 3068. both databases were on same server. Like I said, go into articles list for PublicationAtoB and see what you have..you should have only one article.. from the scripts it created both Table1 and Table2.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources