Set TDS client_version on connection to SQL Server 2005 from asp.net 1.1

Question

I'm trying to debug a problem along similar lines to this old question: http://stackoverflow.com/questions/1568852/sqldatasource-timeout-ok-in-management-studio

Complex stored procedure always times out when run from ASP.NET 1.1 via .NET SQL Client, but runs no problem from SQL Management Studio. I looked at sys.dm_exec_sessions to see what the settings were for the respective connections. For some reason, the connection from the ASP.NET app is using client_version 4 (SQLServer 2000 sp1) while the management studio connection is (appropriately) using version 5 (SQLServer 2005).

I assumed I'd have to change the connection string to prompt ASP.NET to use the correct version, but I don't see how or where to do that. Google is oddly unhelpful... can I specify the client_version that the ASP.NET app should use somewhere?

Any help is much appreciated.

(PS... the arithabort settings were also initially different for the two connections, but setting them both to "on" doesn't seem to help, unfortunately.)

Answer 1

The problem is that the .NET SqlCommand object has a default 30 second command timeout built into it. SQL Server Management Studio has an unlimited timeout set for it. You can change the timeout value for the application by changing the SqlCommand.CommandTimeout value before executing the command:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(VS.71).aspx

How long does it take to execute the query from SSMS? A 30 second query is a long query for a web application to make, so you might consider tuning your code so that it doesn't take as long to run before changing the timeouts.

The client version is based on what is installed on the application machine. If you download and install the 2005 SQL Native Client it would update the client being used. The SQL Native Client is on the installer CD or can be downloaded from the 2005 Feature Pack separately for free:

http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en