4

I looked though Transient Fault Handling Framework code trying to address temporary loss of connectivity to SQL Server. There's one key point there: SqlException is thrown both when there's an SQL-related problem (like syntax error) and something not related to SQL (like no connection).

Of course I need to try recover from the latter class problems only - if my code runs a malformed query I need to fail fast, not retry anything.

The framework tries to distinguish between those classes by examining SqlError.Number and comparing it against a huge set of hardcoded values. That's lot of knowledge and code based on this strategy will definitely need maintenance once SQL Server internals change.

I thought maybe I can use SqlException.LineNumber instead? According to MSDN, line numbering starts at 1 and line number 0 means the line number is not applicable so I guess that means the problem is not SQL related. I tried this for a while - whenever I have connectivity problems LineNumber is always zero.

Is using SqlException.LineNumber a good reliable way for identifying whether an exception is due to an SQL query problem or due to connectivity problem?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • 1
    From the help it seems only to be populated in queries and stored procs. I dont know if optimization would cause LineNumber to be 0 on a query. If you can assert that, I guess it is reliable. – leppie Aug 24 '11 at 14:09
  • Those hardcoded messages are as defined in sys.messages system catalog view, those will most likely NOT change over the years as Microsoft takes compatibility very seriously – Thiago Dantas Aug 30 '11 at 18:47
  • @Thiago Dantas: Yes, but some new codes might be added and my code would have to recognize them. – sharptooth Aug 31 '11 at 09:08
  • The same as you would have to change code if the linenumber behavior changes. If it is already working I wouldn't bother changing it. – Thiago Dantas Aug 31 '11 at 20:35
  • One example where it wouldn't work. `LINENO 0; SELECT 1/0` – Martin Smith Oct 09 '11 at 20:15

1 Answers1

0

I don't believe you can reliably use the LineNumber property as indicator that an exception is a connection-related error. The LineNumber will be set when an error occurs during a Stored Procedure, Trigger, or Function. But, you could still have errors in queries outside of those items or even in views that could cause an SqlException with a LineNumber of 0. The best approach is to use the technique you described using the Number property compared to the set of hardcoded values.

Parmenion
  • 299
  • 1
  • 3