Daryl, What else was happening yesterday? Is it possible that someone was running a query or using DTS yesterday and turned off the identity insert flag. In addition, why was a primary key constraint (or fk constraint) violation not thrown? Is it possible these do not exist or were dropped?
-Mark -----Original Message----- From: Daryl Walsh [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 1:50 PM To: CF-Talk Subject: SQL Server Identity Seed hiccup in the process of merging 2 sql server databases into one, i created an Identity seed on the primary key on an Orders table and set the seed to the value of the max orderid + 1 from the first db's orders. added the 2nd db's orders to the new table and the database properly incremented the additional orderids starting with the identity seed +1. orders have been getting entered into the system and the orderid has been incrementing correctly (identity seed was in the 13000's, current max orderid is in the 22000's)--until yesterday. yesterday, 3 orders were entered but the database assigned Orderids to these orders *starting with the above Identity seed* and then incremented +1 from the identity seed for the next 2 orders. the database somehow stored the relevant data for these orders and connected it to these faulty orderids--the first orderid (also the identity seed value) was not in the database and the next 2 were already assigned to other orders already in the table. yet all three orders w/the data entered yesterday were returned from the database for a confirmation page using the faulty orderids. i.e., immediatelty after the orders were entered and the faulty orderids returned by sql server, those orderids were used to query the db and return the order data and actually returned the data entered yesterday for orderids which were already in the db and assigned to different order data. since then, test orders have been entered and sql server is using the proper orderid (currently in the 22000's vs. the 13000 identity seed which is where the faulty orderids from yesterday started). also, none of the 3 orders from yesterday can be pulled from the db with the proper data. the identity seed orderid is not in the db, never was. and the next two return the data for old orders as they should. current test orders are firing properly--sql server returns a properlty incremented orderid in the 22000's. why did it misfire on those 3 orders yesterday, returning to the Identity seed for the first orderid and incrementing +1 for the next two? was it because there's no order in the table with a value matching the identity seed, e.g., identity seed = 13000, but there's no orderid = 13000 in the table, causing sql server to try to use 13000 yesterday? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
