.Net SQLCommand times out when executing Stored Procedure which returns no records.

Question

Hi guys,

I wonder if any one may be able to help?

I have an issue with a SqlCommand object that when it executes a stored procedure that returns no records, will timeout.

The stored procedure is not complicated, it is just a simple SELECT ColumnA, ColumnB, ... FROM TableA WHERE Id = @Id type of thing. If I run the SP in Sql Managment Studio it returns in a flash.

However when I try to excecute the command to fill a DataAdapter, or execute the command manually from the "Immediate Window" - once it has been created and parameters populated, it will always timeout.

I use the SqlCommandBuilder's DeriveParameters() method to populate the SqCommand parameters,and then iterate through the collection and populate the values. I then set the DataAdapter.SelectCommand to a reference of the SqlCommand and call the DataAdapter's fill method.

The code seems to work fine with any SP that returns data, but baulks when no rows are returned.

Has any one experienced this, and can point me in the correct direction, please?

Thanks in advance, Regards, Duane.

Answer 1

look for a command timeout property and set its value to zero.

    Dim cmd As SqlCommand

    cmd = New SqlCommand
    cmd.CommandText = spName
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Connection = _sqlConn
    cmd.CommandTimeout = 0

Answer 2

In the end it turned out that another part of the code had a transaction open on the record we were reading.

The reason for more than one connection to the database is because the project currently uses ADO and ADO.Net (It is a very large project and is currently being from converted from VB6 to .Net. There is still a lot of legacy data access using ADO). The ADO connection had the transaction held open, and the new ADO.Net connection could not read until the transaction ended, which would only happen AFTER the ADO.Net command timed out and the error was thrown and the ADO transaction rolled-back!

Doh!

Thanks for all who read and thought about the solution. Thanks to Beth for her suggestion.

Regards, Duane.