Alert a user from SQL Sever Trigger

Hi
We are trying to alert a user from a trigger in SQL Server.
We are currently using a Trigger to produce an email to the user, which is
not ideal due to the quantity of times the alert will be raised during the
day.
Any suggestions would be ideal.
Thanks
BDon't send email from a trigger (see
http://www.google.co.uk/groups?&sel... />
groups.com)
Instead, schedule the notification process at regular intervals to poll
the required table (based on an updated datetime column for example)
and send notifications as required.
Consider using Notification Services:
http://www.microsoft.com/sql/ns/default.asp
David Portas
SQL Server MVP
--|||Ben
Yes , it is not ideal.
It hurts performance ,so you are going to change the logic to alter your
users.
Have you looked at Alerts under Management folder in EM?
"Ben" <Ben@.NoSpam.com> wrote in message
news:uKzBiUPOFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi
> We are trying to alert a user from a trigger in SQL Server.
> We are currently using a Trigger to produce an email to the user, which is
> not ideal due to the quantity of times the alert will be raised during the
> day.
> Any suggestions would be ideal.
> Thanks
> B
>|||This kind of thing is exactly what notification services is for... It is
designed to scale huge, and it comes with SQL Server... Joe Webb has written
a nice coverage of notification services in a book published by Mann
Publishing.
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
"Ben" <Ben@.NoSpam.com> wrote in message
news:uKzBiUPOFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi
> We are trying to alert a user from a trigger in SQL Server.
> We are currently using a Trigger to produce an email to the user, which is
> not ideal due to the quantity of times the alert will be raised during the
> day.
> Any suggestions would be ideal.
> Thanks
> B
>|||Hi David & Uri
I agree that we should not send emails in Triggers.
As this is for a single user I believe that the Notification Services may
cost too much.
Is it possible to use windows messaging?
Thanks
B
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:efxBacPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Ben
> Yes , it is not ideal.
> It hurts performance ,so you are going to change the logic to alter your
> users.
> Have you looked at Alerts under Management folder in EM?
>
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:uKzBiUPOFHA.3076@.TK2MSFTNGP14.phx.gbl...
>|||Apologies everyone I did not realise Notification Services was part of SQL
Server.
I will look into it, it seems that it could solve three other problems that
I have too!
Many thanks for your posts
B
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O2TKQMROFHA.2728@.TK2MSFTNGP15.phx.gbl...
> This kind of thing is exactly what notification services is for... It is
> designed to scale huge, and it comes with SQL Server... Joe Webb has
> written a nice coverage of notification services in a book published by
> Mann Publishing.
>
> --
> 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
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:uKzBiUPOFHA.3076@.TK2MSFTNGP14.phx.gbl...
>|||There is no purchase cost for Notification Services itself but you do
need to be able to comply with the licensing requirements. (see "How to
Obtain" under the link posted previously)
If NS doesn't suit you then I recommend xp_smtp_sendmail. Very easy to
setup and configure. See the following for more info:
http://www.aspfaq.com/show.asp?id=2403
David Portas
SQL Server MVP
--

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.

Aggregate only top 20 records in a table- MSSQL2000

Hi All

I need to aggregate a query to produce the following:

Workplace Avg
M100 4.7
M120 3.45

Which would be a normal aggregate:
SELECT Workplace, Avg(VALUE)
FROM PROD
GROUP BY Workplace

However I need the average to only be based on the most recent 20
results from each of the Workplace groups.

I've never had to do something like this before so can't think of any
way to only take off the most recent 20 for each group (ordered by
Date). It doesn't really matter if there were 25 spread across 2 days
I would just cut the list at 20 VALUEs as there is no time component
invloved.

Is there any way to do a sub-query that uses select top 20 ... for
each group that could then be aggregated?

I would prefer to do it through a select statement rather than having
to use a stored procedure using and variables, etc which I can do. The
table is not huge but is growing rapidly so I'm concerned that
anything using dyamic SQL or similar would be become painfully as the
number of groups grows to 5,000 or more.

If anyone has any ideas they would be greatly appreciated.

Thanks in advance,
BevanOne option is to use a derived table construct like:

SELECT col1, AVG(col2)
FROM ( SELECT TOP 20 col1, col2
FROM tbl
ORDER BY col2 ) D
GROUP BY col1 ;

--
- Anith
( Please reply to newsgroups only )|||"Bevan Ward" <bevan_ward@.hotmail.com> wrote in message
news:b9cc76b2.0306270622.4ec978c5@.posting.google.c om...
> Hi All
> I need to aggregate a query to produce the following:
> Workplace Avg
> M100 4.7
> M120 3.45
> Which would be a normal aggregate:
> SELECT Workplace, Avg(VALUE)
> FROM PROD
> GROUP BY Workplace
> However I need the average to only be based on the most recent 20
> results from each of the Workplace groups.
> I've never had to do something like this before so can't think of any
> way to only take off the most recent 20 for each group (ordered by
> Date). It doesn't really matter if there were 25 spread across 2 days
> I would just cut the list at 20 VALUEs as there is no time component
> invloved.
> Is there any way to do a sub-query that uses select top 20 ... for
> each group that could then be aggregated?
> I would prefer to do it through a select statement rather than having
> to use a stored procedure using and variables, etc which I can do. The
> table is not huge but is growing rapidly so I'm concerned that
> anything using dyamic SQL or similar would be become painfully as the
> number of groups grows to 5,000 or more.
> If anyone has any ideas they would be greatly appreciated.
> Thanks in advance,
> Bevan

CREATE TABLE Prod
(
workplace VARCHAR(10) NOT NULL,
dt DATETIME NOT NULL,
value FLOAT NOT NULL,
PRIMARY KEY (workplace, dt)
)

SELECT workplace, AVG(value) AS avg_value
FROM Prod AS P
WHERE dt IN (SELECT TOP 20 dt
FROM Prod
WHERE workplace = P.workplace
ORDER BY dt DESC)
GROUP BY workplace

Regards,
jag

Aggregate Concatinate in join

I have a two tables:
table1
id
name
table2
id
date
I'd like to produce
table3:
id names date
where names = all names for the id concatinated seperated by ","jobs
You have two ID columns ,which one you want?
select tb1 .id,name,date from tb1 join tb2 on tb1.id=tb2.id
where name in ('a','b','c')
"jobs" <jobs@.webdos.com> wrote in message
news:1162310209.501090.68440@.e3g2000cwe.googlegroups.com...
>I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Is table2 supposed to have a name column where a row in table 1 corresponds a
row in table 2 where the id's are equal?
try this it will create the table and populate it with your data from the
other tables.
select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
into table3 from table1 as a inner join table2 as b on (a.id=b.id)
if the other tables are being updated or used by apps then you can use a
view... create view as remove the 'into table3'
Hope it helps,
Netmon
"jobs" wrote:
> I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Netmon wrote:
> Is table2 supposed to have a name column where a row in table 1 corresponds a
> row in table 2 where the id's are equal?
> try this it will create the table and populate it with your data from the
> other tables.
> select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
> into table3 from table1 as a inner join table2 as b on (a.id=b.id)
> if the other tables are being updated or used by apps then you can use a
> view... create view as remove the 'into table3'
> Hope it helps,
> Netmon
>
> "jobs" wrote:
>
> > I have a two tables:
> >
> > table1
> > id
> > name
> >
> > table2
> > id
> > date
> >
> > I'd like to produce
> >
> > table3:
> > id names date
> >
> > where names = all names for the id concatinated seperated by ","
> >
> >
In SQL Server 2005 you can do like this
select distinct a.id , stuff((select ','+name as [text()] from a as b
where a.id = b.id for xml path('')),1,1,'') as names,
b.date
from a inner join b on a.id = b.id
Regards
Amish Shah