?
Solved

SQL Connection Timeout

Posted on 2004-04-07
3
Medium Priority
?
6,284 Views
Last Modified: 2008-03-03
I am setting the timeout in a connection string to be 420 seconds but for some reason it still times out at 30, I've run the query in analyzer and it's all good, in the example below i set the timeout to 0 which should be infinite
Here's the code

            public DataAccess(string psUser, string psPassword, string psServer, string psDB)
            {
                  mstrConnection = "Network Library=DBMSSOCN;Data Source=" + psServer + ";Initial Catalog=" + psDB + ";User ID=" + psUser + ";Password=" + psPassword + ";Connection Timeout = 0";
                  
            }

            public DataTable ExportShorts()
            {
                  System.Diagnostics.Debug.WriteLine(mstrConnection);
                  
                  SqlConnection conn = new SqlConnection(mstrConnection);
                  System.Diagnostics.Debug.WriteLine(conn.ConnectionTimeout);
                  DataSet ds = new DataSet();
                  DataTable dt = new DataTable();
                  ds=SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "ShortPositionAges");
                  dt=ds.Tables[0];
                  return dt;
            }

Thanks
0
Comment
Question by:painlessprod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:painlessprod
ID: 10778893
Sorry I'm also using sql helper if that has anything to do with it, but I don't see why it should.
0
 
LVL 3

Accepted Solution

by:
gillit earned 500 total points
ID: 10779015
I have had trouble with this before, although I am not very familiar with SqlHelper. But the following is what I used.

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = new SqlConnection(strConnection);
myCommand.Connection.Open();
myCommand.CommandText = strSql;         // *******************1
myCommand.CommandType = CommandType.Text;  //***************2
myCommand.CommandTimeout = 120;

SqlDataAdapter objDataAdapter = new SqlDataAdapter(myCommand);

objDataAdapter.Fill(dt);


*****************

You probably would need to change 1 and 2 with the following:
myCommand.CommandText = "ShortPositionAges";
myCommand.CommandType = CommandType.StoredProcedure;


**************

Hope that helps.
0
 

Author Comment

by:painlessprod
ID: 10779246
Well that worked.

Upon doing further research I found that SQLHelper doesn't allow a command timeout, it has a connection timeout but not a command timeout.  So SQLHelper is useless for large stored procedures.  Otherwise SQLHelper is a very useful tool.

Thanks gillit
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question