November 12, 2012 at 3:35 pm
Hi folks
I have a problem with a SP that is intended to send an email using the system SP sp_send_dbmail and also store the email details in a table named DBMail.
The table definition is as follows:
/*
Tuesday, 13 November 20124:39:50 AM
User:
Server: HARVEYNORMAN-PC\SQLWEB64_2008
Database: SamaritansPurseTas
Application:
*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.DbMail
DROP CONSTRAINT DF_DbMail_DateTimeSentUTC
GO
CREATE TABLE dbo.Tmp_DbMail
(
MailItemID int NOT NULL IDENTITY (1, 1),
DateTimeSentUTC datetime NOT NULL,
Profile varchar(100) NOT NULL,
Recipients varchar(MAX) NULL,
CC varchar(MAX) NULL,
BCC varchar(MAX) NULL,
Subject nvarchar(255) NULL,
Body nvarchar(MAX) NULL,
BodyFormat varchar(20) NOT NULL,
Importance varchar(6) NOT NULL,
Sensitivity varchar(12) NOT NULL,
Sender varchar(MAX) NULL,
FilesAttached nvarchar(MAX) NULL,
ReplyTo varchar(MAX) NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_DbMail SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_DbMail ADD CONSTRAINT
DF_DbMail_DateTimeSentUTC DEFAULT (getutcdate()) FOR DateTimeSentUTC
GO
SET IDENTITY_INSERT dbo.Tmp_DbMail ON
GO
IF EXISTS(SELECT * FROM dbo.DbMail)
EXEC('INSERT INTO dbo.Tmp_DbMail (MailItemID, DateTimeSentUTC, Profile, Recipients, CC, BCC, Subject, Body, BodyFormat, Importance, Sensitivity, Sender, FilesAttached, ReplyTo)
SELECT MailItemID, CONVERT(datetime, DateTimeSentUTC), Profile, Recipients, CC, BCC, Subject, Body, BodyFormat, Importance, Sensitivity, Sender, FilesAttached, ReplyTo FROM dbo.DbMail WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_DbMail OFF
GO
DROP TABLE dbo.DbMail
GO
EXECUTE sp_rename N'dbo.Tmp_DbMail', N'DbMail', 'OBJECT'
GO
ALTER TABLE dbo.DbMail ADD CONSTRAINT
PK_DbMail PRIMARY KEY CLUSTERED
(
MailItemID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
select Has_Perms_By_Name(N'dbo.DbMail', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.DbMail', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.DbMail', 'Object', 'CONTROL') as Contr_Per
The SP definition is as follows:
USE [SamaritansPurseTas]
GO
/****** Object: StoredProcedure [dbo].[procDBMailInsertAndSendEmail] Script Date: 11/13/2012 09:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Ross Petersen
-- Create date: 12 November, 2012
/* Description:Inserts a record into the DBMail table
then generates and sends an email
using the System SP sp_send_dbmail */
-- =============================================
ALTER PROCEDURE [dbo].[procDBMailInsertAndSendEmail]
-- Add the parameters for the stored procedure here
@datetimesent datetime = GETUTCDATE,
@profile varchar(100) = 'SQLWEB64_2008_SamaritansPurseTas_DefaultProfile',
@recipients varchar(max) = NULL,
@cc varchar(max) = NULL,
@bcc varchar(max) = NULL,
@subject nvarchar(255) = NULL,
@body nvarchar(max) = NULL,
@bodyformat varchar(20) = 'HTML',
@importance varchar(6) = 'Normal',
@sensitivity varchar(12) = 'Normal',
@sender varchar(max) = 'rossandsuzanne@iprimus.com.au',
@filesattached nvarchar(max) = NULL,
@replyto varchar(max) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @mod_subj nvarchar(255)
DECLARE @mod_body nvarchar(max)
DECLARE @mail_item int
DECLARE @send_error int
DECLARE @insert_error int
BEGIN
/*
check to see that there is one recipient in either:
Recipients, CC or BCC
if no recipients,cancel the procedure
*/
IF @recipients IS NULL AND @cc IS NULL AND @bcc IS NULL
-- cancel procedure
RETURN
/* check that there is content
if no content then cancel this procedure
content is defined by the following parameters:
@body
@subject
@filesattached */
IF @body IS NULL AND @subject IS NULL AND @filesattached IS NULL
-- cancel procedure
RETURN
END
/*
insert the details into the table DBMail
check to see if an error occurred, and if so,
exit the procedure immediately
capture the automatically generated identity value
for the field MailItemID and assign it to the
variable @mail_item
taking the value for the MailItemID and the other
values for the fields as specified by the proc
arguments, generate and send the email
*/
BEGIN
INSERT INTO DbMail
(Recipients, CC, BCC, Subject, Body, BodyFormat, Importance, Sensitivity, FilesAttached, ReplyTo)
VALUES(@recipients, @cc, @bcc, @subject, @body, @bodyformat, @importance, @sensitivity, @filesattached, @replyto)
SELECT @insert_error = @@ERROR
IF @insert_error > 0
-- an error has occurred in the insert operation, therefore exit the procedure
RETURN
ELSE
-- no error occurred, therefore continue
BEGIN
-- get the newly generated identity value for the field MailitemID
SELECT @mail_item = SCOPE_IDENTITY()
/*
having got the identity value, we want to get this info
into both the Subject and Body fields of the actual email,
along with any data that is specified in the proc arguments
for the two fields
note that because both the Subject and Body arguments (@subject, @body)
can both be NULL, we have to check for that for formatting purposes
*/
IF @subject IS NULL
BEGIN
SELECT @mod_subj = '(S/P OCC Mail Item ID: ' + CAST(@mail_item AS nvarchar(255)) + ')'
END
ELSE
BEGIN
SELECT @mod_subj = @subject + ' (S/P OCC Mail Item ID: ' + CAST(@mail_item AS nvarchar(255)) + ')'
END
IF @body IS NULL
BEGIN
SELECT @mod_body = '(S/P OCC Mail Item ID: ' + CAST(@mail_item AS nvarchar(255)) + ')'
END
ELSE
BEGIN
SELECT @mod_body = @body + ' (S/P OCC Mail Item ID: ' + CAST(@mail_item AS nvarchar(255)) + ')'
END
-- generate the email using the system SP sp_send_dbmail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipients,
@copy_recipients = @cc,
@blind_copy_recipients = @bcc,
@subject = @mod_subj,
@body = @mod_body,
@body_format = @bodyformat,
@importance = @importance,
@sensitivity = @sensitivity,
@file_attachments = @filesattached,
@mailitem_id = @mail_item,
@from_address = @sender,
@reply_to = @replyto
-- check to see if there were any errors
SELECT @send_error = @@ERROR
IF @send_error > 0
BEGIN
-- there was an error when sending the email
-- therefore the email wasn't sent
-- therefore, because there is now a record
-- in the table DBMail, we have to delete that record
DELETE FROM DbMail
WHERE DbMail.MailItemID = @mail_item
END
ELSE
BEGIN
-- there wasn't an error involved with sending
-- therefore we have to update the data in the fields
-- Subject & Body in the table, so they match
-- the equivalent fields in the actual Email
UPDATE SamaritansPurseTas.dbo.DbMail
SET Subject = @mod_subj, Body = @mod_body
WHERE SamaritansPurseTas.dbo.DbMail.MailItemID = @mail_item
END
END
END
END
When I run the SP as per the follows:
USE [SamaritansPurseTas]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[procDBMailInsertAndSendEmail]
@recipients = N'rossandsuzanne@iprimus.com.au',
@subject = N'Test Email for Insert into DBMail table and Send Email',
@body = N'Test Email for Insert into DBMail table and Send Email',
@bodyformat = N'Text',
@sender = N'rossandsuzanne@iprimus.com.au'
SELECT'Return Value' = @return_value
GO
I get the following message:
Msg 241, Level 16, State 1, Procedure procDBMailInsertAndSendEmail, Line 0
Conversion failed when converting date and/or time from character string.
I am presuming that the error is occurring in the proc arguments section, specifically the line that says @datetimesent datetime = GETUTCDATE.
This is confusing, because my understanding of GETUTCDATEis that it returns a date and time with a datatype of datetime.
If somebody could throw some light on the subject, I would appreciate it.
Kind regards and blessings
Ross
November 12, 2012 at 6:34 pm
Looking at this:
ALTER PROCEDURE [dbo].[procDBMailInsertAndSendEmail]
-- Add the parameters for the stored procedure here
@datetimesent datetime = GETUTCDATE,
Remove the @datetimesent parameter. You aren't even using it in your procedure. You have a default value being assigned to the column in your dbo.DBMail table and the column is not referenced in the insert statement.
November 12, 2012 at 7:18 pm
Hi Lynn
Oh, of course - thankyou so much.
I am surprised I didn't twig to it earlier.
Just an observation - not about this particular post, but I often am writing some code that doesn't do what I think it should be doing and I scratch about for awhile trying different approaches, then all to often I realise that the original code was basically OK, except that it needed a minor amendment.
So, what I have to do is before I start writing code, is just to sit down with pen and paper and just think for awhile and make notes, diagrams etc.
The challenge for me is to actually put the above into practice.
Kind regards
Ross
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply