I have a T-SQL stored procedure with the signature
CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...
When executed directly in Sql Server the procedure runs in under 5 seconds, returning a few result sets amounting to about 100 rows in total.
Calling this procedure using the ADO.NET SqlDataAdapter.Fill
method to populate a Dataset
causes a SqlTimeoutException
on the SqlCommand
after 3 minutes (the specified timeout interval).
Changing the stored procedure so that it no longer has an output parameter, and that the output value required is returned as the last result set, solves the problem, and the whole thing runs in under 5 seconds as expected.
But why?
I don't want to go through my code base and modify all instances of this type of behaviour without understanding if I have really solved the problem.
Another thing to note is this is only apparent on one particular server, which admittedly has a larger dataset than other similar databases we run. Surely not a Sql Server setting?
UPDATE
Stepping into the framework source the issue appears to be in metadata retrieval. The ConsumeMetaData
method of the SqlDataReader
object hangs indefinitely. However I ran tests on other databases and cannot reproduce, so it is a database specific issue when this procedure is called though ADO.NET...Great.
Update II
Have confirmed the issue still occurs if I change the code to use the OleDbDataAdapter
with the SQLOLEDB orSQLNCLI provider types. Definitely to do with the connection.