How you go about troubleshooting SQL query failure?

Question

I have a SQL query and it fails at times. That means that query runs fine most of the times. Once the query fails if I execute is again with some delay, it is successful in the same execution.

Please note there are no changes in the tables being used in the query in between the success and failure scenarios. They are exactly same. As mentioned below, the query joins couple of tables and the result of the query should be empty. But at times ADO throws and error.

How one goes about finding out the root cause of the problem?

I left out some of the details intentionally, since I am interested in knowing generic problem solving approach for SQL query failures.

---

Here are some details to know about any specific alternative approaches as well.

• Microsoft SQL Server 2008
• Query use joins two tables
• Query is passed from .NET through interop and a COM component deals with ADO to execute the query.
• Error info 0x80004005(-2147467259) [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

---

Update:

For this specific problem, I figured that the client runs out of available TCP ports. As per default settings 5000 ports are the max number of TCP ports. In my case, ADO creates implicit connections, which create TCP port every time a query is made.

netstat showed me that the number of ports open dramatically increased when I ran the workflow. At around 4000 ports the query fails (0-1023 are reserved ports). Answer from Jibcov Dobrev in here pointed me to the right direction.

Answer 1

Make use of SqlProfiler

Answer 2

Read the error message!

A query will only "fail" for a few reasons really:

• unique or check or other constraint violation
• datatype issue (mismatch, overflow)
• ???

Incorrect or contradictory aggregates or syntax will be picked up at parse time. But...

• with dynamic SQL then it could "fail" because it's syntactically wrong

It could be slow or give wrong data but this is not a "fail" as such.

Edit:

This is not a query error: it's a connection error unrelated to any SQL command.

Generally, you now need to decide if it's related to one client or is a general problem

Answer 3

A "generic problem solving approach" for SQL query failures is the same as any other code failure:

• Identify what it is doing.
• Identify what it should be doing.
• Identify why it is different.
• Fix it.

EDIT: Following more specific information being provided, the following microsoft knowledgebase article: http://support.microsoft.com/kb/328306 discusses the possible causes and ways of resolving this issue.

Answer 4

You can:

Run sql profiler to capture the query, make sure query is well formed. You can leave the trace running for as long as you like.

Run the captured query in management studio, sql server will throw out any errors it encounters.

if that still doesn't help maybe split the query into multi step stored procedure.. logging values/parameters etc states in a table/log file before each step.. so that you can identify some common factors whenever it fails.

Maybe even log the constructed query and outcome in a log file from .net, and compare difference in queries.

Depending on how you set up your data connection visual studio also allows facilty to step through stored procedures.

Answer 5

Specific to

0x80004005(-2147467259) [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

this seems to be a good starting place for investigation: Potential causes of the "SQL Server does not exist or access denied" error message