By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,463 Members | 2,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,463 IT Pros & Developers. It's quick & easy.

SQL Server timeout error while executing stored procedure

P: n/a
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
Nov 16 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 16 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.