How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,
Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,
|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas

"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
..[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:
> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or user
> > account - and then be able to kill the query programaticaly. We have a web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one table). I
> > would like a way a user can kill their query.
> >
> > Thanks,|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or
user
> > account - and then be able to kill the query programaticaly. We have a
web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one
table). I
> > would like a way a user can kill their query.
> >
> > Thanks,

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or use
r
> account - and then be able to kill the query programaticaly. We have a w
eb
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table).
I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done b
y
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]

How to Identify an Array

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.

CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)

I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.

I am now looking at a table that is similar in nature - but different.

CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]

The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.

I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.

I will appreciate your input.rm wrote:

Quote:

Originally Posted by

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.
>
CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)
>
I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.
>
I am now looking at a table that is similar in nature - but different.
>
CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]
>
The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.
>
I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.
>
I will appreciate your input.

A table is not an array. It is a relation. Unlike arrays, relations are
not addressable by an index structure but only by the values they
contain. A relation with N attributes is N-dimensional but that doesn't
make it an N-dimensional array.

I have only your column names to go on. Your HoursWorked structure is
surely very impractical, not least because of the difficulty of
aggregating data across multiple days. The second case is trickier to
interpret. At the very least it seems probable that it isn't normalized
appropriately because of what you have said about the dependencies.
Think Fifth Normal Form and satisfy yourself about the appropriateness
of the design. Design by newsgroup is really not much more than
guesswork.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to hide specific rows in TABLE?

My report result has 100 lines of records (details) and I only want to
display the first 10 rows and hide the rest of them. How can I achieve
that?
Thanks,
KeithYou can set the visibilty property of the row based on the count of some
distinict item in the details being less than ten.
for example the expression for the visibiltiy property might read:
iif( Count( Fields!UniqueDetailItem.Value, "GroupNameIfNeeded" ) > 10,
True, False )
Hope this helps.
"Keith" wrote:
> My report result has 100 lines of records (details) and I only want to
> display the first 10 rows and hide the rest of them. How can I achieve
> that?
> Thanks,
> Keith
>|||Thanks Rand!
I tried with this expression something like:
=iif( Count( Fields!zzcusteu.Value) > 10, True, False )
But it still doesn't work. It just counted the total # of rows, if >10,
then hide all the rows (I want to the first 10 rows to be visible).
Any idea?
Thanks,
Keith|||Hi:
You can use this expression:
=iif( RowNumber( Nothing) > 10, True, False )
Since Count() return the total number of records, but RowNumber() return
the current row number
Kent
"Keith" wrote:
> Thanks Rand!
> I tried with this expression something like:
> =iif( Count( Fields!zzcusteu.Value) > 10, True, False )
> But it still doesn't work. It just counted the total # of rows, if >10,
> then hide all the rows (I want to the first 10 rows to be visible).
> Any idea?
> Thanks,
> Keith
>