Rick Caminiti dotCOM

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ‘’s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ’select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

I have been in under the impression that you could not drop SQL Agent Jobs without being able to look at the properties and being able to get the JOB ID, since you need the jobid to pass to the sp_delete_job function.  Because of that, whenever I wanted to change a Job, I always had to incude a manual step to log onto the server, right click and delete the job.

However, I found a simple script to automate this, so now I can just include this in my script that will recreate the job.  Simple… should have known.
USE [msdb]
GO
DECLARE
@jobid sysname;
IF
EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME')
BEGIN
SELECT
@jobid = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME'
EXEC  msdb.dbo.sp_delete_job @job_id=@jobid
END