troubleshooting Question

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

Avatar of MaxwellTurner
MaxwellTurnerFlag for Canada asked on
ColdFusion LanguageMicrosoft SQL Server
4 Comments2 Solutions208 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
gdemaria

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros