Killing all sleeping processes

Hi All,
When I run sp_who2, I see there are many sleeping processes. Instead of
killing one by one, I was wondering if there is any way to kill all
sleeping processes programmatically (MS SQL Server 2000).
Thanks a million in advance.
Best regards,
mamunHi,
It is possible to do. But Killing the sleeping user process may not be a
good idea. A user processes
may be running for 40 minutes and when you check that process may be
sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
Script:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + 'Kill' + convert(varchar(5), spid)
from master.dbo.sysprocesses where status='Sleeping'
exec (@.x)
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
SQL Server MVP
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>|||If the unneeded idle connections are created by applications, then it's an
issue for the developer to resolve by managing the connection pool. If the
connections are created by people logging into Query Analyzer or Enterprise
Manager, then it's an issue for the DBA to resolve by perhaps restricting
logins and permissions.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>

keyword parameter

Is it possible to have a parameter that uses "LIKE" instead of other
operators? For example:
WHERE company LIKE @.company
instead of:
WHERE company = @.company
any ideas as to other strategies which would accomplish the same thing?
thanks!It should work just like you have listed... assuming the end user will put
the wildcards in the parameter... Otherwise you might do
where company like '%' + @.company + '%'
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:10929361-B7D7-4976-BA09-7A1B107B9391@.microsoft.com...
> Is it possible to have a parameter that uses "LIKE" instead of other
> operators? For example:
> WHERE company LIKE @.company
> instead of:
> WHERE company = @.company
> any ideas as to other strategies which would accomplish the same thing?
> thanks!|||It worked! THANK YOU SO MUCH!!!
"Wayne Snyder" wrote:
> It should work just like you have listed... assuming the end user will put
> the wildcards in the parameter... Otherwise you might do
> where company like '%' + @.company + '%'
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:10929361-B7D7-4976-BA09-7A1B107B9391@.microsoft.com...
> > Is it possible to have a parameter that uses "LIKE" instead of other
> > operators? For example:
> >
> > WHERE company LIKE @.company
> >
> > instead of:
> >
> > WHERE company = @.company
> >
> > any ideas as to other strategies which would accomplish the same thing?
> >
> > thanks!
>
>

Key export: exception

Recently as part of the process of upgrading to RS SP2 I followed the
normal procedure to export my crypto keys. Unfortunately instead of
providing me with the key file, it gave me an "External component has
thrown an exception" message on the console. At the same time the RS
service shut itself down.
As it turns out, I already have the crypto key tucked away on a CD. I
went ahead and updated to RS SP2 successfully, but the key export is
still giving me trouble.
The trace log isn't doesn't appear to contain any error information.
Has anyone run into this before? How did you resolve it?I've googled and looked through the knowledgebase, but still haven't
found a solution to this.|||We also had this problem. The solution for us was to amend the
web.config file to <customErrors mode="Off"> (we were using a custom
error handler). If you have custom errors "On" then turn them off and
seeing what happens. It didn't fix the problem but it did give a
proper error page showing what the actual problem was and on what line
of code, etc. Good luck.