Just arrived in work and surprised by the reaction to this one! Apologies if the wording was confusing to some. I tried to make the question has clear as possible...

It's triggered 3 times in the past 24 hours on our production server :sick:

Eureka! So I finally solved the issue - just by trail and error.

When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error....

Eureka! So I finally solved the issue - just by trail and error.

When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error.

I...

I just tried that and also added the service account to the DatabaseMailUserProfile role in the msdb and that didnt make any difference either :crazy:

Hi Geoff,

Thanks for getting back. Yes that works without a problem. The issue is this statement:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'default',

@recipients = 'xxxxx@xxxxxxx',

@subject = @mysubject,

@body = 'Deadlock has occurred. View attachment...

I still cant get this script to work with an SQL SERVER AGENT job - I am trying to solve it over on this forum post but still no luck...

Hi Craig - thanks for the reply....

In youe extract is says : "When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user."

But in my script...

thanks for the replies....

The Job falls over when it hits the

EXEC msdb.dbo.sp_send_dbmail

profile_name = 'default',

@recipients = 'xxxxxxx@xxxxxxxxxx',

@subject = @mysubject,

@body = 'Deadlock has occurred. View attachment to see the deadlock...

Unfortunately not 🙁 I have changed the job owner to the the same account that runs the SQL AGENT which has sysadmin privileges. The same error still persists. The whole...

I have checked and the account that is running the SQL AGENT has sysadmin priviledges on the sql server instance and also the code run Ok if I run it...

Hi LutzM - Ok - I take on board the comment about nchar - I'm actually in 2008 now but will need to deploy this to 2005 so could use...

Its an nchar(11)...

When I run the sql contained in the job within the QA it works Ok and I get a mail. However when I try and execute the job itself I...

Thanks for the replies......

I'm actually getting a clustered index scan on the table. The date for the orders is actually not contained in the order_products table

but a parent orders table....