How to change Query Timeout?

Greetings

I have encountered the following problem:

I currently develop an application for my company that actually uses rather long queries, with many records.

I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):

The first one is a test database that we use when in developing time quota to test our data

The second one is the real thing a data base that contains lots of records.

When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...

I tried to change the Query time out time from inside the SQL Server from

Tools/Options/Advanced

but it doesn't seem to work out... it still times out after 30 secs

Any Ideas?

Thanx in advance :DCould it be that this is not a SQL problem but an ADO-ADO.NET problem, i'm sure the default commandtimeout for command objects in ADO-ADO.NET is 30 seconds if not specified, specify 0 for unlimited timeouts and a value in seconds if you want different from the default.

I'd go for a 45 seconds property on the commandtimeout property...

regards,
J.

P.S. If i'm off base here I appologise, this is my first post, total newb...|||Thanks a lot for the advise Dreamweaver, but I need to specify that I tested the queries from inside the SQL Server enterprise Manager creating two new Views (One in each separate Data Base) and pasting in their SQL section the SQL string that I "Pinched off/Copied " from the debugger of the .NET just before it is fed to the adapter...

So I have side by side two new viewes with the exactly same SQL, but belonging to two different data bases...

So the problem arises through the SQL server...

Any more Ideas guys?...

Thanks!. :D|||I've just been looking at the settings on a server I am looking after. Surprisingly the timeout properties in Tools/Options/Advanced differ from right clicking the server and selecting properties and then looking at the bottom part of the connection tab, on this server it has;

0 in Tools/Options/Advanced

and

600 in Server Properties/Connection Tab

I'd have a look in right click Server/Properties/Connection Tab and see if that says 30,

regards,
J.|||first set sp_configure to display advanced options
execute reconfigure with override to force a "LIVE" change
then set remote query timeout to an appropriate value.
run reconfigure with override again.

USE master
EXEC sp_configure 'show advanced option', '1'
/*
Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.
*/

RECONFIGURE with override
EXEC sp_configure

--XXXX change to query timeout value (example '800')
sp_configure 'remote Query Timeout', 'XXXX'

:eek: I was so tired at the time i wrote this that i lost myself in coloring the code in the message posting areasql

How to Capture variables values in a Profiler trace

I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > I have the variables that I pass to the SP, but there are several
queries
> > within the SP that get other variables from the tables being used by it.
> >
> > Is there a way to create a SQL 2000 trace that will capture the
variables
> > being used within the stored procedure?
> >
> > Thanks.
> >
> >
>

How to capture the sql query

Hi
can someone explain how the profiler get the queries that are fired against the database. This information will be useful for building a customized trace tool.
ThanksRefer to Inside SQL Server book from Kalen Daleney and also you may get better information from MS Support about designing such a tool.

HTH|||look at the sp_trace_xxxxxx stored procedures