No duplicates exist but get Msg 2627, Cannot insert duplicate key in object

On SQL Server 2000, a transaction of thousands of INSERTs contains these three consecutive INSERT statements:

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', 0, 1)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05e51bee-ac3b-4257-90f1-f34adcb185cb', 0, 128)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05f33ded-979b-41ee-9d6c-ac5adc4d8635', -1000000000, 1000000000)

The transaction fails with a Msg 2627 error, Violation of PRIMARY KEY constraint 'ARR_Constraint2'. Cannot insert duplicate key in object 'PARAMETER_ATTRIBUTES'. SQL Profiler reveals that the last statement executed is the second INSERT.

The primary key is on the ID column. What is particularly significant is that before running the transaction, this query returns no rows:

select * from PARAMETER_ATTRIBUTES
where id in (
'05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', -- pk value in first insert
'05e51bee-ac3b-4257-90f1-f34adcb185cb', -- pk value in second insert
'05f33ded-979b-41ee-9d6c-ac5adc4d8635') -- pk value in third insert

In other words, all three INSERTs contain primary key values that do not exist in the table. As you can see, each INSERT is inserting a unique value for the primary key. I am asserting that there are no duplicates. How can the fact of a Msg 2627 error be reconciled with the facts of no duplicates? Can SQL Profiler not be trusted to show the specific statement causing a duplicate key problem? In other words, in a transaction of 1000s of statements, does a dup key problem cause SQL Profiler to stop before it displays the statement causing the error?

Is there, by any chance, a trigger involved?

It does not look, on the surface, that you can both get no results from the select and the duplicate error. Therefore, we need to question the assumptions. Make sure that you can make the select return results from a known existant string. You might put the select after each of the selects.

What datatype are you using for the id?

That is how I would approach the debugging.

|||

No triggers exist.

The id column, the primary key column, is a uniqueidentifier.

|||Can you run the insert with just those three statements? One of the other batch inserts earlier in the order could contain an id with the same value. What you really need to do is run the select... where id in.... right before the insert, then just don't insert any rows after the insert, and see if there were rows existing the moment before statement 2 is run.|||The id value only exists in that one INSERT statement. I suspect that the last INSERT shown in SQL Profiler is not the INSERT causing the problem.|||

Further testing reveals that the the duplicate key INSERT occurs 100s of lines before the last statement shown in SQL Profiler.

Here is a detailed explanation of what is happening:

1. A very popular and well regarded database update utility is being used to update 100s of very similar databases.

2. This vendor's product puts all of the 10,000s of statements in a single transaction.

3. Within the single transaction are many batches of statements.

4. Within a particular batch, there is a statement that causes a duplicate key error. Not on the QA database, not on the many real databases, just on one developer's database.

5. The last statement in the batch does not cause a duplicate key error. It was the statement I was looking at because it was the last one the SQL Profiler displayed. I also looked at the immediately preceding and following (first statement of next batch) INSERT statements. None of these statements could or did cause a duplicate key error.

What we can conclude from this is that depending on how statements are grouped and processed, the last statement displayed by SQL Profiler in an abort situation may not physically be anywhere near the statement actually causing the problem.

NFR needed in Foreign Key constraint?

Chris,
have a look at this article:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q
302/3/41.ASP&NoWebContent=1
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
That's great for one row, but reality is multiple rows can get updated.
Will the NFR work, what are my options? Doing a backup/restore to synch
subscriber, do I drop all FK contraints after restore and replace w/ indexes?
What is best practice? Subscribers used to offload reporting, so selects only
occuring against subscr.
Thanks,
Chris
"Paul Ibison" wrote:

> Chris,
> have a look at this article:
> http://support.microsoft.com/default.aspx?
> scid=http://support.microsoft.com:80/support/kb/articles/q
> 302/3/41.ASP&NoWebContent=1
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My understanding is that to have TR work for a multi-row
update, you'd need to disable the FKs entirely.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Next Inserts ID Number, someone please help

G'day,
I have a table with a primary key being a bigint and its set to auto increment (or identity or whatever ms calls it). Is there anyway I can get the ID number that will be assigned to the next Insert before I insert it? I want to use that ID number within another field when inserted.

I hope that makes sense.

Thanks for any help.

Robboare you using a stored procedure?? If so, you can get the id from the @.@.IDENTITY variable

select @.@.IDENTITY|||Sorry, the id won't be allocated until the actual insert
(You have to think of there might be many parallell inserts..)
What you can do is to leave the other field blank
and then create an after-trigger that picks up the inserted id and
updates the other field with it.

Regards
Fredrik|||::Is there anyway I can get the ID number that will be assigned to the next Insert before I
::insert it?

Waht good would this be, given that another instance of the ap etc. could already have used up the number? This ppiece of information is unreliable unless you get into VERY bad programming practices.

Or do you seriously propose to create a table level lock at this moment?

Or have you simply not thought about the consequences of your wish?

News with database

hi how i can make news bar related with DB ?

i have 2 table for news one for newsName,primary key(newsID), descNews,date,forign(flagNo) _ and another for kind of user (primary(kindID),flagName

i want to display news in different page each page display news for one kinde of user

means iwant to make

select newsID,newsName from News where (flageNo=1)

oR(flageNo=2)

pleaaaaaaaaaaase help me to make this in details

thank you for each member help me

>hi how i can make news bar related with DB ?
Could you explain more clearly what you are trying to achieve?

NEWSEQUENTIALID sample code

We're currently converting the database of our asset management system to a SQL2005-database. The SQL2000 database uses an INT32 as the primary key with a clustered index on that column on most tables. Needless to say that the current environment is not ideal for replication, so we're investigating the use of GUIDs as primary keys.

We know the usual recommendations as far as GUIDs are concerned (4 times bigger, slower, not good for clustered indexes, yet better for replication). However, on one of the ascend-program training days one of my colleagues talked to one of the instructors and that person suggested that the use and performance of GUIDs is significantely improved in SQL2005 and that they have become the preferred datatype for primary keys if the database is used in replication.

I have tried to find some information about how GUIDs are improved in SQL2005 but I don't find any documents containing anything relevant. The only thing I've found is the documentation of the NEWSEQUENTIALID function which doesn't really tell me much. It just looks like this function improves the performance during an insert if a GUID is used on a clustered index. I have noticed for example that SharePoint only uses GUIDs for most tables; so the performance should be all that bad.

So I was wondering:

1. has the performance of GUIDs improved?

2. are there new suggestions concerning the datatype of the primary key column if the database might be used in a merge replication environment (all sites can do modifications) on SQL2005?

3. what about the performance of NEWSEQUENTIALID? Is there a bigger chance of running into duplicates using NEWSEQUENTIALID?

Regards,

Michael

NEWSEQUENTIALID is derived based on several hashes, one of which is the network card. Since no two network cards are identical, duplicates should not occur. However should you remove your network card, there's a slight chance you can get the same value as another machine whose network card has been removed, but this is highly unlikely.

Since NEWSEQUENTIALID values are incrementing as they're created, clustered index performance should improve as they'll be inserted ascending and in sort order. You also have less page splits, thus less fragmentation.

So, is NEWSEQUENTIALID a good candidate for a primary key? NEWSEQUENTIALID can only be used as a default constraint. that means if this is your primary key, and you have to update it, you can only use NEWID as the new value. Or, you can do delete/insert. If you do a lot of updates to your primary key, you'll be doing a lot of page splits introducing fragmentation.

|||How does the program get the NEWSEQUENTIALID value? In other words, the code has:
sqlCmd = "insert ..."
ExecuteNonQuery
If you have foreign key references, you'll need to retrieve your NEWSEQUENTIALID value and pass it as the foreign key value into the child table(s).|||Just use the OUTPUT clause of the INSERT statement.|||

For a parent child relationship, you can do this:

CREATE TABLE Employee(
EmployeeID uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
EmployeeName nchar(10) NOT NULL
)

insert Employee (EmployeeName)
output Inserted.EmployeeID
values ('Ima Person')

The OUTPUT clause essentially works like a SELECT statement. You can retrieve multiple values with an OUTPUT clause.

Alternatively, you may wish to use this syntax for CREATE TABLE:

CREATE TABLE Employee(
EmployeeID uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
EmployeeName nchar(10) NOT NULL
)

Newbie: sql 2005 encryption or where do I put that key?

I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?

I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.

What are your ideas on this matter?

TIA,

barkingdog

Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?

Thanks
Laurentiu

|||

Laurentiu,

From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?

My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.

Barkingdog

|||

Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.

For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.

For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.

For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.

In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.

If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.

Thanks
Laurentiu

|||

Hi,

I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.

This user is used by my UI(ASP.NET) for querying the DB.

All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.

Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:

select EncryptXXX(Key,Column) from table

To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:

Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...

Anybody who is running the profiler can now read the password as the profiler does not block the same.

What is the best way to overcome this?

Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.

Kindly correct me if I have misqouted anything.

|||

1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.

2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.

However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Thanks
Laurentiu

|||>>However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

So the programmers themselves MIGHT be hackers...

Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||

As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?

Thanks
Laurentiu

|||Hi,

When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"

Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).

Where and how exactly do you want me to store the password that protects the key?|||

I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.

Thanks
Laurentiu

|||>>it should just be passed to the OPEN SYMMETRIC KEY statement

Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:

1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.

I choose the second one therefore the problem.|||

Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).

Thanks
Laurentiu

|||Hi,

Before I implement your idea i would like to know more about the defenition of a current session.

I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?

One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||

Hi,

You can use CLR function to open symetric key , The script will be in assembly

and the password is hidden at all.

Thanks,

Tarek Ghazali

SQL Server MVP

web site : www.sqlmvp.com

|||

Hi,

I am totally new to this. Could you possibly giude me to some tutorials on the same?