November 2011

You are currently browsing the monthly archive for November 2011.

I came across an interesting little enhancement which I’d like to share with you.  I’m sure you’re all familiar with the following error message:

Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’.

That’s right, the “hey, you’re inserting junk, stop that right now!” message.

However, earlier this week I encountered a variation of that error in our ETL logs:

Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’. The duplicate key value is (1, 2).

I’m sure I don’t need to explain how useful that extra sentence can be, right?  So, then I started digging because obviously I want that error to always include the offending values.

Recently our database back-end servers have been replaced with new machines running SQL Server 2008 R2 SP1.  And indeed, as of R2 SP1, this error message has gotten an upgrade!

If you want to check for yourself, the following code snippet can be used to generate the error:

create table #t (PK1 int, PK2 int);
create unique index PK_Unique on #t(PK1, PK2);
insert into #t values (1, 2),(1, 2);

Now, message 2601 is not the only one complaining about duplicate keys.  Another example can be generated using the following query:

create table #t (PK1 int unique, PK2 int unique);
insert into #t values (1, 2),(1, 2);

Executing that snippet on SQL Server 2008 R2 SP1 results in this error:

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint ‘UQ__#t________C5776555123EB7A3′. Cannot insert duplicate key in object ‘dbo.#t’. The duplicate key value is (2).

Do you notice the difference?  The message did indeed get an upgrade, but is not able to support a simultaneous violation on multiple columns, while msg 2601 can.  Hopefully they can get that fixed by SQL Server 2012 RTM!

Out of curiosity I decided to get a closer look at other possible variations on this error and dug into the sys.messages table using this query:

select * from sys.messages
where text like ('%duplicate key%');

Results of that query:

All error messages containing "duplicate key"

So, there’s one more to investigate: 1505.  To get that error generated is not that complicated.  We actually already have all the statements, just need to use them in the right order, like so:

create table #t (PK1 int, PK2 int);
insert into #t values (1, 2),(1, 2);
create unique index PK_Unique on #t(PK1, PK2);

And what do we get?

Msg 1505, Level 16, State 1, Line 1

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#t__________________________________________________________________________________________________________________00000000001D’ and the index name ‘PK_Unique’. The duplicate key value is (1, 2).

Nice, message 1505 also supports multiple columns, that makes two out of three!

Ow, don’t forget to clean up (yes, I even do that for temporary tables).

drop table #t;

Additional Info

Some further digging around on the internet brought me to the following suggestion on Microsoft Connect: Duplicate Key Values by Anton Plotnikov.  Looks like our enhanced statements are the result of that request.

There’s another suggestion as well: Tweak To Duplicate Key Message by Louis Davidson.  Louis suggests to have all offending values added to the message as well, but in a different situation than when dealing with multiple unique columns.  You can create one insert statement that inserts more than one record with offending values, and that’s the situation he’s referring to.  If you feel that’s important, get over to Connect and cast vote!  I do think that the number of values would need to get limited in that case, we might end up with really long error messages otherwise.

One last link, also by Louis Davidson.  He also wrote a blog post about the error message when he found out about it in Denali.

Have fun!

Valentino.

Share

Tags: ,

It sure has been a busy time: in the last couple of weeks I’ve had the pleasure of being able to attend not one but two different SQL Server conferences.

Nordic SQLRally 2011

The first event was the Nordic PASS SQLRally in Sweden last week.  And I have to admit, it was a special experience.  When we arrived at Arlanda Airport, we decided to go to the conference center using a taxi.  There was a lot of choice on drivers and we selected one that looked trustworthy.  Before entering the car, obviously we first asked him the price to Bålsta.  He said it would cost about 500-600 SEK, so we got in the car.  After about one kilometer he pretended to have misunderstood the name of the village (funny how one can misunderstand a written address) and suddenly the price went up to 895!  Long story short: the correct price was 650, but others got ripped off even more, up to 1600 SEK has been paid for a trip of 35-45 minutes!  So, small warning when travelling to Sweden: check in the airport what taxi companies can be trusted.

As we arrived on Monday and left the country on Thursday, we had plenty of time for networking outside of the session hours.  During that time we met some great people.  One of them was Mark Rasmussen.  This dude decided about half a year ago to reverse-engineer the MDF file, in his spare time.  And he succeeded!  But that’s not all, he wrote an assembly that allows you to query the tables in the MDF file without attaching that file on SQL Server!  He also delivered a session in the first slot on the second day.  Thing is, practically no one new this was going to be a level 500 session.  Yes, it was a tough wake-up, and a session worth attending!  If you’re interested in his project, which is open source, it’s called OrcaMDF.

Mark also convinced me to create a Twitter account.  As we all know, the SQL Server community is one of the greatest around, and apparently also on Twitter.  Who am I to ignore advice from such a smart guy, so I am now occasionally tweeting on @ValentinoV42.

One of the other things that I learned was that, in SSMS, you can drag the Columns node into your script to generate a list of fields, very useful when creating source queries for your ETLs!  Thanks Aaron, you’ll have to find another hidden feature now to fulfill the “I want you to learn at least one thing from this session’” rule. Smile

Drag Columns node to generate field list

Belgian SQL Server Days 2011

imageThe second event, our own SQL Server Days earlier this week, was also a bit special.  I’m part of the organizing committee and this year it was the first time that the conference took two days.  And that’s not all, but we had a record on international speakers as well, nine in total!

To keep the speakers busy, we treated them on a visit to the Duvel brewery on the first night.  And if that wasn’t enough, the Speaker’s Dinner on the second night was certainly worth repeating as well!

I’m happy to say that everything went as planned, everyone was happy and isn’t that how it should be?

My main task in the SQLUG is speaker communication.  Besides that, an additional task this year was to play taxi driver for a day.  To avoid speakers getting ripped off when travelling from their hotel to the conference center (what, you mean taxi drivers aren’t honest all the time?), we decided to take care of their transportation ourselves.  I have to say, I enjoyed it!  And it gave me an opportunity to give the car a good cleaning. Smile

To Conclude

Ordina Jobsite

Thank you Ordina for letting me spend my time at such great events, thereby allowing me to stay up-to-date and well-connected!  Want to become my colleague?  Check out our jobs through the link above!

Have fun!

Valentino.

Share

Tags: ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved