ERROR: “Cannot insert the value NULL into column 'permission path', table '@temp'; column does not allow nulls. INSERT fails.”

 

I was scripting a senddb code

and got this error:

“Cannot insert the value NULL into column 'permission path', table '@temp'; column does not allow nulls. INSERT fails.”

 

Descriptive huh!?

This has to do with a profile name not being entered.

 

 

View what the name of the profile is.  mine is “testForMailJob”

 

and I updated my code to include the profile name:

Alert on Job Failure (email alert)

I have had such luck getting a single failure alert to happen.  Here’s what I did.
I presume you have an email account set up in SQL Server.

Right-click SQL Server Agent –> Properties

Select Alert System.
Turn on Enable mail Profile and click OK. 
Then repeat by turning it off! Then turn it back on.

Then, I created a job called fail that would force to fail.
Right-click the ill-fated job –> Properties
 
select Notifications
don’t mess with Alerts (they annoy me at times; my ignorance shows).

Make the ill-fated-job  run so it can fail; check your mail.  If you have enabled mail properly, (turn off + turn on SQL Alert system) you typically won’t have any problems

send email

this evaluates jobs:

declare @tableHTML nvarchar(max) ,
@hdr varchar(max) ;

--------------------------
select case recent_jobs.run_status
when 1 then 'YES'
when 0 then 'NO'
end as run_status, msdb.dbo.sysjobs.name, msg.message, msg.run_date
into [#t]
from (
select max(instance_id) as [MOST RECENT], job_id, server,
run_status
from msdb.dbo.sysjobhistory
group by job_id, server, run_status
) as recent_jobs
inner join msdb.dbo.sysjobs
on recent_jobs.job_id = msdb.dbo.sysjobs.job_id
inner join msdb.dbo.sysjobhistory as msg
on recent_jobs.[MOST RECENT] = msg.instance_id
where ( msdb.dbo.sysjobs.enabled = 1 )

--------------------------

set @hdr = ( N'

Recent SQL Server Job Run

' + N'
<><><><><><>' + N' <><><><><><>' + N' <><><><><><>' ) select @tableHTML = @hdr + cast(( select td = #t.run_status, '', td = #t.name, '', td = #t.message, '', td = #t.run_date, '' from #t for xml path('tr') , type ) as nvarchar(max)) + N'

OK	name	message	date

' ;

exec msdb.dbo.sp_send_dbmail @recipients = 'it@nationalraisin.com',
@subject = 'SQL Daily Status Report', @body = @tableHTML,
@body_format = 'HTML' ;

drop table #t

Whozits & Whatzit: Managing SQL Server email Accounts

/*
I don't know who or where I got this one.
this gives you all the email accounts listed on your SQL Server.
*/
CREATE TABLE #temp01
(
profile_id INT,
[name] VARCHAR(50),
description VARCHAR(50)
)
INSERT INTO #temp01
EXECUTE msdb.dbo.sysmail_help_profile_sp ;
CREATE TABLE #temp02
(
profile_id INT,
profile_name VARCHAR(50),
account_id INT,
account_name VARCHAR(50),
seq int
)
INSERT INTO #temp02
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp ;
CREATE TABLE #temp03
(
account_id INT,
[name] VARCHAR(50),
description VARCHAR(50),
email_address VARCHAR(50),
display_name VARCHAR(50),
replyto_address VARCHAR(50),
servertype VARCHAR(50),
servername VARCHAR(50),
port INT,
username VARCHAR(50),
use_default_credentials VARCHAR(50),
enable_ssl int
)
INSERT INTO #temp03
EXECUTE msdb.dbo.sysmail_help_account_sp ;
SELECT a.name,
b.account_name,
c.description,
c.email_address,
c.display_name,
c.replyto_address,
c.servertype,
c.servername,
c.port,
c.username,
c.use_default_credentials,
c.enable_ssl
FROM [#temp01] AS a
INNER JOIN [#temp02] AS b ON a.profile_id = b.[profile_id]
INNER JOIN [#temp03] AS c ON b.account_id = c.account_id
DROP TABLE #temp01
DROP TABLE #temp02
DROP TABLE [#temp03]

Database Mail Architecture

There are four components to the Database mail Architecture
(These will be on exam 70-431) :

• configuration components
  database mail account - contains the smtp information you have entered for the server (I use my normal email account to play around with)
  database mail profile - a generic name you choose for other applications to handle email routing. "use profile that I called 'main' "... "I want to use the 'my alternate' email profile"...

• messaging components
  this is the host database that holds all teh objects. (hint-hint: host database is is msdb).


• 
  

• executable adn logging & auditing components

enable database mail

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

---
to keep an eye on the emails going in and out, what is failing, try these (found at databasdsournal.com)

sysmail_allitems – This view allows you to return a record set that contains one row for each email message processed by Database mail.

sysmail_event_log – This view returns a row for each Windows or SQL Server error message returned when Database Mail tries to process an email message.

sysmail_faileditems – This view returns one record for each email message that has a status of failed.

sysmail_mailattachments – This view contains one row for each attachment sent

sysmail_sentitems – This view contains one record for every successfully email sent

sysmail_unsentitems – This view contains one record for every email that is currently in the queue to be sent, or is in the process of being sent.
-------
to clean up messages:
sysmail_delete_mailitems_sp – This SP permanently deletes email messages from the msdb internal Database Mail tables

sysmail_delete_log_sp - This SP deletes Database Mail log messages