problem with updating identity in transactional replication

Hey. I've a few transactional publications. It fails with this error. When I
looked at the command, it's trying to insert a null in identity column. Why
does it do that? As of now, I've commented the update for the identity column
in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
identity column, what would happen? The reason I need the identity property
on subscriber side is because these replicated tables will be horizontally
partitioned and replicated to another server. And I'm hoping to use
Transactional Replication for that. Please let me know what should be done
and what's the ideal. thank you.
Cannot update identity column 'DeviceBeaconID'.
(Source: XIAN\XIAN2 (Data source); Error number: 8102)
{CALL sp_MSupd_logDeviceBeacon
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
Transaction sequence number and command ID of last execution batch are
0x002BA62A00000E7E000100000000 and 1.
This looks like you update proc as opposed to your insert proc.
Take the proc and open it up in a text editor. In the bottom half of the
proc comment out the part where it updates the identity column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:728B16DD-98F6-41F1-925F-53C6684FD831@.microsoft.com...
> Hey. I've a few transactional publications. It fails with this error. When
> I
> looked at the command, it's trying to insert a null in identity column.
> Why
> does it do that? As of now, I've commented the update for the identity
> column
> in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
> identity column, what would happen? The reason I need the identity
> property
> on subscriber side is because these replicated tables will be horizontally
> partitioned and replicated to another server. And I'm hoping to use
> Transactional Replication for that. Please let me know what should be done
> and what's the ideal. thank you.
>
> Cannot update identity column 'DeviceBeaconID'.
> (Source: XIAN\XIAN2 (Data source); Error number: 8102)
>
> {CALL sp_MSupd_logDeviceBeacon
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
> 15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.
|||Yes, I'm sorry. I used the wrong word. It's trying to update the Ident column
with a 'NULL' value. Can you explain what this statement does?
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
I think this is the line you have asked me to comment out. In my case, I
checked the @.bitmap variable and I found it to be '0x8009' when it does a
substring, it'll be case 0, right? so, it'll go to the else statement,
right?
Now, the question is why does the sp try to insert a null? Is it because no
modifications were made to it?
Thank you for your help.
Tejas
|||It should not be trying to do a null update. I am really confused here
however, you persist in talking about inserts, for the life of me it should
be an update. Or perhaps you are that pesky Paul Ibison in disguise trying
to push me over the edge?
The @.bitmap dictates which columns are to be updated. It looks like for your
bitmask the else will be used which means that the identity value will be
updated to the same value. A Null is passed due to the call type you are
using MCALL IIRC. As the identity value is not updated on the publisher no
value is passed - i.e. a NULL is passed. If the identity column was updated
(if this is possible) a value would be passed here instead of the null.
I think your update proc portion should look like this
update "datObjects" set
-- "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
-- "ObjectTypeID" end
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:2D828E4F-AC1A-4875-83CB-85B58FC80A73@.microsoft.com...
> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
> with a 'NULL' value. Can you explain what this statement does?
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> I think this is the line you have asked me to comment out. In my case, I
> checked the @.bitmap variable and I found it to be '0x8009' when it does a
> substring, it'll be case 0, right? so, it'll go to the else statement,
> right?
> Now, the question is why does the sp try to insert a null? Is it because
> no
> modifications were made to it?
> Thank you for your help.
> Tejas
|||lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm sorry
again for using the word insert in my first post.Thank you very much for your
help. This was what I was actually looking for. Thank you.
|||Ok, thanks Paul.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:50263252-D63C-448A-8A89-470CABA983EA@.microsoft.com...
> lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm
> sorry
> again for using the word insert in my first post.Thank you very much for
> your
> help. This was what I was actually looking for. Thank you.
|||Hey Hilary. I could not do what you had asked. I'm pasting the whole
sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
Below, objectId is the identity column. But it doesn't exist in the else part.
I've pasted the sp as is. Made no modifications to it. So, if i comment the
objectid in the if part it works fine... Is it normal to be not there in the
else part? because that's where u asked me to comment it out.
-----
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else "ObjectID"
end
,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
|||Ok Paul, I think this is it
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
--on the safe side I will do this too
--"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"ObjectID"
--end
--,
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
--"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
--"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
>
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5FA239EC-5A40-4D2F-AB2F-570BF4B6FAF9@.microsoft.com...
> Hey Hilary. I could not do what you had asked. I'm pasting the whole
> sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
> Below, objectId is the identity column. But it doesn't exist in the else
> part.
> I've pasted the sp as is. Made no modifications to it. So, if i comment
> the
> objectid in the if part it works fine... Is it normal to be not there in
> the
> else part? because that's where u asked me to comment it out.
> -----
> CREATE procedure "sp_MSupd_datObjects"
> @.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
> bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
> bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
> ,@.bitmap binary(3)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "datObjects" set
> "ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "ObjectID"
> end
> ,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> ,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end,
> "ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> GO
>
|||Just wanted to know if you noticed that the commented lines in the sp are two
different fields...
In the if, it's objectid
in the else, it's objectTypeID.
These are two different columns with no correlation...
Is the commenting still correct?
Just want to be sure.
Thank you,
TEJAS
(not Paul)
|||Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:561E41D4-279E-4FF3-B976-8F75A2AA7689@.microsoft.com...
> Just wanted to know if you noticed that the commented lines in the sp are
> two
> different fields...
> In the if, it's objectid
> in the else, it's objectTypeID.
> These are two different columns with no correlation...
> Is the commenting still correct?
> Just want to be sure.
> Thank you,
> TEJAS
> (not Paul)