[Tech] Holy SQL Failure, Batman or... SQL Identity Value Change

I had literally one of the most stressful group of hours as an admin... I made an update to a production table that was required and two hours later it magically decides to start inserting duplicate numbers in the identity field (which happens to be the primary key as well, as that would naturally prevent duplicates since SQL generates the number and knows not to duplicate numbers, right?!)... Well, that was not the case today.

Today I got the dreaded "Violation of PRIMARY KEY constraint (contraint_name). Cannot insert duplicate key in object (table_name). The statement has been terminated" when trying to insert a new record into the table.

Of course, there was nothing that I changed in the table that should have created this problem... but here we were. So as I scoured the internet for solutions I noticed that there were absolutely no actual solutions readily available, so I stepped into Sleuth Mode and started pounding my head against my palms to figure out a solution to a problem that I hadn't identified yet.

Well, I knew that all of the other data that was being entered was not duplicate (because I had randomly entered it) and the only thing that could be causing a conflict would be the identity.

My first deduction was to identify what the value of the identity field should have been. A quick query against the table with a descending order of the id field solved that problem quickly: 119139.

Now I had to figure out what the current identity SQL was trying to use was. At first I hadn't any idea of how to do this off the top of my head (software dev first, database admin second), so I started digging. I found a very helpful command courtesy of MS: ident_current(). So there I was, sitting at the query window and I typed in my command... select ident_current('tablename') as currentid. Bingo... It said the current id being used was 119086. Wait... what? How could that even be? Why would it think that it was ok to roll back a number and begin trying to insert duplicates? I still don't know to this very moment why it did that. Remember, I hadn't changed anything about the primary key nor the identity. I had merely added a few more fields to the table.

So this is where the panic set it: This is a live solution. A database that the production software was using to store call logs, client data, anything and everything that made us money... and it was refusing to coincide to what it was supposed to be doing.

So then the Googling started again. I started simple: Why would SQL start duplicating identity fields. Guess how many valuable results that came up with. If you guessed ZERO then you are absolutely right.

After my first failure at finding a potential solution, I slapped myself mentally and asked myself why I hadn't just thought of how to change the seed. Well, I knew one way... and had tried it an hour ago. Guess what, it locks up the DB for a substantial amount of time and times out. Can't have that. So then I start looking for commands and I come across this nice fellow: dbcc checkident('tablename', reseed, newseedid). Simply replace the term tablename with your table's name (leave the single quotes in the command) and change newseedid to the new number you'd like the id seed to be and bingo, you have a new seed. Problem solved and crisis averted.

I hope this helps out some of you poor souls who may run into this issue in the future. It's far from an end-all-be-all solution, and it should be done only if you understand what it is you are doing (please look up the commands on MSKB before you implement them), and of course you are doing any of these things at your own risk... but it fixed my problem, and that is why I decided to share the experience.

Peace,
B

[Food] Penne Rustica

I've been making this pasta for about three years now. It is honestly my favorite. I actually use chicken & shimp for my meats in this and it does really well... Women beg me for this recipe. ;)



Gratinata Sauce
3 tablespoons butter
2 tablespoons minced garlic
3 tablespoons marsala wine
2 cups heavy cream
1 cup grated Parmesan cheese
1/2 cup milk
1/2 cup chicken broth
1 tablespoon cornstarch
1 tablespoon Grey Poupon Dijon mustard
2 teaspoons minced fresh rosemary
1/2 teaspoon salt
1/2 teaspoon minced fresh thyme
1/4 teaspoon ground cayenne pepper
1 pound penne rigate pasta, cooked
12 medium shrimp, peeled and deveined
2 skinless chicken breast fillets
1/2 cup (about 2 ounces) thick-sliced smoked prosciutto, chopped

Topping 3 tablespoons grated Parmesan cheese
1 1/2 teaspoons paprika

Garnish  12 sliced pimentos< 4 sprigs rosemary



Instructions

1. Preheat barbecue grill to high.

2. Prepare gratinata sauce by melting 3 tablespoons of butter in a medium saucepan over medium/low heat. Add garlic and sweat it for about 5 minutes. Be sure the garlic doesn't brown. Add the marsala wine and cook for another 5 minutes. Add the remaining ingredients for the sauce and whisk well until smooth. Bring mixture to a simmer and keep it there for 10 minutes or until it's thick. Cover sauce and remove it from the heat.

3. Cook pasta following directions on the package (7 to 9 minutes in boiling water). You want the pasta tender, but not mushy (al dente). Strain pasta and set it aside when it's done.

4. Pound the thick end of your chicken breasts a bit with a kitchen mallet to make them a uniform thickness. Rub chicken with olive oil, then sprinkle on a bit of salt and pepper. Spear the shrimp on skewers (6 per skewer should work fine), then rub them with oil, and sprinkle with salt and pepper.

5. Grill chicken for 5 to 6 minutes per side. Grill shrimp for 2 minutes per side. When chicken is done, slice each breast into strips.

6. Preheat oven to 500 degrees F. Build each dish in a large, shallow baking dish. Or you can use a 9-inch glass or ceramic pie plate. Load 3 cups of pasta into each baking dish. Add one-quarter of the chicken, 3 shrimp and 2 tablespoons of prosciutto onto each serving. Spoon 3/4 of a cup of gratinata sauce on each serving and toss to coat. Combine 3 tablespoons of grated Parmesan cheese with 1 1/2 teaspoons paprika, then sprinkle about 1 tablespoon of this mixture over the top of each serving. Bake the dishes for 10 to 12 minutes, or until tops begin to brown. Arrange three pimentos on the top of each serving, then jab a sprig of rosemary into the center and serve.
Makes 4 servings.


Thanks to the great Todd Wilbur who released this one years ago. I watched it the day it came out and I fell in love.