SQL Exception because of a timeout

You think it would be easy to find information on exactly what error number a SqlException has when the command timed out. But, it isn’t. MSDN, for all that it is normally an excellent resource, fails to even mention that the Number property of a SqlException or SqlError can even be negative. (I suppose if I am to be fair, it doesn’t say it can be positive either). What it does say is exactly this:

This number corresponds to an entry in the master.dbo.sysmessages table.[^]

If you look at the sysmessages table, you will notice that all the numbers are positive. So, why am I concerned about negative numbers? Because sometimes the SqlException.Number property returns negative numbers, and therefore it does not correspond to an entry in the sysmessages table. So, the documentation is not telling the whole story.

I want to find out when an SqlException happened because of a timeout. The easiest, and I should imagine, the more reliable way of checking a SqlException for any particular error message is to check the Number property in case the message description has been localised or changed for some other reason. For most cases this is perfectly fine. I can check the sysmessages table for the description for the error that I am wanting to handle and I can check for that number when I catch a SqlException. But, there isn’t any error number for a timeout.

The exact error message that is in the SqlException is

Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, if the error is potentially because the server isn’t responding then the error message cannot be generated on SQL Server itself. How would it generate this error message if the server isn’t responding? The error, I can only assume, is being generated by ADO.NET. Why do I say I can only assume? Because after spending some time fruitlessly googling for a definitive answer I have yet to find one.

The best answer I’ve found is some sample code, that happens to check the Number property of the SqlException in a switch statement and next to case -2: is the comment /* Timeout */

So, at the moment I’m working on the assumption that -2 just means a timeout caused the execution of the SqlCommand to fail. I don’t like developing software in this way because if there is a bug in the future I cannot be certain that this code is okay or if sometimes -2 is something else, or perhaps sometime a timeout is something other than -2

NOTE: This was rescued from the Wayback Machine. The original date was Monday 17th October, 2005.

Tags:

Original Comments:

-2 is the error code for timeout, returned from DBNETLIB, the MDAC driver for SQL Server. So, its not ADO.NET.

A full list of possible error numbers can be found by using Reflector on System.Data.SqlClient.TdsParser.ProcessNetLibError.

10/18/2005 1:35 AM | Steve Campbell

Steve: That is most helpful. I feel much more confident that my code is doing the right thing now. Many thanks.

10/18/2005 6:48 AM | Colin Angus Mackay

What's new in Visual Source Safe 2005?

I’ve been looking at the new version of Visual Source Safe as the company I work for is desperately looking for something that isn’t VSS6.0 and isn’t going to cost as much as Team System. So far, initial investigations look very promising. There are other source code control providers out there that aren’t being investigated, such as SourceGear’s Vault, which may also provide a good (or even better) solution but due to budget constraints are being ruled out without being looked at. That isn’t to say that Vault is expensive, just that because we will receive VSS 2005 bundled with our MSDN subscription it would have to compete with an effectively zero cost alternative.

Anyway, this entry is all about Microsoft Visual SourceSafe 2005 so here is some initial stuff that I’ve looked at.

It is now possible to open VSS connections over the internet through a remote source control provider. This will be excellent for people who are on the move. We also have some clients who would like access to the source tree for their projects. Previously this was not possible unless a third party product was purchased, like SourceGear’s SourceOffSite.

There are limitations when using Visual SourceSafe using the remote provider. For example there is no access to viewing an individual file’s history. The reason given for this is that people who are accessing the source remotely are unlikely to need access to that sort of functionality. From a personal perspective, I have worked on distributed projects where the developers were spread out across many sites and that sort of feature is actually useful in those scenarios. Perhaps for a developer who’s just taken his laptop away from the office for a few days might get by without that functionality, but I can see there being many projects where that is something that will be a requirement. In those instances then solutions like SourceGear’s Vault may be more suitable.

Integration with Visual Studio is much better. The open solution dialog now has the option to browse the source code control tree as well as the file system, so there is no need to remember to click Open from Source Control the first time the project is opened.

Visual Studio retrieves the files asynchronously from Visual SourceSafe so that the developer can get to work faster as files can be checked out and edited before the whole source tree has been built on disk. The only caveat that I saw to this was that, naturally, you cannot build the solution until all the files have been retrieved.

Visual Studio also makes it easy for developers who operate on and off site by providing the facility to switch between multiple source code control providers. This was always possible, but it was a tedious task and prone to problems.

Visual SourceSafe now supports Windows Authentication which means that you don’t have to use the log in dialog as the connection from the client to the server will be authenticated depending on the account that you are logged in with.

Plugins are available so that third party diff engines can be used to allow the differences between various versions of a file to be seen where previously it wasn’t possible. This is especially useful for viewing the differences between certain proprietary or binary formats.

All in all, VSS 2005 looks like a good step forward however, there are a number of cheap or free alternatives that might suit some people better.

NOTE: This was rescued from the Google Cache. The original date was: Friday, 28th October, 2005.

Tags:

Original comments:

You forget to mention my most favorite VSS2005 addition….

File transfers are compressed giving a speed increase of 2x-5x during check in and check out. This can be especially important if the VSS server is remote on the network or internet where speed might be a prime. In the rare case of a dialup connection, this is a life saver.

And don’t quote be on this, but I believe file check in and checkout is now official transaction based. I know I’ve come across 2 files in my day that appear to have been corrupt in VSS.

Finally, keep in mind VSS6 came out in 1998 making it 7yrs old, which is ancient history in computer terms. Ok there’s been a couple patches/service packs but comon, VSS6’s age has been showing since VS.Net came out. I think we were all surprised when VS2003 didn’t include a new VSS.

11/3/2005 8:55 PM | Travis Owens

Additional: It should be pointed out that VSS is not transaction based. Team System is transaction based.

 

Sony DRM Hides Trojan

Further to my post last week about Sony’s malware disguised as DRM it seems that a trojan is now taking advantage of the Sony malware.

From The Register: “This means, that for systems infected by the Sony DRM rootkit technology, the dropped file is entirely invisible to the user. It will not be found in any process and file listing. Only rootkit scanners, such as the free utility RootkitRevealer, can unmask the culprit,” warns Ivan Macalintal, a senior threat analyst at security firm Trend Micro.

The full story can be found here: First Trojan using Sony DRM spotted

This was rescued from the Google Cache. The original date was Thursday 10th November, 2005.

Tags:

To be scammed, or not to be scammed

A little while ago I wrote about the poor security procedures that some banks had in place. The BBC have an article on today’s edition of their news website about tactics scammers use called “How to stay off the suckers list“. The common theme is that you have to be constantly vigilent about the situation or the scammers will get away with your money or belongings. However, how do you tell the difference. One reader summed it up succinctly:

The thing that always amazes me is when your bank rings up and asks you to answer some security questions. They could be anyone, and yet they always seem surprised when you ask them to prove who they are.
John James, London

And another wrote:

A further bit of advice when checking oseut credentials is not to ring the number on the ID card shown but to get the official number via the telephone book.
Peter Lockwood, Loughborough

I totally agree with both these sentiments. As I mentioned previously when my bank’s fraud department rang, I verified the phone number left in the voicemail message and when I couldn’t correlate it to any existing correspondance I had with my bank I phoned their customer service department. I spoke at length about the security implications of what they had done, but despite the assurances of the person I spoke to, I still have the nagging feeling that it wasn’t going to be taken any further.

NOTE: This was rescued from the Google Cache. The original was dated: Tuesday, 7th February 2006.

Tags:

Original comments:

We got cold-called today by some kind of business directory company. I didn’t talk to them, my colleague did. Towards the end of the conversation, as a ‘security question’ he got asked his place of birth. He refused to give it. The telesaleswoman said that she calls 400 people every day and he’s the first to refuse. He refused again and asked why she needed it. Allegedly it was to confirm to her supervisor, should he call, that she had indeed spoken to us.

In the end to get rid of her he simply lied.

My dad says that for his online bank account, he actually hasn’t answered any of the questions as stated. Instead he’s supplied other information which he can remember based on the information he was asked for. I’m not that smart – I couldn’t even remember the right answers to some of the questions (e.g. ‘memorable name’ – clearly not that memorable!)

2/7/2006 10:48 PM | Mike Dimmick

I completely agree with John James’ comments too about two way verification. If I get called by my bank, telco etc, I always request certain information from them to make sure they are who they say they are. It absolutely works both ways. I am also always surprised when they do not expect it. Recently, BT receoved a call from my partner to report a fault on our line. She is not the account holder, nor is she documented anywhere as living there (apart from council tax, data BT does not have access to) however BT were more than happy to disclose details about my account and even went so far as to divert calls to her mobile number (big security risk – what if I was having an affair or what if she wasn’t indeed my partner – easy trick to pull off!!!). Obviously, this is all going in my letter to them (they finally managed to fix my fault after 6 weeks).
Anyway, back to work for me.
Thanks Colin for the SQL injection attacks article on codeproject.com

2/10/2006 4:16 PM | Andrew Lewis

Types of join

Occasionally there is a post on a forum asking what a certain type of join is all about, so I thought it would probably be good to have a stock explanation to refer people to so that I don’t re-write near enough the same response each time the question arises.

First lets consider these two tables.

A

Key         Data
----------- ----------
1           a
2           b

B

Key         Data
----------- ----------
1           c
3           d

We can see that the only match is where Key is 1.

INNER JOIN

In an INNER JOIN that will be the only thing returned. If we use the query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
INNER JOIN B ON a.[Key] = b.[Key]

the returned set will be

aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1           a          1           c

In the case of the various outer joins non-matches will be returned also.

LEFT OUTER JOIN

In a LEFT OUTER JOIN everything on the left side will be returned. Any matches on the right side will be returned also, but if there is no match on the right side then nulls are returned instead.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
LEFT OUTER JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1           a          1           c
2           b          NULL        NULL

RIGHT OUTER JOIN

The RIGHT OUTER JOIN is very similar to the LEFT OUTER JOIN, except that, of course, the matching is reversed. Everything on the right side is returned, and only matches on the left side are returned. Any non-matches will be filled with nulls on the left side.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
RIGHT OUTER JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1           a          1           c
NULL        NULL       3           d

FULL OUTER JOIN

A FULL OUTER JOIN returns a set containing all rows from either side, matched if possible, but nulls put in place if not.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
FULL OUTER JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1           a          1           c
2           b          NULL        NULL
NULL        NULL       3           d

CROSS JOIN

The CROSS JOIN doesn’t obey the same set of rules as the other joins. This is because it doesn’t care about matching rows from either side, so there is no ON qualifier within the join clause. This is a simple join that joins all rows on the left side to all rows on the right side. Where the inner join and left/right outer join cannot return more rows than exist in the most populous of the source tables and the full outer join’s maximum result set if the sum of the source rows, the CROSS JOIN will return the product of rows from each side. If you have 5 rows in Table A, and 6 rows in Table B it will return a set containing 30 rows.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
CROSS JOIN B

returns

aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1           a          1           c
2           b          1           c
1           a          3           d
2           b          3           d

NOTE: This was rescued from the Google Cache: The original date was Monday, 27th February 2006.

Tags:

Confucius Say….

“Man who stand on hill with mouth open will wait long time for roast duck to drop in.”
— Confucius

If you want something you are going to have to put in some effort to get it, it will not arrive to you exactly as you want it. This is especially true in on-line forums. Many times I see questions from people that just want the answer to their homework. There is no intention to actually understand the problem, they just want something they can hand to their tutor the next morning. This is really frustrating because I spend some of my lunch hour or free time on these forums trying to help people. Most people are genuinely stuck and cannot make sense of the documentation (you will have noticed from other blog entries where I’ve written up a clarification of some documentation because it wasn’t written as I’d have liked) or they’ve been trying various things to get it to work and they’ve got their code in a “richt fankle”* and as a result they’ve lost the thread somewhat. Getting back to the analogy that confucius made, these people have actually attempted to obtain a duck, pluck it and roast it, but somewhere along the way it isn’t working out. These people deserve to get help because they have shown a willingness to learn by themselves.

What about the students that need to get their homework assignments in on time? Well, as it is obvious they’ve not done a jot of work themselves (unless you count copy and pasting their assignment to an online forum) their needs will most likely go unmet.

People get help because they deserve it, not because they need it. Does this sound unfair? Is it fair for me to waste my time helping someone who cannot even help themselves. To use another famous quote “Give a man a fish and you feed him for a day, teach a man to fish and you feed him for a life time“. If I just answer their question I use my time to just give them a fish even although I am trying to teach – I give them my fish that I caught to demonstrate during the lesson. If I can see that they are willing to learn then I know that if I teach them to fish, they will learn and they can then help themselves to as many as are in the sea.

A “richt fankle” is a Scots expression that means to get something in a complete mess. Code that is in a “richt fankle” would be most likely also be described as spaghetti code.

NOTE: This was resuced from the Google Cache. The original date was Wednesday 6th October, 2005.

Tags:

Original Comments:

I couldn’t have said it better Colin!

10/5/2005 4:00 AM | Rob Manderson

I continually experience co-worker brain death. When they realize that they can ask me for help, their brain stops working and they ask me the stupidest questions, that they could figure out themselves if they didn’t go into “I’ll ask Marc” mode. Sigh.

It is a balance though, to decide how much time to spend figuring out the answer oneself vs. asking someone for help.

Marc

10/5/2005 1:57 PM | Marc

A bad workman blames his tools

I wish that some people, when asking questions on a forum, would look inwards for a moment and reflect whether they really understand what they are talking about before making unfounded bold statments such as:

.NET is only one problem after another problem smiley_mad smiley_mad smiley_mad

One such poster on Code Project made a statement like that, then provided his code that was talking 2 minutes to run and that was unacceptable. He was blaming Microsoft and the .NET Framework but from one look at his code it was obvious where the problem was – and it wasn’t with Microsoft or the .NET Framework.

He wanted to update a column on a table. In fact he wanted to update that column on every row in the table. So, he pulled across 100,000 rows in to his .NET application then proceeds to loop over each returned row performing an UPDATE statement. So, in total he sent 100,001 commands to SQL Server. His complaint was magnified because he was expecting to have situations where 1,000,000 rows would need to be updated and that would take much longer. (20 minutes by the method he was employing)

Was the .NET application doing anything fancy as part of the update? No, it was simply copying the value from one column to another.

All his .NET code could be reduced to sending just one single piece of SQL to the database to do all the work. A simple UPDATE statement would do everything and take less than a second to execute – most likely even for a million rows.

But, did the poster seem to accept that perhaps it was his code or his misunderstanding of how the database could be leveraged that was at fault. No! He was insistant, with lots of angry faces, that it was Microsoft‘s fault for not getting the .NET framework right.

NOTE: This was rescued from the Google Cache. The original date was Wednesday, 5th April, 2006.

Tags:

Original comments:

Good Lord!
Did you reply to him with the obvious?
Maybe his next step will be to use a cursor within a single SQL statement and then blame the database!

ACK!

4/5/2006 9:23 PM | LJ

I told him how to get better performance, and I did point out that it wasn’t Microsoft‘s fault. It was his poor code.

4/5/2006 9:27 PM | Colin Angus Mackay

Don’t worry as his 386 workstation, with 4Mb of RAM and a dial-up modem will always ensure he is slow!

I’d have politely pointed out that his code “sucks”, what was wrong, what could be done to fix it then told him to get another career.

You should have linked to the CodeProject thread that shows this plonker in action!!!

4/5/2006 9:52 PM | John A Thomson

I find this a lot with rookies. Although, development these days requires you know a number of disciplines. SQL, ASP.NET, VB.NET/C#, HTML, T-SQL. Fast machines normally hide an inefficient programmer. That is why you need a jack-of-all-trades. Great blog.

4/19/2006 2:13 PM | Calvin

Oh yeah I know a few of them.

5/7/2006 3:37 PM | Derek Smyth

The Normal Forms of Database Normalization

I’m doing a clear out at the moment as I’m getting the house ready to sell – I’m planning to move to West Lothian – and I came across some old notes about database normalisation. It was a summary about the first 5 normal forms so I thought I’d share them.

First Normal Form: Every column contains just one value. That means that if you have a person’s name it should be split up to forename and surname, an address is split up so that the city and postcode are in separate columns, and so on.

Second Normal Form: The First Normal Form plus every table has a primary key. That means that everything can be uniquely referenced through the primary key.

Third Normal Form: The Second Normal Form plus every non-key column depends on the primary key. So for example, if you have an Orders table then columns such as DateOfOrder and DateOfDispatch are valid but listing the client’s details such as name, address and so on are not valid.

Fourth Normal Form: Remove repeating columns to a new table. Repeating columns are indicative of a missing relationship. So, if you have an Orders table it will not have item1, item2, and item3 columns. They will be removed to a separate table and form part of a join to the orders table.

Fifth Normal Form: Remove repetition within columns. Simply, this is enumerated values. For example, a company may accept Visa, Mastercard and American Express for payment. Rather than repeat those strings in the order payment table, they can be places in a small lookup table and the order payment table can refer to their smaller primary key.

However, I should point out that my notes are at odds with other references that I found. It would seem that the correct Fifth Normal Form is to do with ternary many-to-many relationships. In fact a quick search on the internet found that what ever I had written down as the Fifth Normal Form isn’t to be found. More research is in order to find out where my idea of the Fifth Normal Form comes from

NOTE: This was rescued from the Google Cache. The original date was Saturday, 22nd April 2006.

Help me to help you! Help me to help you! Help me to help you!

I’m a fan of the comedy-drama show Scrubs and Dr. Cox repeatedly tells young Dr. Dorian “Help me to help you! Help me to help you! Help me to help you!” And I think that is excellent advice for people who want help in forums. (And after my stressed out day yesterday you can probably see a rant coming).

Now, I guess I can understand why some people might want to hide what they are actually doing. They have code that is being written under an NDA or is somehow comercially sensitive. However, if they need to ask for help on forums then they have already implicitely admitted that it it isn’t that sensitive because they think someone else may already have solved that particular problem. Unfortunately, they haven’t admitted that to themselves yet and it makes it much harder for someone who really really wants to help to… well… help.

People wrap up their problems into abstractions and post that. Or they type up code that should have the same problem, but doesn’t. Or they do some other strange thing which hides what their actual goal is and scuppers many chances of getting the help they need.

For example. One poster said they wanted to manipulate some data from one table into another table. He showed some sample data in the first table. But the columns were called “az” and “azz”. What does that mean? The values in the columns didn’t mean anything as there wasn’t an explanation. Perhaps one was a primary key… perhaps it was something else… In the sample set of data some values were a dot. Was it really a dot or was that being used as a stand in for null. Only the original poster knows. When it came to putting the data in the new table there wasn’t an obvious relationship. How can anyone figure out what the rules are to manipulate from one to the other with such scant information?

So, in the words of Dr. Cox: Help me to help you!

NOTE: This was rescued from the Google Cache. The original date was Thursday, 4th May, 2006.

Tags: