Alert Notification ...URGENT Help Required

I want to create an alert for the following condition -
if the number of rows in a table Exceeds 2M it should send me an email...
How can i do this...Please advice...

Thanks,
B

Create a trigger on the table for checking the number of rows. As soon as it crosses 2M, generate a mail using XP_SENDMAIL.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

You could set a job that runs a count(*) on the table and if arrive to a certain range, send the alert. Schedule it to run as often as needed.
If you absoluely need the number to be 2M, then I guess trigger is your only option. Though I would not reccomand it as it will fire with each insert. A job even if run often would be a better choice. Make sure you use NOLOCK hint in the SELECT COUNT(*) not to cause lock problems.
You can create the job from EM (management/sql server agent/jobs).

Bambola.

Well it will impact the performance of insert queries. So try this query for getting the number of rows...
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(Table_Name)
AND INDID < 2
I am not sure if I put the correct column names....

Also note this may not give you the current no. of rows. So if you want to be dead sure about that use sp_spaceused with updateusage set to true.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Gaurav
I tried this
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(emp_tag)
AND INDID < 2

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'emp_tag'

I tried using Bambola's suggestion but count(*) takes 5-10 mins to come back with the result...

Please advice
-B

emp_tag is the table name ...did I do something wrong.....

SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(emp_tag)
AND INDID < 2


I got this error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'emp_tag'

You are missing the single quotes.

SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID('emp_tag')
AND INDID < 2

Note that this will give an approximate number as it depends on sp_updatestats. If you can live with it it's a faster choice. I'd still would go with a job though and not with a trigger.

Bambola.

Well I tried it with the quotes

SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID('emp_tag')
AND INDID < 2

and it did not return any rows ....



So you are suggesting that I put the following in a job

Select count(*) from emp_tag WITH (NOLOCK)
if count(*) > 2M then
use xp_sendmail to email


run this as a job on a schedule and get the job done?

try to update statistics and see if it changes.

Bambola.

it gives me an error

UPDATE STATISTICS emp_tag

Server: Msg 2706, Level 11, State 6, Line 1
Table 'emp_tag' does not exist.


Should I create statistics....on the table

IF YES , Is this the syntax.....
CREATE STATISTICS emp_tag
ON DB..emp_tag
WITH FULLSCAN, NORECOMPUTE

I was thinking about the same thing, Luis. bzeebee - If you are using QA run use database_name to make sure you are using the correct database, or select db_name() to find out where you are <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Bambola.

Its definitely the right db
dbserver - ARGONE
dbname - DBONE



use DBONE
UPDATE STATISTICS emp_tag

Server: Msg 2706, Level 11, State 6, Line 2
Table 'emp_tag' does not exist.

So table does not exist on the database...
can you see it in sysobjects? maybe you did not spell the name correctly...

Bambola.

select * from sysobjects where name = 'emp_tag'

I get one row for this

Any chance we are talking about a view?
what does this return

select xtype from sysobjects where name = 'emp_tag'

Bambola.

xtype = 'U'
its definitely a table

works fine.... job ran successfully ...

queries run fine....

update stats does not work though......

i just need to get an email when the count of rows in the table exceeds 2million

What about the owner of the table? maybe it's not dbo....

Bambola.

update stats worked when I user user.table_name
thanks bambola ..I guess i overlooked that one..

luis I used this query you gave:-

select si.rows, so.name as TableName, si.id from sysobjects as so
join sysindexes as si
on so.id = si.id
where so.name = 'user.emp_tab' and si.indid = 0


It does not return any rows inspite of me having done the update stats

Try this

select si.rows, so.name as TableName, si.id
from sysobjects as so
join sysindexes as si
on so.id = si.id
where so.name = 'syslogd'
and si.indid < 2
and so.type = 'u'
Bambola.

Why do we need this join? Why can't we get the information from sysindexes only? Use this and post what is the result?
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID('user.emp_tag')
AND INDID < 2
I am assuming user is the owner of the object.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

gaurav,
the query returned the number of rows..

so now I need to use xp_sendmail to email me right?

thanks

Yes you do....

See if

http://www.exchangeadmin.com/Articles/Index.cfm?ArticleID=14309
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp
http://www.sqlservercentral.com/columnists/kdas/xp_sendmail.asp


is of any help...

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Thanks Gaurav, Bambola and Luis for your valuable time to help me solve this issue.....

-B