Trigger, SProc and resulting ERROR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger, SProc and resulting ERROR

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2001-06-19 : 14:35:58

I am trying to create a trigger that launches a sproc on a For Insert, Update. If @TaskHours is >= 10 then an email is sent out. Syntax for both checks out but it doesn't appear to do anything (the table update doesn't happen). Also, if I try to manually update the table the trigger is on, via Enterprise Manager, I get the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Supplied datatype for @user is not allowed, expecting 'varchar'.

Now, I have no idea where @user is coming from....here is my trigger and sproc. Thanks for any insight.

CREATE TRIGGER TenHours
ON ReqChargeTime
FOR INSERT, UPDATE
AS
BEGIN
EXEC master..xp_startmail
usp_TenHours
END

And here is the sproc:

CREATE PROCEDURE usp_TenHours
AS
DECLARE @RequestID int,
@RequesterName varchar(60),
@RequesterEmail varchar(60),
@ScripterEmail varchar(60),
@BusownerEmail varchar(60),
@TechownerEmail varchar(60),
@TaskHours decimal(5),
@TotalTaskHours decimal(5),
@Billable bit,
@NotifyHours bit,
@RequestType char(1),
@CurrentStatus int,
@AllRecipients varchar(255);
SELECT DISTINCT @RequestID = R.Request_ID,
@RequesterName = R.Requester_Name,
@RequesterEmail = R.Requester_Email,
@ScripterEmail = RSD.Scripter_Email,
@BusownerEmail = RS.Busowner_Email,
@TechownerEmail = RS.Techowner_Email,
@TaskHours = SUM(RCT.TaskHours),
@AllRecipients = @RequesterEmail + ';' + @ScripterEmail + ';' + @BusownerEMail + ';' + @TechownerEmail
FROM ReqCommon R
LEFT OUTER JOIN ReqScripterDef RSD ON R.Scripter_ID = RSD.Scripter_ID
LEFT OUTER JOIN ReqScripting RS ON R.Request_ID = RS.Request_ID
LEFT OUTER JOIN ReqChargeTime RCT ON R.Request_ID = RCT.Request_ID
WHERE (R.Billable = 1) AND (RCT.Notify_Hours = 0) AND (R.Current_Status_ID = 3 OR R.Current_Status_ID = 4 OR R.Current_Status_ID = 7)
GROUP BY R.Request_ID, R.Requester_Name, R.Requester_Email, RSD.Scripter_Email, RS.Busowner_Email, RS.Techowner_Email, RCT.TaskHours

--DECLARE @cmd varchar(2000)
DECLARE @Sbody varchar(2000)
DECLARE @Obody varchar(2000)
SET @Sbody='Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(13) + CHAR(13) + 'The application listed below has reached 10 hours of scripting time. As a courtesy, we are notifying you so that you may take proper action (if any) to identify possible issues your application may have. For a detailed report on your request, please click on the following link: (http://dboas.bpweb.bp.com/StatusDetailScripting.asp?request_id=' + CAST(@RequestID as varchar(10)) + '). For more information regarding your request, please contact your scripter. Thank you.'
SET @Obody='Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(13) + CHAR(13) + 'The application or project listed below has reached 10 billable hours. As a courtesy, we are notifying you so that you may take proper action (if any) to identify possible issues your application or project may have. For a detailed report on your request, please click on the following link: (http://dboas.bpweb.bp.com/StatusDetailOther.asp?request_id=' + CAST(@RequestID as varchar(10)) + '). For more information regarding your request, please contact your scripter. Thank you.'

IF (@RequestType = 'S') AND (@TaskHours >= 10)
BEGIN
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='Your Request has reached 10 billable hours', @dbuse='Staging', @message=@Sbody

UPDATE ReqChargeTime
SET Notify_Hours = 1
END

ELSE IF (@RequestType = 'O') AND (@TaskHours >= 10)
BEGIN
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='Your Request has reached 10 billable hours', @dbuse='Staging', @message=@Obody
UPDATE ReqChargeTime
SET Notify_Hours = 1
END

EXEC master..xp_stopmail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources