I created a new stored procedure and a new trigger (see below). It will send an email when a record is inserted into a table (T_DUEDATE_EXTENSIONS). When I run the stored procedure by itself, hard coding my own values, it works fine. However, when I use the trigger and pass the variable, I get the following error:
"Error converting data type varchar to int"
The datetime variables are passed from the trigger into the stored procedure as character via the CONVERT function in the trigger. Am I missing something? I am new to this, so are there any little tricks or rules that I don’t know about? I’ve attached the stored procedure and trigger.
CREATE TRIGGER [TR_I_EMAIL_ACTION_DATE_UPD] ON [dbo].[T_DUEDATE_EXTENSIONS]
FOR INSERT
AS
declare @BusinessReason varchar(1000), @EnteredBy varchar(50)
declare @ActionPlanID int
declare @OriginalDueDate varchar(10), @NewDueDate varchar(10)
select @ActionPlanID = ACTIONPLAN_ID,
@OriginalDueDate = convert(varchar(10),ORIGINAL_DUE_DATE,101),
@NewDueDate = convert(varchar(10),NEW_DUE_DATE, 101),
@BusinessReason = BUSINESS_REASON,
@EnteredBy = ENTERED_BY from inserted
exec ERICA..SP_EMAIL_INSERT_DUEDATE_TRIGGER @ActionPlanID, @OriginalDueDate, @NewDueDate, @BusinessReason, @EnteredBy
-----------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_EMAIL_INSERT_DUEDATE_TRIGGER]
@BusinessReason varchar(1000), --These variables are the parameters sent from the trigger
@ActionPlanID int,
@EnteredBy varchar(50),
@OriginalDueDate varchar(10),
@NewDueDate varchar(10)
as
SET NOCOUNT ON
declare @MsgString varchar(4000) --declaring variables for the subject and body of the email
declare @SubjectString varchar(100)
declare @MsgEnd varchar(600)
declare @MsgBeginning1 varchar(500)
declare @ERICAEmail varchar(50)
declare @Description varchar(1000)
declare @APOwner varchar(200), @FuncOwner varchar(150), @FuncOwnerEmail varchar(150)
--Following is a little errorchecking
select @MsgEnd = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'ReminderEmailFooter')
select @MsgBeginning1 = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'EmailTriggerActionDueDate')
select @ERICAEmail = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'ERICA_EmailInbox')
--select @RecpEmail = isnull(@RecpEmail, @ERICAEmail)
--select @Recp = isnull(@Recp, 'Not Assigned')
select @Description = (select ACTION_DESC FROM T_ACTIONPLAN WHERE ACTIONPLAN_ID = @ActionPlanID)
select @APOwner = (select RESPONSIBLE_PARTY FROM T_ACTIONPLAN WHERE ACTIONPLAN_ID = @ActionPlanID)
select @FuncOwner = (select T_RESPONSIBLE_FUNCTIONS.FUNCTION_R_PARTY FROM T_ISSUES INNER JOIN T_RESPONSIBLE_FUNCTIONS ON T_ISSUES.FUNCTION_ID = T_RESPONSIBLE_FUNCTIONS.FUNCTION_ID INNER JOIN dbo.T_ACTIONPLAN ON dbo.T_ISSUES.ISSUE_ID = dbo.T_ACTIONPLAN.ISSUE_ID WHERE T_ACTIONPLAN.ACTIONPLAN_ID = @ActionPlanID)
select @FuncOwnerEmail = (select T_RESPONSIBLE_FUNCTIONS.FUNCTION_R_PARTY_EMAIL FROM T_ISSUES INNER JOIN T_RESPONSIBLE_FUNCTIONS ON T_ISSUES.FUNCTION_ID = T_RESPONSIBLE_FUNCTIONS.FUNCTION_ID INNER JOIN dbo.T_ACTIONPLAN ON dbo.T_ISSUES.ISSUE_ID = dbo.T_ACTIONPLAN.ISSUE_ID WHERE T_ACTIONPLAN.ACTIONPLAN_ID = @ActionPlanID)
--setting the subject
--select @SubjectString = 'ERICA ACTION PLAN DUE DATE HAS BEEN UPDATED'
select @SubjectString = '****THIS IS A TEST. PLEASE DISREGARD****'
--These following lines are just populating the body variable with the HTML code that displays the information
select @MsgString = '<html><body><p>'+@FuncOwner+ @MsgBeginning1 +'<ul>'
select @MsgString = @MsgString + '<li><strong>Action ID:</strong>'+str(@ActionPlanID) + '</li>'
select @MsgString = @MsgString + '<li><strong>Description:</strong></li><ul><li>'+@Description+'</li></ul>'
select @MsgString = @MsgString + '<li><strong>Original Due Date:</strong>'+isnull(@OriginalDueDate, 'No Date Assigned')+'</li>'
select @MsgString = @MsgString + '<li><strong>New Due Date:</strong>'+isnull(@NewDueDate, 'No Date Assigned')+'</li>'
select @MsgString = @MsgString + '<li><strong>Business Reason:</strong>'+@BusinessReason
declare @temp varchar(60)
select @temp = isnull((select DISPLAY_NAME from T_UCAMS where USERID = @EnteredBy) + ' ( ' + @EnteredBy + ')', @EnteredBy)
select @MsgString = @MsgString + '<li><strong>Entered By: </strong>' + @temp + '</<li>'
declare @temp2 varchar(60)
select @temp2 = isnull((select DISPLAY_NAME from T_UCAMS where USERID = @APOwner) + ' ( ' + @APOwner + ')', @APOwner)
select @MsgString = @MsgString + '<li><strong>Action Plan Owner: </strong>' + @temp2 + '</<li></ul>'
select @MsgString = @MsgString + @MsgEnd
--And then we send the email
exec master..sp_send_cdosysmail_html @ERICAEmail,@FuncOwnerEmail,@SubjectString, @MsgString
GO
"Error converting data type varchar to int"
The datetime variables are passed from the trigger into the stored procedure as character via the CONVERT function in the trigger. Am I missing something? I am new to this, so are there any little tricks or rules that I don’t know about? I’ve attached the stored procedure and trigger.
CREATE TRIGGER [TR_I_EMAIL_ACTION_DATE_UPD] ON [dbo].[T_DUEDATE_EXTENSIONS]
FOR INSERT
AS
declare @BusinessReason varchar(1000), @EnteredBy varchar(50)
declare @ActionPlanID int
declare @OriginalDueDate varchar(10), @NewDueDate varchar(10)
select @ActionPlanID = ACTIONPLAN_ID,
@OriginalDueDate = convert(varchar(10),ORIGINAL_DUE_DATE,101),
@NewDueDate = convert(varchar(10),NEW_DUE_DATE, 101),
@BusinessReason = BUSINESS_REASON,
@EnteredBy = ENTERED_BY from inserted
exec ERICA..SP_EMAIL_INSERT_DUEDATE_TRIGGER @ActionPlanID, @OriginalDueDate, @NewDueDate, @BusinessReason, @EnteredBy
-----------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_EMAIL_INSERT_DUEDATE_TRIGGER]
@BusinessReason varchar(1000), --These variables are the parameters sent from the trigger
@ActionPlanID int,
@EnteredBy varchar(50),
@OriginalDueDate varchar(10),
@NewDueDate varchar(10)
as
SET NOCOUNT ON
declare @MsgString varchar(4000) --declaring variables for the subject and body of the email
declare @SubjectString varchar(100)
declare @MsgEnd varchar(600)
declare @MsgBeginning1 varchar(500)
declare @ERICAEmail varchar(50)
declare @Description varchar(1000)
declare @APOwner varchar(200), @FuncOwner varchar(150), @FuncOwnerEmail varchar(150)
--Following is a little errorchecking
select @MsgEnd = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'ReminderEmailFooter')
select @MsgBeginning1 = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'EmailTriggerActionDueDate')
select @ERICAEmail = (select PARAM_VALUE FROM T_EMAIL_PARAMETERS WHERE PARAMETER = 'ERICA_EmailInbox')
--select @RecpEmail = isnull(@RecpEmail, @ERICAEmail)
--select @Recp = isnull(@Recp, 'Not Assigned')
select @Description = (select ACTION_DESC FROM T_ACTIONPLAN WHERE ACTIONPLAN_ID = @ActionPlanID)
select @APOwner = (select RESPONSIBLE_PARTY FROM T_ACTIONPLAN WHERE ACTIONPLAN_ID = @ActionPlanID)
select @FuncOwner = (select T_RESPONSIBLE_FUNCTIONS.FUNCTION_R_PARTY FROM T_ISSUES INNER JOIN T_RESPONSIBLE_FUNCTIONS ON T_ISSUES.FUNCTION_ID = T_RESPONSIBLE_FUNCTIONS.FUNCTION_ID INNER JOIN dbo.T_ACTIONPLAN ON dbo.T_ISSUES.ISSUE_ID = dbo.T_ACTIONPLAN.ISSUE_ID WHERE T_ACTIONPLAN.ACTIONPLAN_ID = @ActionPlanID)
select @FuncOwnerEmail = (select T_RESPONSIBLE_FUNCTIONS.FUNCTION_R_PARTY_EMAIL FROM T_ISSUES INNER JOIN T_RESPONSIBLE_FUNCTIONS ON T_ISSUES.FUNCTION_ID = T_RESPONSIBLE_FUNCTIONS.FUNCTION_ID INNER JOIN dbo.T_ACTIONPLAN ON dbo.T_ISSUES.ISSUE_ID = dbo.T_ACTIONPLAN.ISSUE_ID WHERE T_ACTIONPLAN.ACTIONPLAN_ID = @ActionPlanID)
--setting the subject
--select @SubjectString = 'ERICA ACTION PLAN DUE DATE HAS BEEN UPDATED'
select @SubjectString = '****THIS IS A TEST. PLEASE DISREGARD****'
--These following lines are just populating the body variable with the HTML code that displays the information
select @MsgString = '<html><body><p>'+@FuncOwner+ @MsgBeginning1 +'<ul>'
select @MsgString = @MsgString + '<li><strong>Action ID:</strong>'+str(@ActionPlanID) + '</li>'
select @MsgString = @MsgString + '<li><strong>Description:</strong></li><ul><li>'+@Description+'</li></ul>'
select @MsgString = @MsgString + '<li><strong>Original Due Date:</strong>'+isnull(@OriginalDueDate, 'No Date Assigned')+'</li>'
select @MsgString = @MsgString + '<li><strong>New Due Date:</strong>'+isnull(@NewDueDate, 'No Date Assigned')+'</li>'
select @MsgString = @MsgString + '<li><strong>Business Reason:</strong>'+@BusinessReason
declare @temp varchar(60)
select @temp = isnull((select DISPLAY_NAME from T_UCAMS where USERID = @EnteredBy) + ' ( ' + @EnteredBy + ')', @EnteredBy)
select @MsgString = @MsgString + '<li><strong>Entered By: </strong>' + @temp + '</<li>'
declare @temp2 varchar(60)
select @temp2 = isnull((select DISPLAY_NAME from T_UCAMS where USERID = @APOwner) + ' ( ' + @APOwner + ')', @APOwner)
select @MsgString = @MsgString + '<li><strong>Action Plan Owner: </strong>' + @temp2 + '</<li></ul>'
select @MsgString = @MsgString + @MsgEnd
--And then we send the email
exec master..sp_send_cdosysmail_html @ERICAEmail,@FuncOwnerEmail,@SubjectString, @MsgString
GO