Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smithygreg
Starting Member
37 Posts |
 Posted - 2008-09-16 : 19:28:30
|
Hi.I have a service running on my server that imports data using SSIS then runs a stored procedure on the data. The SSIS runs fine, but the query take forever to run and I inevitably get Timeout Errors. I have set the commandtimeout to 600 which I believe is 10 minutes.I also have this same issue when I write a .NET form application to run stored procedures...They take much longer than if I were to run the query in Management Studio.To give you an idea of the time they normally take, I had an import give me a timeout error this morning, but when I ran it in Mgmt Studio, it took 17 seconds.Has anyone else experienced errors like this? Could it be a network issue? Is there any tool I could install that would help me monitor exactly what is happenning causing the process to time out?Sample Code follows..SqlConnection conn = new SqlConnection();conn.ConnectionString = "blahblah"; SqlCommand cmd = new SqlCommand();cmd.CommandTimeout = 600;cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ImportID", importID);conn.Open();cmd.Connection = conn;try{ int nRows; cmd.CommandText = "ProcessData"; nRows = cmd.ExecuteNonQuery();}catch (Exception e){ LogMessage = e.Message;}finally{ conn.Close();}Is there anything I am doing there that would suggest that it would take a long time? I just remembered that the service that runs these SP's is on the server where the DB is, so Network issues shouldn't matter should they? The connection string does specify the actual server name..not ".".Well, thanks for looking and any help you can give me...-Greg |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-16 : 19:50:25
|
This should have been posted in the .net forum600 is a 10 minute timeout period.Your code seems ok to me, but what i use is slightly different. Try putting them in the order belowSqlConnection conn = new SqlConnection();conn.ConnectionString = "blahblah"; SqlCommand cmd = new SqlCommand();cmd.CommandTimeout = 600;cmd.CommandType = CommandType.StoredProcedure;conn.Open();cmd.Parameters.AddWithValue("@ImportID", importID);cmd.Connection = conn;try |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-16 : 19:51:38
|
| From my assumption of your code. There is a problem with the connection/executing the SP and since you have set your timeout to 10 minutes. It just holds on to resources.Question: Are you getting any error message, when you set the connection to the default ? |
|
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2008-09-17 : 11:26:21
|
| Hi..Sorry if I am in the wrong place here..I contemplated puttin git in the .NET section, but I assumed that was just for the CLR stuff.I am not sure what you mean with..."Are you getting any error message, when you set the connection to the default ?"What does setting the connection to the default mean?I will re-organize the code and see if that helps any...Thanks!Greg |
|
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2008-09-17 : 11:29:51
|
| As a follow up, the only error I am getting is a Timeout error...Thanks again!Greg |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-17 : 11:32:03
|
| organize it, and if it doesnt work. I can rewrite a connection (that works for me) for you if you want.1. Most important, what error message are you getting ?2. Does the SP execute at all ? and are you getting any feedback from it ? |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-17 : 11:32:58
|
quote:
Originally posted by smithygreg
As a follow up, the only error I am getting is a Timeout error...Thanks again!Greg
I guess, the connection is not working. Reorganize the script above, as i said |
|
|
|
|
|
|
|
|
Query runs slow when called from .NET application