SQL server 2005 Replication Problem

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