SQL Server timeout error while executing stored procedure

[Ville Huovinen]

Platform: Windows 2003 Server (MS SQL Server 2003 SP3)
Language: C#

Problem:

My stored procedures times out randomly, some proces works fine when using
them from C#, and some generate SqlException which states that the server
has timed out. The problem isn't in server, neither in stored procedures
because they work fine in Sql Server's enterprise manager, but when calling
from code they are slow and take about 60 seconds before executing.

Also the click here is that when first SP fails and you run the exact same
SP again, it runs smoothly and do not generate any kind of exception.

I also use ServicedComponents, and the class which handles the database is
inherited from ServicedComponent. In case if i raise the timeout to about 60
or 90 seconds, it goes through but throws COMException, which states that
transaction has failed.

i call the sp like

SqlConnection con = new SqlConnection(constr);
con.open();
SqlCommand cmd = new SqlCommand("StoredProcName", con);
cmd.CommandType = CommandType.StoredProcedure;

<Some parameter assigning>

reader = cmd.ExecuteReader(); <- Generates timeout

while(reader.Read())
{
ret.add
}

Also samekind of problem is when i try to insert some data with stored
procedure. There is about 50 stored procedures in the database, and some of
them work like these two which generates the timeout, but they work fine.

Thank you for respones

Ville Huovinen

 

[Mohamoss]

Hi Ville
You didn't specify how you set the name of the stored procedure to the
command object; try to give the full qualified name of the stored procedure
to the command text property. Try also to see the status of your
connections to the DB from the enterprise manager, process info.

Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC