Solved

SQL Connection Timeout

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
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
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now