primary key constraint violation, only when deploying DB via vsdbcmd

Question

I am using VS 2010 Premium RTMRel (10.0.30319.1) and SS2008. I have a database which builds AOK and has a post deployment script which loads some sample data. When I build and deploy this DB via the VS UI, it works just fine. When I try to deploy it via vsdbcmd, it fails on the data population with errors like this:

** * SQL01268 C:\source\mydatabase.sql (5197,0) .Net SqlClient Data Provider: Msg 2627, Level 14, State 1, Line 227 Violation of PRIMARY KEY constraint 'pk_customers'. Cannot insert duplicate key in object 'dbo.customers'.**

For demo purposes, I am trying to populate a specific record into the customers table and am overriding the IDENTITY property of the id column to set a value of my choosing. The relevant lines in my post-deployment script are these:

SET IDENTITY_INSERT [dbo].[customers] ON
INSERT INTO [dbo].[customers] ([id], [name], [notes]) VALUES ( 10001, N'ABC Co', NULL )
SET IDENTITY_INSERT [dbo].[customers] OFF

Does anyone know why I can override the IDENTITY column (by using SET IDENTITY INSERT ON) via the UI, but not via the command line?

And no, there is no possibility that there is already a record in the customers table with id = 10001. There are no other records (or inserts) in the table at all.

I believe that the "duplicate key in object" part of the error message is just completely wrong, and that it is objecting to me trying to put my own value into the PK column.

The PK constraint has the standard defaults. If I change the defaults and set IGNORE_DUP_KEY = ON, then the command line deploy works. But I don't want to have to do that.

Any insights or suggestions appreciated.

Answer 1

OK, embarassing.

It turned out that my post-deployment script had a USE statement that was specifying the default DB name (call it MobileDB). When I was building via the UI, I was also using this MobileDB name, the deployment configuration was always re-creating the database, and so everything worked fine.

However when I deployed from vsdbcmd I was using a random DB name (like ChickenSoup). So when the data population script ran it was trying to write back to MobileDB, hitting the data that was already there, and failing with a violation of a PK constraint.

Classic User Error.