problem with updating identity in transactional replication

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tejas Parikh - 30 Nov 2005 00:38 GMT

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.

Reply to this Message

Hilary Cotter - 30 Nov 2005 01:40 GMT

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.

Signature

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

> Hey. I've a few transactional publications. It fails with this error. When
> I
[quoted text clipped - 18 lines]
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.

Reply to this Message

Tejas Parikh - 30 Nov 2005 15:53 GMT

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

Reply to this Message

Hilary Cotter - 30 Nov 2005 16:15 GMT

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

Signature

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

> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
[quoted text clipped - 16 lines]
>
> Tejas

Reply to this Message

Tejas Parikh - 30 Nov 2005 16:51 GMT

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.

Reply to this Message

Hilary Cotter - 30 Nov 2005 17:20 GMT

Ok, thanks Paul.

Signature

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

> 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.

Reply to this Message

Tejas Parikh - 30 Nov 2005 18:48 GMT

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

Reply to this Message

Hilary Cotter - 30 Nov 2005 19:35 GMT

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

Signature

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

> 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.
[quoted text clipped - 95 lines]
>
> GO

Reply to this Message

Tejas Parikh - 30 Nov 2005 21:18 GMT

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)

Reply to this Message

Hilary Cotter - 30 Nov 2005 21:21 GMT

Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?

Signature

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

> Just wanted to know if you noticed that the commented lines in the sp are
> two
[quoted text clipped - 10 lines]
> TEJAS
> (not Paul)

Reply to this Message