Query Timeout

Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:

> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA

Query Timeout

Hi,
I want to increase query timeout value but I don't know how to do this.
Please
advise me where to change query timeout value. Is it possible to set this
for a
particular database?
Thanks,
Soura.
Hi Sounder,
I think we can increase the Timeout for a whole server(all DB's in that
Server)
Right click on the server name => Select properties => Choose the
Connections tab => Increase the query timeout value. (The value is in
seconds. Set it to 0 for unlimited time) => Click OK.
Regards,
Herbert
"SouRa" wrote:

> Hi,
> I want to increase query timeout value but I don't know how to do this.
> Please
> advise me where to change query timeout value. Is it possible to set this
> for a
> particular database?
> Thanks,
> Soura.
>
|||Hi Herbert,
Thanks for your reply. There is an option in replication job to change the
number of attempts to connect subscriber with some specified time interval.
We can modify the information. Similarly is there any option to change query
time out (in any replication jobs,subscription properties or any where else)?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think we can increase the Timeout for a whole server(all DB's in that
> Server)
> Right click on the server name => Select properties => Choose the
> Connections tab => Increase the query timeout value. (The value is in
> seconds. Set it to 0 for unlimited time) => Click OK.
>
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
I think u need to increase the timeout of merge agent....
Open the Replication monitor in that choose the Merge agent => Right-click
appropriate publication and select Agent Profiles... => Click the New Profile
button to create the new profile with the appropriate QueryTimeout value =>
Choose the newly created profile.
Hope this will help..
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. There is an option in replication job to change the
> number of attempts to connect subscriber with some specified time interval.
> We can modify the information. Similarly is there any option to change query
> time out (in any replication jobs,subscription properties or any where else)?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Hi Herbert,
Thanks for your reply. Actually I getting time out error in my application
frequently.
So I decided to increase the query time out value. I found a profile High
volume server to server profile (in merge agent profiles), any idea about
this?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think u need to increase the timeout of merge agent....
> Open the Replication monitor in that choose the Merge agent => Right-click
> appropriate publication and select Agent Profiles... => Click the New Profile
> button to create the new profile with the appropriate QueryTimeout value =>
> Choose the newly created profile.
> Hope this will help..
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
Since ur getting timeout in application, i think u have to increase the
query timeout of Server instead of replication agent.
by my previous post step u can set the query timeout of Server.
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||SouRa, rather than adjusting the query timeout setting, why dont you try to
figure out why the queries are taking so long?
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:14E41307-124F-40A1-A1ED-97E4E9DC7B81@.microsoft.com...[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Soura,
the querytimeout as you seem to be requesting - for all access to the
server - is really a client-side setting. Herbert is right that
replicationwise we can set this in the agent profile, but as you seem to be
requesting more generally, it'll be a property of the command object if you
are using ADO / ADO.NET. Usually this doesn't need changing but there are
legitimate cases for it. I also concur with Chris - you might want to start
by looking to see if you have blocking issues that can be remedied by using
NOLOCK or a reporting server or better indexes for optimization etc. Not
always appropriate but still worth verifying.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
We have modified the Merge agent profile to "High end server to server.."
After couple of days of this change, we came across a new error message "the
process is running and waiting for a response from one of the backend
connections", after this message, we noticed the normal error message "
...clean up".
Please provide any other areas we may need to review.
We "push" the subscription to the Subscriber. All our foreign keys are not
enabled for replication.
Thanks,
Soura
"Paul Ibison" wrote:

> Soura,
> the querytimeout as you seem to be requesting - for all access to the
> server - is really a client-side setting. Herbert is right that
> replicationwise we can set this in the agent profile, but as you seem to be
> requesting more generally, it'll be a property of the command object if you
> are using ADO / ADO.NET. Usually this doesn't need changing but there are
> legitimate cases for it. I also concur with Chris - you might want to start
> by looking to see if you have blocking issues that can be remedied by using
> NOLOCK or a reporting server or better indexes for optimization etc. Not
> always appropriate but still worth verifying.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||SouRa,
the first error message is not normally anything to be concerned about. I
often get this when initializing. In my case it is the application of the
indexes that causes this message. Have a loog at the processes and use bdcc
inputbuffer to see exactly what is happening during this time. Please could
you post up the complete text of the second error message.
Cheers,
Paul
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:800312A8-DE48-44DA-9B1C-705323B6EA6E@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have modified the Merge agent profile to "High end server to server.."
> After couple of days of this change, we came across a new error message
> "the
> process is running and waiting for a response from one of the backend
> connections", after this message, we noticed the normal error message "
> ..clean up".
> Please provide any other areas we may need to review.
> We "push" the subscription to the Subscriber. All our foreign keys are not
> enabled for replication.
> Thanks,
> Soura
> "Paul Ibison" wrote:

Query Time Outs

Hi All.

I have some rather large SQL Server 2000 databases (around 60GB).

I have set up jobs to re-index the tables and update statistics every
sunday. This worked will for a few months. Now after a day or two of
using it the connections to it keep timing out. If i start the jobs
manually, all is well for two days or so.

Surely there can be a better solution to this ?

TIA.
Ryan,.Are the timeouts happening on the tables or on the queries run against
the tables ? The tables shouldn't time out. The queries could well
time out. Try running your queries with the execution plan showing. It
may give you some indication of what is causing the delays.

Also, do you get the same results on each PC and the server ? Try
running the query on the server if possible to see if it is server
based or networking. Networking issues may be causing a bottleneck
which then slows you down. Worth looking at if possible.

The trick will be narrowing this down to where the problem actually
lies, not just where it shows up.

budgie@.doormat.za.org (Ryan Budge) wrote in message news:<8b601867.0311200322.281b643d@.posting.google.com>...
> Hi All.
> I have some rather large SQL Server 2000 databases (around 60GB).
> I have set up jobs to re-index the tables and update statistics every
> sunday. This worked will for a few months. Now after a day or two of
> using it the connections to it keep timing out. If i start the jobs
> manually, all is well for two days or so.
> Surely there can be a better solution to this ?
> TIA.
> Ryan,.|||Hi.

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0311200631.7f7fdf6a@.posting.google.com>...
> Are the timeouts happening on the tables or on the queries run against
> the tables ? The tables shouldn't time out. The queries could well
> time out. Try running your queries with the execution plan showing. It
> may give you some indication of what is causing the delays.

OK. Will do.

> Also, do you get the same results on each PC and the server ? Try
> running the query on the server if possible to see if it is server
> based or networking. Networking issues may be causing a bottleneck
> which then slows you down. Worth looking at if possible.

I have tested the application on the server and on client PCs. It
does not seem to make a difference. Timeouts and slow performance is
on both...

> The trick will be narrowing this down to where the problem actually
> lies, not just where it shows up.

Yep... Im sure. :-).

I remeber googling around or reading some of the books online and
reading about the sample percentage when updating statics and indexes.
Is it possible to re-index a table with a greater percentage to
enable the job to only run once a week ?

What kinda percentage is safe to use ?

Thanks for the suggestions.
Ryan.

Query time out while inserting data

Hi guys,
when inserting data in a table i am getting the following
error:
Error : -2147217871
Source : Microsoft OLE DB Provider for SQL Server
Descripion : Timeout expired
Help Context : 1000440
The data is added through a stored procedure which is
called under MTS from a dll which in turn is accessed
through a ASP page.
The table has arround 150,000 records. This is not a bluk
insert. Why am i getting this error, is this a locking
problem?
Please reply ASAP
Thanking you.Could be a locking problem. Use EM, Current Activity, Profiler, sp_who, sp_who2 and sp_lock to
troubleshoot.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Saleem Subhi" <saleem_subhi@.hotmail.com> wrote in message
news:39d201c37614$137a6e00$a301280a@.phx.gbl...
> Hi guys,
> when inserting data in a table i am getting the following
> error:
> Error : -2147217871
> Source : Microsoft OLE DB Provider for SQL Server
> Descripion : Timeout expired
> Help Context : 1000440
> The data is added through a stored procedure which is
> called under MTS from a dll which in turn is accessed
> through a ASP page.
> The table has arround 150,000 records. This is not a bluk
> insert. Why am i getting this error, is this a locking
> problem?
> Please reply ASAP
> Thanking you.
>

Query Time Out Expired

I am using a School Management software with MSSql Server 2005 software is working well on the server but it is not accessible on network. we using wareless network....Open [Start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server Surface Area Configuration], select [MSSQLSERVER> Database Engine> Remote Connections], make sure that "Local and remote connections" option is selected.

Query Time Out

Hi,

I have a report with query timeout set to 1 sec (I want this for producing timeout exception). When I view it from Business Intelligence Dev. studio's preview tab, it gives me "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" exception.

This is fine..but when i deploy it on report server it doesn't give this exception and opens the report without giving any exception.

in DatabaseQueryTimeout in rsreportserver.config is set to 900sec.

Can someone help me out if I am doing anything wrong or missing something?

And I'will be thankfull to you in advance.

-Thanks

Sounds like more a reporting services issue. Try that group.

query time out

What is best setting for query time out 600 or 0 , unlimited . and why?
On Sat, 12 Jan 2008 15:21:44 -0800, "OA" <omrana@.verizon.net> wrote:

>What is best setting for query time out 600 or 0 , unlimited . and why?
The best setting for query timeout is whatever is best for what you
are doing, because each circumstance is different.
I tend toward zero (unlimited) myself, but there are many applications
where that might be a mistake. Obviously an application that has
queries that take three minutes needs a timeout greater than that.
Likewise for an application that should respond in less than a second
it might make more sense to set a limit that is less than a delay that
causes the user to scream.
Roy Harvey
Beacon Falls, CT