Problem with Sql Server

From: JoshuaLuke (josh_at_mentura.com)
Date: 02/10/04 

Date: Tue, 10 Feb 2004 10:46:10 -0800

I have a sproc on sql server that I am calling from an asp.net app using the data application block v2 method ExecuteDataSet. The sproc returns data that is bound to a datagrid. The problem is that the command times out when being called through .net but only takes 1-3 sec when run from query analyzer.

I've used sql profiler to try to diagnose the problem, here are the results:

Command Agent CPU Reads Duration
------------------------------------------ ------ ----- ------ --------
RptInvRoyalty 126, '2/1/2004', '2/29/2004' SQL QA 562 2187 1266
RptInvRoyalty 126, '2/1/2004', '2/29/2004' .net 26578 231648 30530
------------------------------------------ ------ ----- ------ --------

Things I have tried (sa account was used for all tests):

- Executing the command as an sproc by passing the sproc name and the parameters to the executedataset method.

- Executing the command using CommandType.Text and passing the same string I use in query analyzer (for example: "dbo.MySp 5, '01/02/2004'").

- Executing the command by creating my own SqlCommand and other objects directly (as opposed to using SqlHelper).

- Executing the command using the oledb data provider.

- Increasing both the command timeout and connection timeout to 120 sec for all of the above.

- Running the command against a backup db. It works the first couple times but then starts timing out.

- Running the command from different web servers.

- Running both web server and db server on same machine.

- Creating a new sproc with the same tsql code.

- Using ExecuteReader and ExecuteNonQuery.

- Running it in both c# and vb.

- Stopping and starting the db server.

- Rebooting the db server.

Any help would be appreciated.

Thanks,
Josh

Relevant Pages

  • Re: Problems running remote batch copy from wshshell asp script
    ... differently than opening a command line and executing a program by ... just used the "NET USE" command to attach to the server i need in my ... >> from an internal development server a external server. ... >> trying to use the wshshell component to kick off a batch script that I ...
    (microsoft.public.scripting.wsh)
  • Re: Problems installing XP SP2!
    ... package source directory and make sure the that XPSPS2sms.exe file exists in ... in your SMS Program command line property. ... > Executing program as a script ... > OnOptionalExecutionRequests failed for program Automated upgrade from XP ...
    (microsoft.public.sms.admin)
  • Re: Windows update error 0x80242008
    ... 2.219: Hotfix started with following command line: /si ... Unexpected Error While Executing Line 1 ( ... Test.IE7InstallBlock.Section) of PreRequisite ...
    (microsoft.public.windowsupdate)
  • Problem using exec() from Java servlet
    ... Note that this error ONLY occurs when executing from a servlet. ... I am trying to run the help command on pcli.exe. ... That executable is on a different machine than the server, ...
    (comp.lang.java.help)
  • RE: Open Form Code
    ... I've generally found that if you issue a DoEvents command after opening the ... I set that form's VISIBLE property to FALSE. ... VISIBLE property to TRUE immediately prior to executing the code. ...
    (microsoft.public.access.formscoding)
Loading