Cannot update identity column 'PointOfSaleID'.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry Herbinaux - 26 Jul 2006 18:24 GMT

We are receiving an error when the distribution agent executes the generated
stored procedure sp_MSupd_PointOfSales. The bit flag for the identity column
is not set and the identity column is Yes (Not For Replication).

The sp_MSupd_PointOfSales stored procedure checks to see if the bit flag is
set and if so it executes an update statement which includes the identity
column and if not it executes an update statement without the identity column.

When I change the stored procedure by commenting out the update statement
for the identity column, it executes fine and chooses the else path. So this
indicates to me that the logic is correct, but an error is being generated
prior to execution due to a verification process when the update statement is
not commented out. Here are some questions:

1) Not For Replication - I thought the distribution agent uses this flag to
allow updates / inserts into the identity column, so why should we receive
the error during the verification process?

2) In what cases would the identity column bit be set? Why does the if case
get generated for the stored procedures that update a table?

Below are the details of the call, the result, and the generated stored
procedure (I have commented out the line that was giving us problems and
added a couple of PRINT statements to show the execution path):

{CALL sp_MSupd_PointOfSales
(NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,NULL,NULL,0,0,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2006-07-25
15:55:54.000,'Website','HTML','',NULL,NULL,NULL,4952,0x00000000001e)}
Transaction sequence number and command ID of last execution batch are
0x0001431C00001C66000400000000 and 1.

NO POSID
(1 row(s) affected)
(1 row(s) affected)

CREATE procedure [sp_MSupd_PointOfSales]
@c1 int,@c2 int,@c3 bit,@c4 varchar(255),@c5 varchar(50),@c6
varchar(50),@c7 varchar(50),@c8 varchar(50),@c9 varchar(50),@c10
varchar(50),@c11 varchar(24),@c12 varchar(50),@c13 varchar(50),@c14
varchar(24),@c15 varchar(24),@c16 varchar(2),@c17 varchar(10),@c18
varchar(40),@c19 varchar(50),@c20 varchar(30),@c21 varchar(255),@c22
char(4),@c23 bit,@c24 bit,@c25 int,@c26 bit,@c27 int,@c28 int,@c29 bit,@c30
bit,@c31 int,@c32 bit,@c33 bit,@c34 bit,@c35 varchar(50),@c36
varchar(50),@c37 varchar(50),@c38 varchar(50),@c39 datetime,@c40
varchar(20),@c41 int,@c42 datetime,@c43 varchar(20),@c44 varchar(10),@c45
varchar(20),@c46 datetime,@c47 int,@c48 datetime,@pkc1 int
,@bitmap binary(7)
as
if substring(@bitmap,1,1) & 1 = 1
begin
PRINT 'POSID'
update [PointOfSales] set
--[PointOfSaleID] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else
[PointOfSaleID] end
--,
[DealerID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [DealerID]
end
,[Active] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Active] end
,[POSTicketIcon] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else
[POSTicketIcon] end
,[LoginName] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else
[LoginName] end
,[MailingAddressLine1] = case substring(@bitmap,1,1) & 32 when 32 then @c6
else [MailingAddressLine1] end
,[MailingAddressLine2] = case substring(@bitmap,1,1) & 64 when 64 then @c7
else [MailingAddressLine2] end
,[MailingCity] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else
[MailingCity] end
,[MailingCountry] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else
[MailingCountry] end
,[ContactEmail] = case substring(@bitmap,2,1) & 2 when 2 then @c10 else
[ContactEmail] end
,[ContactFax] = case substring(@bitmap,2,1) & 4 when 4 then @c11 else
[ContactFax] end
,[ContactFirstName] = case substring(@bitmap,2,1) & 8 when 8 then @c12 else
[ContactFirstName] end
,[ContactLastName] = case substring(@bitmap,2,1) & 16 when 16 then @c13 else
[ContactLastName] end
,[ContactPhone1] = case substring(@bitmap,2,1) & 32 when 32 then @c14 else
[ContactPhone1] end
,[ContactPhone2] = case substring(@bitmap,2,1) & 64 when 64 then @c15 else
[ContactPhone2] end
,[MailingState] = case substring(@bitmap,2,1) & 128 when 128 then @c16 else
[MailingState] end
,[MailingZipcode] = case substring(@bitmap,3,1) & 1 when 1 then @c17 else
[MailingZipcode] end
,[Password] = case substring(@bitmap,3,1) & 2 when 2 then @c18 else
[Password] end
,[PointOfSaleName] = case substring(@bitmap,3,1) & 4 when 4 then @c19 else
[PointOfSaleName] end
,[TrackingNumber] = case substring(@bitmap,3,1) & 8 when 8 then @c20 else
[TrackingNumber] end
,[URL] = case substring(@bitmap,3,1) & 16 when 16 then @c21 else [URL] end
,[LangCodes] = case substring(@bitmap,3,1) & 32 when 32 then @c22 else
[LangCodes] end
,[Show_AccountInfo] = case substring(@bitmap,3,1) & 64 when 64 then @c23
else [Show_AccountInfo] end
,[CheckForAnalogV3Updates] = case substring(@bitmap,3,1) & 128 when 128 then
@c24 else [CheckForAnalogV3Updates] end
,[TimeZoneID] = case substring(@bitmap,4,1) & 1 when 1 then @c25 else
[TimeZoneID] end
,[HasUser] = case substring(@bitmap,4,1) & 2 when 2 then @c26 else [HasUser]
end
,[UserQuantityLimit] = case substring(@bitmap,4,1) & 4 when 4 then @c27 else
[UserQuantityLimit] end
,[LanguageID] = case substring(@bitmap,4,1) & 8 when 8 then @c28 else
[LanguageID] end
,[isBulk] = case substring(@bitmap,4,1) & 16 when 16 then @c29 else [isBulk]
end
,[isTestAccount] = case substring(@bitmap,4,1) & 32 when 32 then @c30 else
[isTestAccount] end
,[PSID] = case substring(@bitmap,4,1) & 64 when 64 then @c31 else [PSID] end
,[Allow_HTML] = case substring(@bitmap,4,1) & 128 when 128 then @c32 else
[Allow_HTML] end
,[Allow_AnalogV3] = case substring(@bitmap,5,1) & 1 when 1 then @c33 else
[Allow_AnalogV3] end
,[Allow_XML] = case substring(@bitmap,5,1) & 2 when 2 then @c34 else
[Allow_XML] end
,[DealerRefNbr] = case substring(@bitmap,5,1) & 4 when 4 then @c35 else
[DealerRefNbr] end
,[StoreID] = case substring(@bitmap,5,1) & 8 when 8 then @c36 else [StoreID]
end
,[Entity] = case substring(@bitmap,5,1) & 16 when 16 then @c37 else [Entity]
end
,[StoreTrackingID] = case substring(@bitmap,5,1) & 32 when 32 then @c38 else
[StoreTrackingID] end
,[lastCloseOut] = case substring(@bitmap,5,1) & 64 when 64 then @c39 else
[lastCloseOut] end
,[TerminalSerialNumber] = case substring(@bitmap,5,1) & 128 when 128 then
@c40 else [TerminalSerialNumber] end
,[CardSetID] = case substring(@bitmap,6,1) & 1 when 1 then @c41 else
[CardSetID] end
,[LastConnectionTime] = case substring(@bitmap,6,1) & 2 when 2 then @c42
else [LastConnectionTime] end
,[LastConnectionAction] = case substring(@bitmap,6,1) & 4 when 4 then @c43
else [LastConnectionAction] end
,[LastConnectionConduit] = case substring(@bitmap,6,1) & 8 when 8 then @c44
else [LastConnectionConduit] end
,[LastConnectionVersion] = case substring(@bitmap,6,1) & 16 when 16 then
@c45 else [LastConnectionVersion] end
,[CreationDate] = case substring(@bitmap,6,1) & 32 when 32 then @c46 else
[CreationDate] end
,[CreationAdmin] = case substring(@bitmap,6,1) & 64 when 64 then @c47 else
[CreationAdmin] end
,[LastCatalogUpdate] = case substring(@bitmap,6,1) & 128 when 128 then @c48
else [LastCatalogUpdate] end
where [PointOfSaleID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
else
begin
PRINT 'NO POSID'
update [PointOfSales] set
[DealerID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [DealerID]
end
,[Active] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Active] end
,[POSTicketIcon] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else
[POSTicketIcon] end
,[LoginName] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else
[LoginName] end
,[MailingAddressLine1] = case substring(@bitmap,1,1) & 32 when 32 then @c6
else [MailingAddressLine1] end
,[MailingAddressLine2] = case substring(@bitmap,1,1) & 64 when 64 then @c7
else [MailingAddressLine2] end
,[MailingCity] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else
[MailingCity] end
,[MailingCountry] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else
[MailingCountry] end
,[ContactEmail] = case substring(@bitmap,2,1) & 2 when 2 then @c10 else
[ContactEmail] end
,[ContactFax] = case substring(@bitmap,2,1) & 4 when 4 then @c11 else
[ContactFax] end
,[ContactFirstName] = case substring(@bitmap,2,1) & 8 when 8 then @c12 else
[ContactFirstName] end
,[ContactLastName] = case substring(@bitmap,2,1) & 16 when 16 then @c13 else
[ContactLastName] end
,[ContactPhone1] = case substring(@bitmap,2,1) & 32 when 32 then @c14 else
[ContactPhone1] end
,[ContactPhone2] = case substring(@bitmap,2,1) & 64 when 64 then @c15 else
[ContactPhone2] end
,[MailingState] = case substring(@bitmap,2,1) & 128 when 128 then @c16 else
[MailingState] end
,[MailingZipcode] = case substring(@bitmap,3,1) & 1 when 1 then @c17 else
[MailingZipcode] end
,[Password] = case substring(@bitmap,3,1) & 2 when 2 then @c18 else
[Password] end
,[PointOfSaleName] = case substring(@bitmap,3,1) & 4 when 4 then @c19 else
[PointOfSaleName] end
,[TrackingNumber] = case substring(@bitmap,3,1) & 8 when 8 then @c20 else
[TrackingNumber] end
,[URL] = case substring(@bitmap,3,1) & 16 when 16 then @c21 else [URL] end
,[LangCodes] = case substring(@bitmap,3,1) & 32 when 32 then @c22 else
[LangCodes] end
,[Show_AccountInfo] = case substring(@bitmap,3,1) & 64 when 64 then @c23
else [Show_AccountInfo] end
,[CheckForAnalogV3Updates] = case substring(@bitmap,3,1) & 128 when 128 then
@c24 else [CheckForAnalogV3Updates] end
,[TimeZoneID] = case substring(@bitmap,4,1) & 1 when 1 then @c25 else
[TimeZoneID] end
,[HasUser] = case substring(@bitmap,4,1) & 2 when 2 then @c26 else [HasUser]
end
,[UserQuantityLimit] = case substring(@bitmap,4,1) & 4 when 4 then @c27 else
[UserQuantityLimit] end
,[LanguageID] = case substring(@bitmap,4,1) & 8 when 8 then @c28 else
[LanguageID] end
,[isBulk] = case substring(@bitmap,4,1) & 16 when 16 then @c29 else [isBulk]
end
,[isTestAccount] = case substring(@bitmap,4,1) & 32 when 32 then @c30 else
[isTestAccount] end
,[PSID] = case substring(@bitmap,4,1) & 64 when 64 then @c31 else [PSID] end
,[Allow_HTML] = case substring(@bitmap,4,1) & 128 when 128 then @c32 else
[Allow_HTML] end
,[Allow_AnalogV3] = case substring(@bitmap,5,1) & 1 when 1 then @c33 else
[Allow_AnalogV3] end
,[Allow_XML] = case substring(@bitmap,5,1) & 2 when 2 then @c34 else
[Allow_XML] end
,[DealerRefNbr] = case substring(@bitmap,5,1) & 4 when 4 then @c35 else
[DealerRefNbr] end
,[StoreID] = case substring(@bitmap,5,1) & 8 when 8 then @c36 else [StoreID]
end
,[Entity] = case substring(@bitmap,5,1) & 16 when 16 then @c37 else [Entity]
end
,[StoreTrackingID] = case substring(@bitmap,5,1) & 32 when 32 then @c38 else
[StoreTrackingID] end
,[lastCloseOut] = case substring(@bitmap,5,1) & 64 when 64 then @c39 else
[lastCloseOut] end
,[TerminalSerialNumber] = case substring(@bitmap,5,1) & 128 when 128 then
@c40 else [TerminalSerialNumber] end
,[CardSetID] = case substring(@bitmap,6,1) & 1 when 1 then @c41 else
[CardSetID] end
,[LastConnectionTime] = case substring(@bitmap,6,1) & 2 when 2 then @c42
else [LastConnectionTime] end
,[LastConnectionAction] = case substring(@bitmap,6,1) & 4 when 4 then @c43
else [LastConnectionAction] end
,[LastConnectionConduit] = case substring(@bitmap,6,1) & 8 when 8 then @c44
else [LastConnectionConduit] end
,[LastConnectionVersion] = case substring(@bitmap,6,1) & 16 when 16 then
@c45 else [LastConnectionVersion] end
,[CreationDate] = case substring(@bitmap,6,1) & 32 when 32 then @c46 else
[CreationDate] end
,[CreationAdmin] = case substring(@bitmap,6,1) & 64 when 64 then @c47 else
[CreationAdmin] end
,[LastCatalogUpdate] = case substring(@bitmap,6,1) & 128 when 128 then @c48
else [LastCatalogUpdate] end
where [PointOfSaleID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
GO

Reply to this Message

Hilary Cotter - 27 Jul 2006 02:01 GMT

answers inline.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

> We are receiving an error when the distribution agent executes the
> generated
[quoted text clipped - 20 lines]
> allow updates / inserts into the identity column, so why should we receive
> the error during the verification process?

That is correct nfr does not enforce the identity
property/constraint/trigger if the DML originates from a replication
process. However your problem is that you can't update the identity value.
The proc does not detect columns with the identity property.

> 2) In what cases would the identity column bit be set? Why does the if
> case
[quoted text clipped - 3 lines]
> procedure (I have commented out the line that was giving us problems and
> added a couple of PRINT statements to show the execution path):

Just comment it out in both sections and be done with it.

Have a look at the article for more info on this problem

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-re
plication/

Reply to this Message

Larry Herbinaux - 27 Jul 2006 04:22 GMT

Hilary,

Thank you for the response. A couple questions:

1) Why does the sp_scriptpublicationcustomprocs store procedure generate the
sp_MSupd_* stored procedures with both cases? I can't see a reason to have
the one with the IDENTITY field being updated. Is there a scenario where
this is useful?

2) I fixed my issue by manually removing the entire IF / ELSE clause and
leaving the Update that did not have the IDENTITY field for all tables that
used an IDENTITY for the primary key. I decided not to change the IF / ELSE
clause for tables that did not use an IDENTITY for the primary key because it
is possible to change the fields of a primary key that is not an IDENITITY,
this makes sense, right?

3) Automatically generating the sp_MSupd_* stored procedures - I guess, I
could write a script that loops through all articles and call
sp_scriptdynamicupdproc to generate correct sp_MSupd_* stored procedures. I
could then apply the script generated by sp_scriptpublicationcustomprocs
followed by this new script to override the original sp_MSupd_* stored
procedures, correct?

Thanks,

Larry Herbinaux

> answers inline.
>
[quoted text clipped - 41 lines]
>
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-re
plication/

Reply to this Message

Hilary Cotter - 28 Jul 2006 14:05 GMT

1) the update proc is not coded to handle updates to columns with the
identity property, it assumes that all columns do not have the identity
property on them. The reason for this is that it is not a good practice to
update primary keys (which most often have the identity property on it).
However Microsoft is aware that some developers do not follow this good
practice hence they have the second clause.

2) Exactly, just make sure that the where clause does pinpoint the row that
has to be modified.

3) Yes this is an good option.

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

> Hilary,
>
[quoted text clipped - 85 lines]
>>
>> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-re
plication/

Reply to this Message

Larry Herbinaux - 28 Jul 2006 19:40 GMT

Thank you very much Hilary and thanks for the reference materials.

Yeah, I know its not good practice to change the primary key and we don't do
this is our operational software, but I could conceive a case where we could
write a maintenance script for a table such as (ServerID, PropNameID,
PropValue) where the primary key is (ServerID, PropNameID) and for some
reason we might want to modify the PropNameID.

Thanks again,

Larry Herbinaux

> 1) the update proc is not coded to handle updates to columns with the
> identity property, it assumes that all columns do not have the identity
[quoted text clipped - 97 lines]
> >>
> >> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-re
plication/

Reply to this Message