Check database samples for zero or null values

I have a program that writes a sample to a database (Table A) every minute.
I like to put together a script that would check the Table to see if last 5
minutes that were sampled were not 0 or NULL. If the samples were 0 or NULL
then send alert to the RSTK.com email account.
Table A
Sample_Time datetime
Sample_Value int
Please help me complete this task.
Thanks,are you wanted to get an email based on system counters? If so you can use
SQL Server Agent Alerts to achieve this. If you are not using system
counters then you can set up a dts package to acheive this, or stored
procedure etc, and run it through a job.
Simon Worth
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:94068EE3-CBD9-4908-BFD6-5BA16C182D3E@.microsoft.com...
> I have a program that writes a sample to a database (Table A) every
minute.
> I like to put together a script that would check the Table to see if last
5
> minutes that were sampled were not 0 or NULL. If the samples were 0 or
NULL
> then send alert to the RSTK.com email account.
> Table A
> Sample_Time datetime
> Sample_Value int
> Please help me complete this task.
> Thanks,
>
>
>
>
>
>|||In principle, like this:
IF
(SELECT *
FROM TableA
WHERE sample_time
BETWEEN DATEADD(MI,-5,CURRENT_TIMESTAMP)
AND CURRENT_TIMESTAMP
AND COALESCE(sample_value,0) = 0)
EXEC usp_sendmail
@.recipients = 'foo@.bar.com',
@.message = 'Blah, blah',
..
However, I recommend you send mail from an application process rather
than from SQL Server.
David Portas
SQL Server MVP
--|||Set up SQL Server for email integration with. Create a job with a t-sql
task.
if exists ( select 1 from tablea where sampletime > dateadd(mi,-5,getdate())
and (Sample_value is null or Sample_value = 0))
xp_sendmail yada yada (
details of xp_sendmail in SQL BOL..
Schedule the job every 5 minutes.
You might wish to use Gert Drapers sqlmail ( www.sqldev.net) instead of
using MS mail integration...
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
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:94068EE3-CBD9-4908-BFD6-5BA16C182D3E@.microsoft.com...
> I have a program that writes a sample to a database (Table A) every
> minute.
> I like to put together a script that would check the Table to see if last
> 5
> minutes that were sampled were not 0 or NULL. If the samples were 0 or
> NULL
> then send alert to the RSTK.com email account.
> Table A
> Sample_Time datetime
> Sample_Value int
> Please help me complete this task.
> Thanks,
>
>
>
>
>
>

CHARINDEX returns zero in TEXT column

I'm running into an issue where CHARINDEX on a text datatype column returns
0
if the expression I'm searching for exists at a position greater than 8000.
For example:
use pubs;
select charindex('New Moon Books',pr_info,8000)
from pub_info
returns zero, even though I know 'New Moon Books' exists past character
8000. Is this a known issue, and is there a workaround? It's causing my
search and replace procedure (using the UPDATETEXT function) to fail, i.e.
LIKE '%searchfor%'
is true but
CHARINDEX('searchfor',textColumn)
is zero.
Any help is appreciated.Did you try PATINDEX?
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||CHARINDEX will not work for strings larger than 8000. To work with TEXT
fields larger than this size, you will need to use the TEXT functions in SQL
Server 2000 like READTEXT, WRITETEXT etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||charindex won't work with text datatype. Use patindex|||Thanks Aaron, you of course are correct, PATINDEX works! (I was sure I had
tried that before, but I think I left out the wildcard character).
Anyway, much thanks (and thanks to Omnibuzz too!)
A.S.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try PATINDEX?
>
> "Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
> news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
>
>