blog.sufmade.me: 2012

The gist of the conversation below is regarding a Microsoft SQL Server error, "Violation of PRIMARY KEY constraint" that happens after a move from 1 environment to another.

The application was working previously, but no longer after the move.

What could possibly go wrong?

Sensitive information have been removed, but the entirety of the conversation is here, typos and all.

[10:10:44] Gymson Viente: im encountering this.. if you have any thoughts on this
[10:10:45] Gymson Viente: Violation of PRIMARY KEY constraint 'PK_TestValues'. Cannot insert duplicate key in object 'dbo.TestValues'. The duplicate key value is (devil).
The statement has been terminated.
[10:11:11] Muhammad Yussuf: in [database name]...?
[10:11:17] Gymson Viente: yup.. hehe
[10:11:28] Muhammad Yussuf: huh, that has never happened before
[10:11:36] Muhammad Yussuf: did they move DBs or something?
[10:11:38] Gymson Viente: heheh.. its not happening in QA also
[10:11:41] Gymson Viente: prod i mean
[10:11:51] Gymson Viente: yes.. its a new server.. everything is new
[10:11:54] Gymson Viente: [a QA URL]
[10:12:08] Gymson Viente: we have moved to [hosting provider A].. from [hosting provider B]
[10:12:23] Gymson Viente: its ok suf.. im investigating it now.. see if i can find something
[10:12:29] Muhammad Yussuf: sounds like... someone forgot to set the table's last id when they moved the data
[10:12:38] Gymson Viente: hmmm..last id?
[10:12:53] Muhammad Yussuf: yeah, i forgot what it's called... the correct term
[10:13:07] Gymson Viente: its in QATest db
[10:13:25] Gymson Viente: whats the logic on this?. so that i can find it on myself
[10:13:28] Gymson Viente: ill try
[10:13:35] Muhammad Yussuf: ms sql right?
[10:13:40] Gymson Viente: yes
[10:14:11] Muhammad Yussuf: its like this, when a new table is created, internally, MSSQL saves the last primary key == 1 and increments it when a new row is created
[10:14:32] Muhammad Yussuf: but when you import data, you switch it off, so you can import your primary key too
[10:14:49] Gymson Viente: ah.. didnt know that part
[10:15:07] Gymson Viente: so whats the best solution for this?
[10:15:17] Muhammad Yussuf: after import, you're supposed to set that internal number to the last number inserted and switch on the feature again
[10:16:13] Gymson Viente: actuallly.. i had some problems also.. adding input parameters.. but i did a workaround..
[10:16:22] Gymson Viente: at the parameters side i did this
[10:16:30] Gymson Viente: SET IDENTITY_INSERT parameters ON

INSERT INTO Parameters(id, methodid, Name) VALUES(2356,134,'startDateTime')
INSERT INTO Parameters(id, methodid, Name) VALUES(2357,134,'endDateTime')
[10:16:32] Gymson Viente: and it work
[10:16:39] Gymson Viente: before.. it never allowed me to insert
[10:17:20] Muhammad Yussuf: yeah, so basically, your primary keys of that table is screwed up
[10:17:27] Gymson Viente: tsktsktsk
[10:17:36] Gymson Viente: any way to fix this suf?
[10:17:56] Muhammad Yussuf: it's mostly a DBA issue... you should forward to [our DBA]?
[10:18:22] Gymson Viente: i am trying to talk to her on this.. if she has any ideas... heheh
[10:18:29] Gymson Viente: ok.. ill work with her on this
[10:18:31] Gymson Viente: thank you suf!
[10:19:02] Muhammad Yussuf: but you understand the basic issue now? (if you need to explain to [our DBA])
[10:19:39] Gymson Viente: yeah.. when it was imported, the primary key was never turned off.. right?
[10:20:04] Gymson Viente: so that messes up everything.. is that it?
[10:21:17] Muhammad Yussuf: http://williamsportwebdeveloper.com/cgi/wp/?p=153
[10:21:24] Muhammad Yussuf: http://blog.sqlauthority.com/2010/04/04/sql-server-enable-identity-insert-import-expert-wizard/
[10:21:48] Gymson Viente: ok let me read it through
[10:22:23] Muhammad Yussuf: "This results in many errors when the web application attempts to insert new records without providing a value for the identity field because it is suppose to auto-increment"
[10:22:53] Gymson Viente: oh.. is that it?.. so the main solution for this.. is just to turn on the auto increment..
[10:23:31] Gymson Viente: but what if.. she never imported it.. she just backup and restore the DB.. that shouldnt happen right?
[10:23:38] Muhammad Yussuf: you can check, but i got a felling that it's already on... but it thinks the next number is 2, which already exists because of the import
[10:23:48] Gymson Viente: oh
[10:24:45] Muhammad Yussuf: so, the table needs to be set that the next number is 2001 or something (the number of the last pri key) so that the next row will be 2002
[10:24:45] Gymson Viente: ya ive seen its already auto increment
[10:26:23] Muhammad Yussuf: i think when you create an alter table script, it shows the next number under constraints
[10:27:07] Gymson Viente: alter to is not enable
[10:27:10] Gymson Viente: enabled
[10:29:55] Muhammad Yussuf: found it!
[10:29:56] Muhammad Yussuf: http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
[10:30:30] Muhammad Yussuf: basically alter table with a new seed. seed is the starting point. last row id. increment is 1
[10:31:36] Muhammad Yussuf: but.. if it's more than 1 or a few tables... delegate it to the DBAs...
[10:31:59] Gymson Viente: let me try to read on it
[10:32:23] Gymson Viente: which one is altering it suf?
[10:36:54] Gymson Viente: so i tried running this
[10:36:54] Gymson Viente: DBCC CHECKIDENT ('TestCases', NORESEED)
[10:37:02] Gymson Viente: it returns this
[10:37:03] Gymson Viente: Checking identity information: current identity value '12', current column value '1716'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[10:37:14] Muhammad Yussuf: yeap
[10:37:30] Muhammad Yussuf: that confirms my suspicion
[10:37:46] Gymson Viente: so its expects.. 1716
[10:37:50] Gymson Viente: or +1
[10:37:58] Gymson Viente: the last number i see on the table is 1690
[10:38:26] Muhammad Yussuf: set your seed to 1717, to be safe
[10:38:42] Gymson Viente: a! no.. the last id i see is 1716
[10:39:03] Gymson Viente: so when entering a new entry, it should be ok righjt?
[10:39:53] Muhammad Yussuf: if you don't so the ALTER ...IDENTITY... SEED=1717, MS SQL will try to insert a new row as prikey = 13...
[10:40:00] Muhammad Yussuf: *do
[10:40:38] Muhammad Yussuf: which may cause a pri key constraint error, as prikey row 13 may already exist due to import
[10:41:02] Gymson Viente: so i need to alter the table having identity 1777.. is it?
[10:41:18] Muhammad Yussuf: correct
[10:41:31] Muhammad Yussuf: 1717
[10:43:53] Gymson Viente: you have a script to do that?.. i couldnt find it here.. the ones i see in google are all reseeding
[10:44:50] Gymson Viente: this script
[10:44:51] Gymson Viente: DBCC CHECKIDENT
(
TestCases,RESEED, 1717
)
[10:45:37] Gymson Viente: this is correct right?
[10:46:28] Muhammad Yussuf: not sure... :P copy a temp table to try it out... 1716 rows ain't a lot ;)
[10:46:33] Gymson Viente: hehehe
[10:46:36] Gymson Viente: ok
[10:48:09] Muhammad Yussuf: http://www.dbforums.com/microsoft-sql-server/664830-script-reset-identity-seed-identity-increment-values.html
[10:48:14] Muhammad Yussuf: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/
[10:48:19] Muhammad Yussuf: http://dbaspot.com/ms-sqlserver/141929-script-alter-identity-identity-seed-identity-increment.html
[10:48:29] Muhammad Yussuf: some links i found on the topic
[10:49:01] Gymson Viente: ya this should work DBCC CHECKIDENT (tablename, RESEED, 0)
[10:49:12] Muhammad Yussuf: 0?
[10:58:09] Gymson Viente: but i think i let [our DBA] decide on this... dont want to break anything on the DB's end
[10:58:10] Gymson Viente: heheh
[10:58:50] Muhammad Yussuf: yeap, just present her the facts we found... she should know the fix and all affected tables
[10:58:58] Gymson Viente: ok... thanks suf

blog.sufmade.me

Tuesday, December 18, 2012

Cleaned Up Picasa Web Albums

Thursday, November 8, 2012

Violation of PRIMARY KEY constraint

Tuesday, October 2, 2012

Trek 1.2 (2012) + Blackburn EX-1 Rack

Sunday, April 29, 2012

I Am Become Darth, Destroyer Of Worlds

Friday, April 27, 2012

In The Clouds