Solved

SQL Connection Timeout

Posted on 2004-04-07
3
6,252 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
  • 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 125 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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