Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:
>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:

>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Subset of Data

I am fairly new to this - so please forgive me. I have a set of data that I
collected with one query but in the same stored procedure I would like to
turn around and use this data in another query to get additional info about
the agent. Please see below for example:
select paymmain.agentno,
sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor = 'Refund' or PaidFor
= 'Credit' or PaidFor = 'Reversed') then paidamount * -1
else paidamount end) as paidamount,
sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
Rev' or PaidFor = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
paidamount * -1 else paidamount end) else (case when
(PaidFor = 'NSF Rev' or PaidFor
= 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
(paidamount * -1) - servicefee else paidamount - servicefee
end) end) as sumamount,
sum(servicefee) as servicefeesum
from paymmain
join agendefa on paymmain.agentno = agendefa.agentno
where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
paymmain.agentno >= 0000
and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
group by paymmain.agentno
order by paymmain.agentno
This will return one record per agent. I have additional information that I
need about the agent - like bank info and status. So I would like to take
the above query and use it in another query to get the additional info
before returning from the stored procedure.
Also, I tired adding the info to this query but the status is a bit which it
gives an error that a bit can not be included ...
Any help would be appreciate - even if it is a keyword to do a search on.You can put this query into a view and then run another select query
that join the agent table to this view.
Another way is to put this query as a inner query in the FROM clause.
SQL Server support the following syntax.
Select * from Agent, (another select statement) as SomeTempTableName
where Agent.JoinField=SomeTempTableName.JoinField.
HHickey wrote:
> I am fairly new to this - so please forgive me. I have a set of data that I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor => 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
> Rev' or PaidFor => 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount - servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>|||You could SELECT this into a temporary table level variable, or a temp table
of some type then use it in another procedure or within the same procedure.
Rick Sawtell
MCT, MCSD, MCDBA
"HHickey" <hhickey@.pcgroup.holdings.com> wrote in message
news:er1v73%23qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> I am fairly new to this - so please forgive me. I have a set of data that
I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info
about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor
=> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor ='NSF
> Rev' or PaidFor => 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount -
servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that
I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which
it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>

Query Subset of Data

I am fairly new to this - so please forgive me. I have a set of data that I
collected with one query but in the same stored procedure I would like to
turn around and use this data in another query to get additional info about
the agent. Please see below for example:
select paymmain.agentno,
sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
'Refund' or PaidFor
= 'Credit' or PaidFor = 'Reversed') then paidamount * -1
else paidamount end) as paidamount,
sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
Rev' or PaidFor =
'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
paidamount * -1 else paidamount end) else (case when
(PaidFor = 'NSF Rev' or PaidFor
= 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
(paidamount * -1) - servicefee else paidamount - servicefee
end) end) as sumamount,
sum(servicefee) as servicefeesum
from paymmain
join agendefa on paymmain.agentno = agendefa.agentno
where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
paymmain.agentno >= 0000
and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
group by paymmain.agentno
order by paymmain.agentno
This will return one record per agent. I have additional information that I
need about the agent - like bank info and status. So I would like to take
the above query and use it in another query to get the additional info
before returning from the stored procedure.
Also, I tired adding the info to this query but the status is a bit which it
gives an error that a bit can not be included ...
Any help would be appreciate - even if it is a keyword to do a search on.
You can put this query into a view and then run another select query
that join the agent table to this view.
Another way is to put this query as a inner query in the FROM clause.
SQL Server support the following syntax.
Select * from Agent, (another select statement) as SomeTempTableName
where Agent.JoinField=SomeTempTableName.JoinField.
HHickey wrote:
> I am fairly new to this - so please forgive me. I have a set of data that I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount - servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>
|||You could SELECT this into a temporary table level variable, or a temp table
of some type then use it in another procedure or within the same procedure.
Rick Sawtell
MCT, MCSD, MCDBA
"HHickey" <hhickey@.pcgroup.holdings.com> wrote in message
news:er1v73%23qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> I am fairly new to this - so please forgive me. I have a set of data that
I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info
about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor
=
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor =
'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount -
servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that
I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which
it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>