Alert on deadlock

I have created an SQL Agent Alert which should notify me whenever
a deadlock is occured on sql server. The following are the details:

Type : Performance Condition Alert
Object : SQLServerLocks
Counter : Number of deadlocks/sec
Instance: Database
Alert : If counter value rises above 0

I got deadlock situation a couple of times on server but i have never received any notification. It seems this settings doenot work. Pls. advise!

Thanks

Is the SQL Agent definitely running ?

If so, "Test" that that the SQL Agent can create a mail session. Right click SQL Server Agent, choose Properties, click the test button.

|||yes. Nothing wrong with sql agent. as such i get other alerts but not this one.|||Is the occurrence count still zero ?
Under the Response tab try reducing the "delay between responses". I recall reading somewhere that if at the time SQL samples the counters there may well not be a deadlock. i.e. the delay between responses is sufficiently high for the condition to disappear before SQL samples the data.

Alert on data deletion

We have an employee table that contains bank details and are experiencing
problems with account numbers being erased and lost. In order to track down
why this is happening (either due to our application code or SQL
replication) we'd like to be able to prevent certain columns from being
deleted if they already contain some data.

Is it possible to setup a check constraint to prevent our ee_acct_no columns
from being set to NULL or blank strings if it contains an account number
(i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
always know employees bank details until later, so we do need to put them on
our database without bank details initially.

Also, if possible, can someone suggest a stored procedure or trigger i could
create that would fire a user-defined error message that would email an
operator if a bank account number changed?

Many thanks

Dan Williams.On Thu, 10 Mar 2005 14:35:55 +0000 (UTC), Dan Williams wrote:

> We have an employee table that contains bank details and are experiencing
> problems with account numbers being erased and lost. In order to track down
> why this is happening (either due to our application code or SQL
> replication) we'd like to be able to prevent certain columns from being
> deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no columns
> from being set to NULL or blank strings if it contains an account number
> (i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
> always know employees bank details until later, so we do need to put them on
> our database without bank details initially.

If ee_acct_no starts out null and later becomes non-null, then a check
constraint can't do the trick. You need a trigger ... which answers the
next question

> Also, if possible, can someone suggest a stored procedure or trigger i could
> create that would fire a user-defined error message that would email an
> operator if a bank account number changed?

CREATE TRIGGER trig_ee_acct
ON ee_acct
FOR UPDATE
AS
IF UPDATE(ee_acct_no)
BEGIN
declare @.msg varchar(400)
RAISERROR ('The ee_acct_no column must never be changed', 16, 1)
ROLLBACK TRANSACTION
END
GO

As Books Online describes:

All ad hoc messages have a standard message ID of 14,000.

Therefore in enterprise manager, you can set an email alert on message ID
14,000, and operators will get an email.

> Many thanks
> Dan Williams.|||Ross Presser (rpresser@.imtek.com) writes:
> As Books Online describes:
> All ad hoc messages have a standard message ID of 14,000.
> Therefore in enterprise manager, you can set an email alert on message ID
> 14,000, and operators will get an email.

Is that SQL 7 Books Online? The number for ad hoc messages is 50000.

Dan could also use sp_addmessage to add a custom message, for instance
75321 for this error, and say:

RAISERROR(75321, 16, 1)

and the set up the alert on this code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan Williams (dtwilliams@.hotmail.com) writes:
> We have an employee table that contains bank details and are
> experiencing problems with account numbers being erased and lost. In
> order to track down why this is happening (either due to our application
> code or SQL replication) we'd like to be able to prevent certain columns
> from being deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no
> columns from being set to NULL or blank strings if it contains an
> account number (i.e a 9 digit number)? We have setup the column to allow
> NULL's as we don't always know employees bank details until later, so we
> do need to put them on our database without bank details initially.

That would have to be a trigger. Ross showed you the basics, but I like
to add some more details.

First IF UPDATE() a bit heavy-handed. IF UPDATE() only tells us that
the column mentioned in the SET clause, but not that the value was
actually changed.

So, you would have to compare inserted and deleted with each other
to compare these. However, joining inserted and deleted can have
costly performance effects. My standard routine is to save inserted
and deleted into table variables and then work with these.

Note that it's a good idea to keep IF UPDATE(), so that you don't
perform the check if the UPDATE is for a completely different column
only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 10 Mar 2005 21:46:25 +0000 (UTC), Erland Sommarskog wrote:

> Ross Presser (rpresser@.imtek.com) writes:
>> As Books Online describes:
>>
>> All ad hoc messages have a standard message ID of 14,000.
>>
>> Therefore in enterprise manager, you can set an email alert on message ID
>> 14,000, and operators will get an email.
> Is that SQL 7 Books Online? The number for ad hoc messages is 50000.
> Dan could also use sp_addmessage to add a custom message, for instance
> 75321 for this error, and say:
> RAISERROR(75321, 16, 1)
> and the set up the alert on this code.

SQL 2000 BOL. And it's self-contradictory!

msg_id

Is a user-defined error message stored in the sysmessages table. Error
numbers for user-defined error messages should be greater than 50,000. Ad
hoc messages raise an error of 50,000.

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style
used in C. The error message can have up to 400 characters. If the message
contains more than 400 characters, only the first 397 will be displayed and
an ellipsis will be added to indicate that the message has been cut. All ad
hoc messages have a standard message ID of 14,000.|||That's great. Thanks a lot.

I've managed to create a trigger that makes use of custom made error
messages that get emailed to me whenever an account number gets
changed. Here is my SQL code:-

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.eecode varchar(30)
SET @.eecode = (select ee_code FROM inserted)

IF UPDATE(ee_acct_no)
DECLARE @.oldAcctNo varchar(10)
DECLARE @.newAcctNo varchar(10)

SET @.oldAcctNo = (select ee_acct_no from deleted)
SET @.newAcctNo = (select ee_acct_no from inserted)

IF LEN(@.oldAcctNo) > 0 AND @.newAcctNo = ''
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) > 0 AND LEN(@.newAcctNo) < 8
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16,
1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) = 8 AND @.newAcctNo <> @.oldAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
END

I believe my logic could probably be made more efficient, but initially
this appears to be working. However, i am experiencing issues with my
custom alert not being triggered consistently when the account numbers
are being changed. Do the insert and delete trigger tables get purged
after the trigger fires?

After updating an employees account number, i do initially receive the
email alert (with details of the old and new numbers), but if i update
it immediately after, it doesn't seem to fire the alert. However, if i
wait a couple of minutes, it appears to work ok.

That's why i'm thinking the inserted and deleted tables still contain
the previously saved information. Is there anyway i can purge the
information?

Thanks again

Dan|||dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
> CREATE TRIGGER trig_ee_acct ON employee
> FOR UPDATE
> AS
> DECLARE @.eecode varchar(30)
> SET @.eecode = (select ee_code FROM inserted)
> IF UPDATE(ee_acct_no)
> DECLARE @.oldAcctNo varchar(10)
> DECLARE @.newAcctNo varchar(10)
> SET @.oldAcctNo = (select ee_acct_no from deleted)
> SET @.newAcctNo = (select ee_acct_no from inserted)

This is not a good trigger. A trigger fires once per statement, and
the deleted/inserted tables can contain many rows, and a good trigger
should handle this. So you will need to rewrite your trigger.

> That's why i'm thinking the inserted and deleted tables still contain
> the previously saved information. Is there anyway i can purge the
> information?

deleted/inserted are so-called virtual tables and are constructed from
the transaction log, and they cannot be accessed outside the scope of
the trigger.

By the way, if you need to make many accesses to the tables in your
trigger, it's a good idea to copy the interesting columns to table
variables and work with these instead. This can give quite some
performance improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9616EB37A93C5Yazorman@.127.0.0.1...
> dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
>> CREATE TRIGGER trig_ee_acct ON employee
>> FOR UPDATE
>> AS
>>
>> DECLARE @.eecode varchar(30)
>> SET @.eecode = (select ee_code FROM inserted)
>>
>> IF UPDATE(ee_acct_no)
>> DECLARE @.oldAcctNo varchar(10)
>> DECLARE @.newAcctNo varchar(10)
>>
>> SET @.oldAcctNo = (select ee_acct_no from deleted)
>> SET @.newAcctNo = (select ee_acct_no from inserted)
> This is not a good trigger. A trigger fires once per statement, and
> the deleted/inserted tables can contain many rows, and a good trigger
> should handle this. So you will need to rewrite your trigger.
>> That's why i'm thinking the inserted and deleted tables still contain
>> the previously saved information. Is there anyway i can purge the
>> information?
> deleted/inserted are so-called virtual tables and are constructed from
> the transaction log, and they cannot be accessed outside the scope of
> the trigger.
> By the way, if you need to make many accesses to the tables in your
> trigger, it's a good idea to copy the interesting columns to table
> variables and work with these instead. This can give quite some
> performance improvements.

OK, thanks for the advice. I've just discovered the wonders of triggers and
have previously been performing validation and integrity checks in my
application code, so i'm very much a beginner in writing SQL code.

Could you provide me with an example trigger that i can use or point me in
the direction of a good web site that i can learn from?

Many thanks

Dan|||Dan Williams (dtwilliams@.hotmail.com) writes:
> OK, thanks for the advice. I've just discovered the wonders of triggers
> and have previously been performing validation and integrity checks in
> my application code, so i'm very much a beginner in writing SQL code.
> Could you provide me with an example trigger that i can use or point me in
> the direction of a good web site that i can learn from?

Writing triggers is not fundamentaly different from writing stored
procedures, although a few things apply:

1) The "inserted" and "deleted" are visible in the trigger only, not
from stored procedures or dynamic SQL called from the trigger.
2) The tables are slow to access, so if the trigger has many references
to them, copying to a table variable is recommendable.
3) You are always in the context of the transaction defined by the statement
that fired the trigger. For this reason, one should engage in long-
running operations, as this can give contention problems.
4) Any error (save RAISERROR) terminates execution, aborts the batch and
rolls back the transaction.
5) Likewise, if the transaction count on exit differs from the trancount
when the trigger started execution, this also causes the entire batch
to be rolled back.

The trigger you posted could be rewritten to something like:

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.inserted TABLE (...)
DECLARE @.deleted TABLE (...)

INSERT @.inserted (...)
SELECT ... FROM inserted

INSERT @.deleted (...9
SELECT ... FROM deleted

IF UPDATE(ee_acct_no)
BEGIN
IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE len(d.oldAcctNo) > 0
AND nullif(i.newAcctNo, '') IS NULL
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
WHERE len(i.oldAcctNo) <> 8)
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE d.oldAcctNo <> i.newAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
ROLLBACK TRANSACTION
END
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Aggregates question

Let's say I have this data set

Country Quant
USA 100
UK 50
USA 25

How would I get a sum of just the USA quantites while keeping all entries in the details section?

Assuming this is a table, you would put the following conditional aggregation expression into the table footer:
=Sum( iif(Fields!Country.Value = "USA", Fields!Quant.Value, 0))

Only if country = USA, the quantity values will be aggregated. Otherwise 0 will be added.

-- Robert

|||You sir are a gentleman and a scholar.

That worked thanks!

Aggregate Problem

When I run the my code in Northwind, the ExtendedPrice is wrong because
UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice in
my statement because I couldn't figure out a better aggregate function to
use.
My goal is to simply get a GROUP by Orders.OrderDate and SUM the
ExtendedPrice expression for each date. I probably need some type of
subquery, but not sure how to go about this one.
CODE (used in northwind database):
SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
[Order Details].UnitPrice * [Order Details].Quantity)) AS ExtendedPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDateHi
It may be clearer is you give your expected results, but this may be a
starting point!
SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
FROM Orders O
JOIN [Order Details] D ON O.OrderID = D.OrderID
GROUP BY O.OrderDate
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
> When I run the my code in Northwind, the ExtendedPrice is wrong because
> UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice
> in my statement because I couldn't figure out a better aggregate function
> to use.
> My goal is to simply get a GROUP by Orders.OrderDate and SUM the
> ExtendedPrice expression for each date. I probably need some type of
> subquery, but not sure how to go about this one.
> CODE (used in northwind database):
> SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
> COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
> [Order Details].UnitPrice * [Order Details].Quantity)) AS
> ExtendedPrice
> FROM Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order Details].OrderID
> GROUP BY Orders.OrderDate
>|||that did it. thx.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uty7J2Y3FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi
> It may be clearer is you give your expected results, but this may be a
> starting point!
> SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
> FROM Orders O
> JOIN [Order Details] D ON O.OrderID = D.OrderID
> GROUP BY O.OrderDate
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
>

Agg op question

hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advancePerhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.|||Beautifully done~ thanks!

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...