Using xp_smtp_sendmail (see below) -- the message portion is just made up
of text strings, which have been gathered from the SP. So I'm thinking
maybe all I need to do is get the output of DBCC INPUTBUFFER into a text
string, but I don't know if that is right, or how to do it. Sorry, I'm a
newbie!



set @emailmessage = 'Process ' + @sql + ' has been killed ' + @kill_flag

exec master.dbo.xp_smtp_sendmail
@FROM = @emailSender,
@TO = @emailTo,
@subject = "Process Notification",
@message = @emailmessage,
@type = N'text/plain',
@server = @smtpServer




On Thu, 28 Jul 2005 15:35:39 -0600, "Chip Smith " <mssqldba-ezmlmshield-
x89558147.[Email address protected] wrote :

> How are you getting info into e-mail body, sofar?
>
>
> -- chip
>
>
>
>
> -----Original Message-----
> From: Andrea Miller
> [mailto:mssqldba-ezmlmshield-x6008033.[Email address protected]
> Sent: Thursday, July 28, 2005 3:24 PM
> To: LazyDBA Discussion
> Subject: RE: kill blocking processes - follow-up question
>
> With the help I got from an earlier question, I've created a stored
> procedure that will kill a process that has been blocking other processes
> for x amount of time. If a process is killed, the SP generates an email
> using xp_smtp_sendmail that states the process that was killed and when.
I
> would also like the email to include the output of dbcc inputbuffer(spid)
so
> I can keep track of what processes have a tendancy to block for a longer
> period of time.
>
> I haven't been able to figure out how to get the output of dbcc
inputbuffer
> into the email message. I'd appreciate anyone that might help me figure
> this out.
>
> Thanks in advance,
>
> Andrea
>
>
> On Mon, 25 Jul 2005 13:54:19 -0700, "Juan Vera " <mssqldba-ezmlmshield-
> x18735914.[Email address protected] wrote :
>
> > Andrea;
> >
> > This is what I wrote to handle the problem - you are free to use it.
> > Read
> through the variable declarations for an idea of what portions you can
> control.
> >
> > http://www.powerslacker.com/Juan/CodeSamples/jvBlockMonitor.sql
> >
> > jvBlockMonitor can be installed in master and run on a one-minute
> schedule, and has saved my butt several times. In the SQL agent schedule,
> you can control the values for how aggressively it behaves.
> >
> > The most important thing is that you can define a service account,
> because in most cases there are certain operations performed as batches
that
> you want to be much less aggressive about killing.
> >
> > The procedure relies on xp_smtp_sendmail for notifications, but this
> > part
> is not indispensable.
> >
> > Enjoy
> >
> > Juan Cristián
> >
> > -----Original Message-----
> > From: Andrea Miller [mailto:mssqldba-ezmlmshield-x94042273.[Email
> > address
> protected]
> > Sent: Monday, July 25, 2005 12:20 PM
> > To: LazyDBA Discussion
> > Subject: kill blocking processes
> >
> > Does anyone know of a way to monitor processes that are blocking other
> > processes and kill them if they block for a certain period of time?
> (This
> > is with SQL 2000.) For instance, I'd like to kill any process that
> blocks
> > out other processes for a time of ten minutes or so.
> >
> > Thanks in advance,
> >
> > Andrea
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website
> > : http://www.LazyDBA.com To unsubscribe:
> > http://www.lazydba.com/unsubscribe.html
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website
> > : http://www.LazyDBA.com To unsubscribe:
> > http://www.lazydba.com/unsubscribe.html
> >
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>

MS Sql Server LazyDBA home page