Alert Not Being Triggered

Hi guys,

Hope you are all well.

I am trying to set up a 'SQL Server event alert' that will 'net send' a warning message to one of the Operators that have been set-up on our SQL Server.

The alert has been configured to act on severity level '009 - User Defined', for all databases.

I have created a user defined system message with a severity level '9' on the sysmessages table.

EXEC sp_addmessage 80000, 9, 'My User Defined Message'

...and I have raised this error on our SQL Server.

RAISERROR(80000, 9, 1) WITH LOG

The error message is being generated in the SQL Server Log file, but for some reason my Alert does not appear to fire off the 'net send' event.

The operator is 'available to receive notifications', and I have tested the 'net send' command successfully.

Does anyone know why this Alert might not be sending a message with the 'net send' command?

Thanks in advance,
KevinI found the 'problem'. There is a text box in the Alert properties labelled 'Error message contains this text'. I stupidly assumed that this would be the message displayed to the Operator, when in fact this actually restricts the alert to only those events that contain the text specified in the error message.

:o :D

Aggregate where claus problem =/

Hi Guys,

Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity

Heres the query so far:

SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID

Thx for reading :-)

--PhilkillsTry this:
SELECT itemid, max(qsum)
FROM
(
SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS qsum
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
)
group by itemid|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

ah thx man perfect ^^

Although, is it not inefficient to have 2 sums for the same thing? or will SQL realise that their the same thing and count them as such..

Also would be it possible to change the query to select all Total's that are greater than say... 10..

like:

SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
WHERE SUM(ORDER_ITEM.ItemQuantity) > 10
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

although this doesn't work as it doesn't like that in the where clause =/|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

thx mate, lol seems kinda pointless "having" an extra keyword just to perform those extra operations ;p (that is instead of where)