I'm posting this here so that I can find it in the future.
When accessing SQL Server via SqlClient (could be ADO.net or Entity Framework), any error is wrapped in an instance of SqlException and the Number property will be set to provide an indication of the specific error condition. In the event of a connection timeout, the Number property will be set to either 2 or 53. A command timeout will set the Number property to -2. And a deadlock will return 1205 in the Number property.
Thursday, July 23, 2015
Thursday, July 16, 2015
Splitting and combining strings with TSQL
My current work includes a batch process that validates 1000's (and sometimes more) transactions against a dozen+ validation rules. Recently this process became the source of blocking issues, causing a lot of users to become very unhappy.
Previously the rules were written in a very straight-forward manner. Usually something similar to:
UPDATE transactions
SET
reason = reason + 'A'
, status=6 -- exception
WHERE {some rule}
It's probably obvious where the blocking became a problem.
Our alternate approach was to collect the a list of invalid transactions and the rule that was violated into a table and mark all of the affected transactions with a single UPDATE.
CREATE TABLE #badRows (id BIGINT, rule char(1));
INSERT INTO #badRows (id, rule)
SELECT id, 'A'
FROM transactions
WHERE {some rule}
The next challenge was pivoting and rule-violation codes into a single string so that the reason field of the transaction could be updated, with the additional constraint that any existing rule-violation code had to be maintained. We came up with the following:
;WITH both AS (
-- combine existing violations with any new ones
SELECT t.id, t.reason
FROM transactions t
INNER JOIN #badRows br ON br.id = t.id
UNION ALL
SELECT id, rule FROM #badRows
)
, ix AS
(
-- split any strings into single characters
SELECT id, SUBSTRING(reason,Number,1) AS code
FROM both
INNER JOIN master.dbo.spt_values ON Number BETWEEN 1 AND LEN(reason) AND type='P'
)
, crushedReasonCodes AS (
-- combine multiple rows into a single string
SELECT DISTINCT id, (
SELECT DISTINCT code AS [text()]
FROM ix
WHERE ix.id = o.id
FOR XML PATH ('')) as reason
FROM ix AS o
)
UPDATE transactions
SET reason = crc.reason
, status = 6
FROM transactions t
INNER JOIN crushedReasonCodes crc ON crc.id = t.id
Previously the rules were written in a very straight-forward manner. Usually something similar to:
UPDATE transactions
SET
reason = reason + 'A'
, status=6 -- exception
WHERE {some rule}
It's probably obvious where the blocking became a problem.
Our alternate approach was to collect the a list of invalid transactions and the rule that was violated into a table and mark all of the affected transactions with a single UPDATE.
CREATE TABLE #badRows (id BIGINT, rule char(1));
INSERT INTO #badRows (id, rule)
SELECT id, 'A'
FROM transactions
WHERE {some rule}
The next challenge was pivoting and rule-violation codes into a single string so that the reason field of the transaction could be updated, with the additional constraint that any existing rule-violation code had to be maintained. We came up with the following:
;WITH both AS (
-- combine existing violations with any new ones
SELECT t.id, t.reason
FROM transactions t
INNER JOIN #badRows br ON br.id = t.id
UNION ALL
SELECT id, rule FROM #badRows
)
, ix AS
(
-- split any strings into single characters
SELECT id, SUBSTRING(reason,Number,1) AS code
FROM both
INNER JOIN master.dbo.spt_values ON Number BETWEEN 1 AND LEN(reason) AND type='P'
)
, crushedReasonCodes AS (
-- combine multiple rows into a single string
SELECT DISTINCT id, (
SELECT DISTINCT code AS [text()]
FROM ix
WHERE ix.id = o.id
FOR XML PATH ('')) as reason
FROM ix AS o
)
UPDATE transactions
SET reason = crc.reason
, status = 6
FROM transactions t
INNER JOIN crushedReasonCodes crc ON crc.id = t.id
Friday, July 3, 2015
Installing Windows 10 Build 10162 on a Dell Venue 8 Pro
Today I decided to repave my Dell Venue 8 Pro with the latest Windows 10 Preview build (10162). I've been running Windows 10 on the device for a very long time, starting with an upgrade from Windows 8.1. Unfortunately, along the way, updates began to fail. Such is the risk of a preview OS.
The installation turned out to be pretty easy.
As most projects go, the prep is the most important step. First, make sure that the device is fully charged. I swear the thing doesn't change when it's running. Maybe it's just my device. Maybe I'm imagining it.
While the device is charging, download the 32bit version of the ISO from the Windows Insider site and burn it to a USB drive. I used isotousb. Format the USB drive for FAT32 and make sure that you click the option to create a bootable drive. Oh! Don't forget to grab the activation keys.
You're also going to need some drivers. You can get them from the Dell Venue 8 Pro Product Support page. I ended up using:
There's also an update for the touch screen (Panel Touch Firmware for Dell Venue 8 Pro 5830) but the installer notified me that the touch driver was already up to date after I had installed the Intel Atom A3000 Series Driver. YMMV.
I created a folder on the USB drive and copied all the installers to it.
Then you're going to need a USB hub.Windows 10 didn't come with a driver for the touch screen so you're going to need a mouse. The hub will allow you to have both the USB drive and mouse connected at the same time. You might also need an adapter (like this one) to connect the hub to the micro USB on the Venue 8.
With the device turned off, connect the USB hub to it. Press the Volume Down button and power on the device. It'll seem like the boot stalls until you release the Volume Down button. Then the device will boot into the BIOS setup. Navigate to the BOOT tab. You should see both the internal drive and the USB drive showing as bootable drives. Change the first boot target to the USB drive then save the changes and let the device restart. If all went well, the Windows 10 installer should start.
I chose the Custom Install option and removed all the existing partitions.
After the installer completed, I installed the drivers in the same order that I listed them above.
One note: the only operating difference that I've noticed is that I have to hold down the power button for a couple of seconds to power on the device. Otherwise, everything else seems to be working as expected.
Subscribe to:
Posts (Atom)