Avatar of MaxwellTurner
MaxwellTurner
Flag for Canada asked on

Insert Record into a Replicated table with Identity column (PK) via ColdFusion - neec to RESEED replicated table

SQL Server 2008
CF Server 9

I am trying to insert a record into a replicated table via ColdFusion, but it crashes telling me I cannot add a duplicate Primary Key.   The Primary Key is an IDENTITY column (increment +1).   I know that subscribed tables are supposed to be read only, but I only replicate once a day, so I want to insert a row into the replicated table so the user can see instant information.  This same information gets added to the original table (via script) at the end of each day so it will be in the replicated table in 24 hrs, but I don't want the user to have to 'wait until tomorrow'.

I've figured out that in the replicated table, the identity seems to have reset itself and all I need to do is use:

DBCC CHECKIDENT ( mytable, RESEED )

an it works fine.

My question:  I need this to be automatic.  I assume that each time I Publish/Subscribe, the subscribed table's INDENTITY will be reset back to null again.  Is there any way to have my subscribed table to RESEEDED via ColdFusion before I insert the row?  Or run a script when I Subscribe the table?

Max
ColdFusion LanguageMicrosoft SQL Server

Avatar of undefined
Last Comment
gdemaria

8/22/2022 - Mon
SOLUTION
MaxwellTurner

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MaxwellTurner

ASKER
Sorry, I forgot to finish this off.  I have provided gdemaria with an assist as I ended up solving my own problem with the solution I provided, however, gdemaria's is also a good option, thus the assist.

Max
gdemaria

It's not necessary to reset identity, to keep records in sync, simply allow identity insert so the pkeys can remain the same
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy