36 Chambers – The Legendary Journeys: Execution to the max!

April 22, 2014

Accessing The INSERTED And DELETED Pseudo-Tables In A Trigger’s CATCH Block

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

At work recently, we had a scenario come up in which we wanted to figure out which specific values are causing a trigger to roll back execution of a stored procedure.  I decided to put together a test case to see if I could still access the DELETED pseudo-table from within a trigger’s CATCH block.  It turns out that you can.

CREATE TABLE [Test]
(
	Id INT,
	Val CHAR(5)
);

INSERT into [dbo].[Test]
(
	Id,
	Val
)
VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc'), (4, 'ddddd');

CREATE TRIGGER tr_test on [Test]
after update
AS
BEGIN
	BEGIN TRY
		IF EXISTS(SELECT * FROM DELETED WHERE Id = 5)
		BEGIN
			THROW 50000, 'Ayup', 1;
		END
	END try
	BEGIN catch
		SELECT
			*
		FROM DELETED;
	END catch
END
GO

--This first run should NOT result in any errors.
UPDATE dbo.Test
SET
	Id = Id + 4;

--This second run SHOULD result in an error, showing us 4 records.
UPDATE dbo.Test
SET
	Id = Id + 4;

TRUNCATE TABLE Test;
DROP TABLE Test;

The trick here is that if you manually issue a ROLLBACK statement inside your CATCH block, you need to do it after collecting the information you need from the DELETED and INSERTED psuedo-tables, as those get cleared out upon rollback. If you insert those records into a variable (either a table variable or a regular variable using something like FOR XML PATH to concatenate the results), you can continue to use them in the CATCH block after you roll back your statement. That way, you can log the error without having that log roll back.

April 21, 2014

Harang And Santana

Filed under: Sports — Kevin Feasel @ 6:00 pm

Alex Remington discusses Aaron Harang and Ervin Santana’s resurgences with Atlanta.  For Harang, it’s all about getting rid of a changeup and curveball, focusing on his three best pitches.  For Santana, it’s about using the changeup more.  Both of them have been lucky so far, and clearly neither will carry a sub-1 ERA through the rest of the season.

Based solely on these descriptions and what I’ve seen so far this year, I’d say that Santana has a better chance of holding on.  Santana has a more recent history of success and better fundamentals, and adding the changeup makes him more dangerous.  For Harang, you can definitely ride smoke and mirrors for a while, but an extreme flyball pitcher in an Atlanta summer will give up home; combine that with his high walk rate and you have a good chance of regression.

Fortunately, the Braves should be able to ride Harang as long as they can, and once he starts tailing off, Mike Minor and Gavin Floyd will be able to slide into the rotation, meaning that Harang becomes a luxury rather than a necessity.

April 20, 2014

Best Easter Photos Ever

Filed under: Wacky Theories — Kevin Feasel @ 8:17 pm

Proof that the Easter Bunny is scary.

April 19, 2014

OOTP 15: First impressions

Filed under: Sports, Video Games — Tony Demchak @ 1:42 pm

I completed my first season of OOTP 15 today. Some general thoughts:

– The new UI is very, very slick. The manager screen is a lot more useful now, giving you a concise synopsis of what needs to be done and how to do it.

– The quality of the sim seemed pretty reasonable. (The Reds beat the Mariners, incidentally, in the World Series.) Jason Kipnis was the best Indian, at a WAR of nearly 5, followed by Carlos Santana and a bunch of dreck. Pitching staff was pretty terrible.

– The draft takes a LOT longer now (there’s a lot of number crunching behind the scenes), but that could just be my PC. I will say that the draft’s talent is a bit more evenly spread — if you have the cash, you can get some top notch prospects late in the draft now.

– I had a couple of CTDs when trying to read a Player Performance Report, but I think that was an unrelated problem (my dictionary on my PC kept insisting I was reading Russian, when I was clearly not doing so.)

– I didn’t try out the 3D system, but there are apparently some issues that they’re working out with that.

– The two laggiest parts of the game are shopping players around and, worst of all, the “Team Position Ranking” report. The latter took almost two minutes for no really good reason.

– Apart from the UI, I noticed a couple of other minor changes. First, you can set player ratings based on the average major leaguer, not purely based on position. Second, they added a posting system, I think, but I haven’t tested it yet — I think you’d have to enable the Japanese Baseball League (which you can totally do, along with about a half dozen other international leagues), which I didn’t do. I’m sure there’s a lot more, of course.

– For a real challenge (and to make the game more fun), make sure you change the TV revenue setting to “based on market size”. Otherwise, every one gets the same contract.

– There’s a very minor glitch: when the build was compiled, the designer forgot to have the game automatically poll the OSA rankings on day one; if you click to disable and reenable scouting, it will do that for you without much hassle. Monday’s patch is supposed to fix that and a few other bugs.

April 18, 2014

Out Of The Park 15

Filed under: Sports, Video Games — Kevin Feasel @ 6:00 pm

Out of the Park 15 has been released.  Oh, well, it wasn’t like I had enough stuff going on in my life…

April 16, 2014

Jon Bon Jovi And The Bills

Filed under: Sports — Kevin Feasel @ 6:00 pm

With the passing of Ralph Wilson, rumors about the Bills being sold are already popping up.  Jon Bon Jovi is apparently interested.

Moving the Bills to Toronto could have made more sense a few years ago, but considering that they’re not drawing much support in Toronto as-is, moving there probably wouldn’t help much.  It would be nice if the Bills kept their name and history but moved to Toronto, as that would give the team financial support that you just can’t expect in Orchard Park or Buffalo.  I believe that the likelihood of that happening would be pretty remote, however.

Gaming news, featuring Qvadriga

Filed under: Our Favorites, Video Games — Tony Demchak @ 10:24 am

A few days ago, I went on a mini-gaming binge. I picked up Hitman: Contracts for $6.99 (a friend bought me the “Hitman collection” as a gift a few months back, which included the 1st, 2nd, 4th, and 5th games, but not the 3rd. Apparently, a music licensing issue?), the Testament of Sherlock Holmes for $16.99, and Qvadriga for $19.99.

The Sherlock Holmes point-and-click adventure games are excellent. The first one, “The Mystery of the Mummy”, is absolute shit, but you can get all but Testament (the latest) for $19.99 or so on Steam. It features Sherlock Holmes matching wits against Arsene Lupin, a Cthulhu cult, and Jack the freakin’ Ripper, among others.

But I am not here to talk about how awesome Sherlock Holmes is. You already know that. I am here to talk to you about a lesser known game: Qvadriga. I first learned of this game via Rock, Paper, Shotgun, which does an admirable job covering PC gaming and, in particular, indie games. Here’s the article that convinced me to buy the game. A bit of backstory: AARs, or After Action Reports, are quite common in the military. It is a write up of what happened during a given action and why. But they’re also an extremely popular version of fan fiction on the Paradox Forums (I myself have written quite a few). The article linked above is one such AAR, and it’s brilliant, as is almost everything written by RPS.

But what, you ask, is Qvadriga? It is a turn based chariot racing game/chariot racing business simulator/horse exploder.

The premise is simple. You can either play a single race or the campaign. The campaign involves you traveling around the ancient world in an effort to get to the Circus Maximus. If you get there and win three races, you win the campaign. (If you choose the Epic Campaign, you also need to have the most wins in the world.) You have a team of charioteers, but only one of them really counts.

The individual races are brilliant. You can win a race in two ways: the traditional way or the destroying people way. You can break chariots, explode horses, kill other charioteers. Each race is made up of a few fixed segments (10 seconds in all), and you make one decision. To speed up, attack the guy next to you, brake to hit a curve gracefully, change lanes to avoid exploded horse carcass, or whip your horses to a point of nigh-explosion.

As long as you finish, you get some cash, which you use to hire new charioteers, improve chariots, replace exploded horses with horses who have yet to explode, what have you. You can skip races, too, to let people recover.

The game is addicting in the best possible way, because even in a short race (you’re looking at maybe ten turns in the average race, which means about a minute and a half per race), anything can happen. Chariots tip over with alarming frequency. Maybe the guy in the other lane doesn’t like you. Or maybe one of the random events that open a race works against (or for you). Maybe you aren’t allowed to whip people anymore. Maybe an opponent was bribed to slow down. Maybe you eyeballed the lane change wrong, and collided with exploded horse remains, thus causing your own horses to explode. The fact remains that you will not win most of the time. You will be lucky to get top three most of the time. In my present campaign, I’ve done about 30 races, have won 7. That seems a fairly typical ratio.

Should you buy it? If $20 is standing between you and food or shelter, no. Otherwise, yes. If I haven’t convinced you yet, the fact that horses explode in a glorious burst of blood when they die (and they die frequently), only to fall on the track in such a way that makes them appear to be sleeping, should be worth the price of admission. The price is a tiny bit steep, compared to most indie games, but worth every cent. Papers, Please and FTL are both better bargains (and FTL just got a massive — and FREE — expansion). Yet Qvadriga is also constantly getting better. There are talks of multiplayer and fleshing out the campaign mode. If you are unwilling to part with $20, the demo (which only lets you do a single race) is free. It’s also available on iPad and Android, and the controls are very conducive to such a move. I don’t know if the price point is different; $20 for an Android game is too much, of course. Still, highly recommended. May you have many hours of horses exploding, particularly the other guy’s horses.

April 15, 2014

Gaps And Islands

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

Not too long ago, I helped a co-worker solve a problem.  The quick version of the problem is, we want to collate together common occurrences of an event.  In this case, it’s log records by IP address.  If the same IP address shows up 3 times in a row, we want to know that there were 3 events, followed by the next IP, and so on.  If that first IP shows up later on, it’s a new chain.

This is another way of formulating the island problem.  Itzik Ben-Gan covers it wonderfully in his chapter on gaps and islands in SQL Server MVP Deep Dives, which chapter Manning has made available for free.

In my case, here’s a sample and the solution:

CREATE TABLE #Temp
(
	LogID INT,
	ErrorDate DATETIME,
	IPAddress VARCHAR(50)
);

INSERT into dbo.#Temp
(
	LogID,
	ErrorDate,
	IPAddress
)
VALUES
	(173371, '2014-04-07 09:47:35', 'IP 1'),
	(173372, '2014-04-07 09:47:36', 'IP 1'),
	(173373, '2014-04-07 09:47:37', 'IP 1'),
	(173374, '2014-04-07 10:01:35', 'IP 2'),
	(173375, '2014-04-07 10:02:35', 'IP 3'),
	(173376, '2014-04-07 10:03:04', 'IP 3'),
	(173377, '2014-04-07 10:03:05', 'IP 3'),
	(173378, '2014-04-07 10:03:18', 'IP 3'),
	(173379, '2014-04-07 10:18:26', 'IP 1'),
	(173380, '2014-04-07 10:18:26', 'IP 1'),
	(173381, '2014-04-07 10:18:26', 'IP 1'),
	(173382, '2014-04-07 10:18:27', 'IP 3');

WITH records AS
(
	SELECT
		LogID,
		ErrorDate,
		IPAddress,
		ROW_NUMBER() OVER (ORDER BY LogID) - ROW_NUMBER() OVER (ORDER BY IPAddress, LogID) AS grp
	FROM
		#Temp
)
SELECT
	MIN(LogID) AS MinimumID,
	MAX(LogID) AS MaximumID,
	COUNT(*) AS NumberOfRows,
	IPAddress
FROM
	records
GROUP BY
	IPAddress,
	grp
ORDER BY
	MinimumID;

The trick here is that we use two separate ROW_NUMBER calls to generate a difference in values.  The first part of the line (ROW_NUMBER() OVER (ORDER BY LogID)) generates a unique row number for each record, ordered by LogID.  The second ROW_NUMBER() operation generates a unique row number for each line, but it’s ordered by IP Address first and LogID second.  This means that the six records with an IP address of 1 will be clustered together and numbered 1-6.  We can take the difference between these two numbers to get a unique cluster, and that (which we call grp) lets us separate each IP address cluster out, solving the island problem with a single read of the data, much more efficiently than any row-by-row or self-joining solution could ever come up with.

April 14, 2014

Too Rich To Bribe

Filed under: Curmudgeonliness — Kevin Feasel @ 6:00 pm

Steve Sailer questions the idea of finding people too rich to bribe.  I tend to agree and sum it up with a classic Mr. Burns quotation:

Homer: Mr. Burns, you’re the richest man in the world. You own everything!

Mr. Burns: Ah yes, but I’d give it all up for just a little bit more.

The fundamental problem here isn’t finding incorruptible people; that’s practically impossible.  Instead, what they should aim to do is minimize the ability of the government to engage in acts of thievery and reduce its control.  This makes it less valuable to bribe government officials, which means fewer government officials will receive bribes.

April 13, 2014

New Presentation: SQL Injection

Filed under: (In)Security, Database Administration — Kevin Feasel @ 6:00 pm

I will be presenting for the PASS Security virtual chapter on Thursday, April 17, 2014 at 1 PM Eastern.  Attendees can go to the link to register for the webinar.  The talk is entitled “Understanding and Eliminating SQL Injection” and here is the abstract:

Over the past several years, hacktivists, criminals, and people just “out for lulz” have managed to find sensitive data owned by organizations like Sony, Yahoo, NASA, and the U.S. army, among many others. In all of these cases, the attackers exploited websites using SQL injection attacks.

SQL injection is at the top of the Open Web Application Security Project (OWASP) top 10 list and is an important part of one of the SANS 20 critical security controls. This talk will go into what SQL injection is, how attackers can use it, and how to secure your sites so that your CIO and CISO never show up on the evening news.

Although the talk will focus on using the Microsoft stack (IIS, ASP.Net, and SQL Server), the lessons will apply to all web systems everywhere.

Older Posts »

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 74 other followers