Timeout period elapsed

[ChrisPalmerNZ]

T-SQL Flex is a great tool, I really appreciate your effort and generosity in making it. However I find it is only useful to me for small queries as I always get a timeout error on any more complicated long-running queries (over 30 seconds).

I'm not sure if this is a problem with T-SQL Flex or a lack in my understanding of how to use it, but even if I set query timeout on my server connection to 0 (the default) and fool around with connection timeout properties, it doesn't affect this, so I don't know where to go to fix it.

This is the message I get:
--There were 1 exception(s) encountered while running the query.
--Error: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.".

Please let me know how I can fix this...

[nycdotnet]

Hi,

Thanks for the kind feedback. I didn't realize that it was using the default timeout.

The connection string comes from the selected node in SSMS, but it's possible that I could modify it once I get it. Take a look here:

https://github.com/nycdotnet/TSqlFlex/blob/master/Code/TSqlFlex/RunCommand.cs#L37

Do you want to take a crack at it?

[ChrisPalmerNZ]

Hmm, not sure what I should be doing to "have a crack at it". Is there a way to manipulate the connection string that is being used?

[nycdotnet]

There is not currently a way provided to allow a user of the program to change it, but this could be added as a feature.

In the link I provided, the code gets the connection string within a ConnectionStringBuilder class which can be changed to add a longer timeout.

Are you interested to see if you can fix it? Otherwise, I will get to this when I can. I am hopeful it would be as simple as adding one line to set the query timeout value to some high value or perhaps 0 or -1 which is infinite.

[ChrisPalmerNZ]

Hi Steve

Sure, I am interested, though I am about to go overseas for a week so won't
be able to look at it until my return.

But just some exploratory examination shows me that we have w which is your
FlexMainWindow object, and it has a property lblConnectionInfo.Text to keep
your connection string, which starts with defaults for Instance (for
DataSource) and DB (for InitialCatalog) only.

I also see that your SetConnectionText method for setting connection string
uses currentConnectionText method which also only returns values for
Instance and DB.

So would I have to add another property for the ConnectTimeout information
to lblConnectionInfo.Text and set that in the currentConnectionText method?

I don't think its a good idea to set to arbitrarily to 0, so it would be
necessary to read from the current connection for its default (which may be
zero but at least is able to be set). I am curious why
the SqlConnectionStringBuilder class doesn't automatically set this based
on the connection used...

Does this seem correct?

To make changes should I fork your code, change it, then let you know when
its ready to re-integrate?

Kind Regards
Chris

On Thu, Jan 21, 2016 at 6:22 AM, Steve Ognibene notifications@github.com
wrote:

There is not currently a way provided to allow a user of the program to
change it, but this could be added as a feature.

In the link I provided, the code gets the connection string within a
ConnectionStringBuilder class which can be changed to add a longer timeout.

Are you interested to see if you can fix it? Otherwise, I will get to this
when I can. I am hopeful it would be as simple as adding one line to set
the query timeout value to some high value or perhaps 0 or -1 which is
infinite.

—
Reply to this email directly or view it on GitHub
#44 (comment).

[nycdotnet]

To contribute - yes. Fork the project, make your changes, submit a PR. See my guide "From Zero to Pull Request": https://github.com/nycdotnet/FromZeroToPullRequestWindows

On line 37 of RunCommand.cs, I'm calling SetConnection on the T-SQL Flex window object. The argument is a ConnectionStringBuilder object. I think that object type has all of the properties that can be set on a connection string, so the fix might be as simple as this:

var csb = new SqlConnectionStringBuilder(ci.ConnectionString);
csb.CommandTimeout = 600;
w.SetConnection(csb);

Instead of hard-coding the timeout, we could also support storing it in the registry. TSQLFlex.Core.Logging has some examples of reading out of the registry to get the LogFileName.

If you want to try to write the patch yourself, please feel free. If not, no big deal - just let me know.

Thanks for contributing!

[nycdotnet]

Nope - I'm wrong. Sorry the command timeout is set on the SqlCommand object. The good news is that it's the same basic concept. Look at line 37 in FlexResultSet.cs - should be fixable in there just by adjusting the timeout.

[ChrisPalmerNZ]

Thanks Steve

Should you include FlexResultSet.cs into the Code / TSQLFlex branch - I
only see it in your QLFlex.Core branch.

Chris

On Thu, Jan 21, 2016 at 8:13 AM, Steve Ognibene notifications@github.com
wrote:

Nope - I'm wrong. Sorry the command timeout is set on the SqlCommand
object. The good news is that it's the same basic concept. Look at line 37
in FlexResultSet.cs - should be fixable in there just by adjusting the
timeout.

—
Reply to this email directly or view it on GitHub
#44 (comment).