0

I have a C# application that needs to connect to a SQL Server DB hosted by Amazon's RDS. Fairly often it throws the error

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

When it does this, it usually works on the next try. So I would like to add a programmatic "retry" to the queries in my app. I think that this (https://stackoverflow.com/a/4822976/1011724) looks like a good pattern to follow for this. However, that answer only retries for the following errors:

private enum RetryableSqlErrors
{
    Timeout = -2,
    NoLock = 1204,
    Deadlock = 1205,
    WordbreakerTimeout = 30053,
}

So is there a way to simply add this error? If so, what is the error code? Looking here (https://msdn.microsoft.com/en-us/library/cc645611.aspx), there is no error 40 and it also is not the exact wording of error -1.

Is my error a SqlException? If so what number? If not, how can I adapted the code from the answer linked above to account for this type of exception?

Community
  • 1
  • 1
Dan
  • 45,079
  • 17
  • 88
  • 157
  • It is probably not the SQL Server that returns the error, since `the server was not found or was not accessible`. It is not a `SqlException`. – Maarten Apr 19 '16 at 14:43
  • The error code is the one returned by the named pipes provider, which is only being used as a fallback because the server isn't responsive on TCP. The actual error number (`SqlException.Number`) is 53, which is a native Windows error code (`ERROR_BAD_NETPATH`). Rather than going for specific error codes in this case, you probably want to retry *any* `SqlException` that occurs as a result of calling `SqlConnection.Open` (as this one should be). This may mean you need to open SQL connections explicitly yourself, if this is currently done implicitly by your data layer. – Jeroen Mostert Apr 19 '16 at 14:49
  • @Maarten do you know how I can find out what kind of exception it is so that I can adapt that retry code to apply to this type of error? – Dan Apr 19 '16 at 14:49
  • @JeroenMostert so then maybe I could just catch and retry on any Exception? Would that be a terrible thing to do if I'm only going to retry a few times before giving up? – Dan Apr 19 '16 at 14:51
  • That *might* be a terrible thing to do, depending on your application. Imagine a DB call that's not fully transactional, and which might do something before it encounters (say) an arithmetic overflow halfway. If you blindly retried this, you could make things worse than if you'd simply bailed out the first time. For operations that only select data and are free of side effects, a limited retry should be no problem. Ditto for operations that are fully atomic (undo everything on rollback). – Jeroen Mostert Apr 19 '16 at 14:55
  • @JeroenMostert thanks - I'll look into it and make sure anything that changes data is in a transaction – Dan Apr 19 '16 at 14:58
  • @JeroenMostert do you know if `.ExecuteCommand(...)` is wrapped in a transaction automatically? http://stackoverflow.com/questions/36741454/does-linq2sql-automatically-put-executecommand-in-a-transaction?noredirect=1#comment61068919_36741454 – Dan Apr 20 '16 at 13:00
  • It's not wrapped in a transaction, but every individual statement is atomic. Multi-statement batches require a transaction to be performed as one, though. – Jeroen Mostert Apr 20 '16 at 13:22
  • @JeroenMostert thanks, so if that is say the least safe query I run, do you think it would still be potentially terrible to make the retry code trigger on any error? – Dan Apr 20 '16 at 13:25
  • The only potential trouble here is that, if the delete aborts because (for example) it takes too long because the database is overloaded, your retry will potentially put even more load on the database by repeating this heavy statement. Or the database may have changed state between your retries, and you actually don't want to do the delete any more. – Jeroen Mostert Apr 20 '16 at 13:30
  • @JeroenMostert ok cool, thanks! – Dan Apr 20 '16 at 13:32

0 Answers0