Inserting parameter in error reporting email

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)

Inserting parameter in error reporting email

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-18 : 06:15:37

Morning all

I am attempting to create some validation in a sproc. If a problem occurs during an insert of the data, I want an email message to be sent to inform the user of the problem. However, although I have the email working fine, I need to report a more detailed message. The code below sends an email if a date has already passed. I would like to include the date parameter (@mailDate) in the message body, but have not yet had any success.

IF @mailDate < @todaysDate
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = 'An invalid mail date has been detected. It is over 2 months later than todays date. Please check the data.'
PRINT 'Invalid Maildate. The maildate has already passed: ' + CONVERT(VARCHAR(12), @mailDate)
RETURN (1)
END

I have tried to concatenate the @mailDate to the string:

@message = 'An invalid mail date has been detected: ' + @mailDate + ' It is over 2 months later than todays date. Please check the data.'

I have also tried using the @query parameter:

@query = 'SELECT @mailDate'

But I get the error 'Must declare the variable @mailDate

Can anyone offer a solution to this problem??? Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-18 : 06:42:13

Oh, by the way, i have written out the wrong message. It is supposed to say 'The mail date has already passed.'

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-18 : 08:10:03

Query should be

@query = 'SELECT '+@mailDate

Madhivanan

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-18 : 08:27:08

Hi

I have tried that, but I get the error message when verifying the syntax:
'Incorrect syntax near the +'

If I try: 'SELECT @mailDate', the syntax is ok, but when run, I get the following:
ODBC error 137 (42000) Must declare the variable '@mailDate'.

I can't declare the @mailDate variable in the transaction, because the value is passed by the application and not generated in the sproc.

I think it is something to do with the fact that the sproc variables are of string data type. If I try to perform any concatenations within the proc variables, (+) I get the same error as above: 'Incorrect syntax near the +'

Any other suggestions?

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-18 : 09:02:56

Can you post the entire code used in Procedure?

Madhivanan

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-18 : 09:12:56

I certainly can:

CREATE PROCEDURE [dbo].[usp_InsertA000Documents]
(
@cycleName VARCHAR(7),
@mailDate SMALLDATETIME,
@letterCode VARCHAR(3),
@A000Document VARCHAR(4000)
)

AS

SET NOCOUNT ON

--Setting the date parameter, eliminating the time part of the data type
DECLARE @todaysDate SMALLDATETIME
SET @todaysDate = (SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS SMALLDATETIME))

--Checking to ensure the maildates are not earlier than todays date
IF @mailDate < @todaysDate
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = 'The mail date has already passed. Please check the data.' ,
@query = 'SELECT @mailDate'(not working)
PRINT 'Invalid Maildate. The maildate has already passed: ' + CONVERT(VARCHAR(12), @mailDate)
RETURN (1)
END

--Checking to ensure the maildates are not earlier than todays date
IF @mailDate > DATEADD(MM,2,@todaysDate)
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = 'An invalid mail date has been detected. It is over 2 months later than todays date. Please check the data.'
PRINT 'Invalid Maildate. The maildate is for over 2 months time: ' + CONVERT(VARCHAR(12), @mailDate)
RETURN (1)
END

--Inserting the values into the CycleInstance table if the instance date does not already exist
IF NOT EXISTS (SELECT CycleInstanceDate FROM CycleInstance WHERE CycleInstanceDate = @todaysDate)
INSERT INTO CycleInstance (CycleID, CycleInstanceDate)
SELECT CycleID, @todaysDate
FROM Cycle
WHERE CycleName = @cycleName

--Inserting the values into the Letter Repository table
INSERT INTO LetterRepository (CycleInstanceID, LetterCodeID, MailDate, A000_Document)
SELECT CycleInstanceID, LetterCodeID, @mailDate, @A000Document
FROM CycleInstance, LetterCode
WHERE CycleInstanceDate = @todaysDate
AND LetterCode = @letterCode
GO

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-18 : 09:15:19

Oooo, the pink didn't come out too well - for those with not the best eyesight (destroyed by years infront of VDU's) its supposed to read:

IF @mailDate > DATEADD(MM,2,@todaysDate)
and
CONVERT(VARCHAR(12), @mailDate)

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-21 : 06:07:29

Hello

I don't suppose anyone can come up with a solution to my problem???

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 06:21:22

try this:


declare @qry varchar(500)
set @qry = 'Invalid Maildate. The maildate has already passed: ' + CONVERT(VARCHAR(12), @mailDate)

EXEC master.dbo.xp_sendmail 
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = 'The mail date has already passed. Please check the data.' ,
@query = @qry

Go with the flow & have fun! Else fight the flow

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-21 : 06:36:00

We can't concatenate @message on the fly. Hope it will work:

declare @m varchar(800)
set @m = 'An invalid mail date has been detected: ' +
convert(varchar(12), @mailDate) +
' It is over 2 months later than todays date. Please check the data.'

EXEC master.dbo.xp_sendmail
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = @m

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-21 : 06:42:35

hmm... spirit1 is more thrifty than me. Compare

declare @m varchar(800) to declare @qry varchar(500)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 07:01:41

so that's why they call you freaky linguist
i'd never think of a word like thrifty...

Go with the flow & have fun! Else fight the flow

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-21 : 07:12:59

... and I recalled that word INSTANTLY...

ps: in my school I "studied" (since 8-years-old) Le Francais...

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-21 : 07:20:31

Thanks for your replies. As you both recommended, I did the following:

IF @mailDate < @todaysDate
BEGIN
DECLARE @qry VARCHAR(500)
SET @qry = 'Invalid Maildate:' + CONVERT(VARCHAR(12), @mailDate)

EXEC master.dbo.xp_sendmail
@recipients = 'RebeccaS@f-eds.com',
@subject = 'Abbey Database-Invalid MailDate',
@message = 'The mail date has already passed. Please check the data.',
@query = @qry
PRINT 'Invalid Maildate. The maildate has already passed: ' + CONVERT(VARCHAR(12), @mailDate)
RETURN (1)
END

But am now getting the error message:

ODBC error 170 (42000) Line 1: Incorrect syntax near 'Feb'.
Invalid Maildate. The maildate has already passed: Feb 15 2005
Stored Procedure: Test_Abbey.dbo.usp_InsertA000Documents
Return Code = 1

Hearty head pats

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-21 : 07:36:44

Personally I recommended a bit different solution than spirit's one.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 07:45:58

emm bex, why do you even wanted to do that in a @query parameter of the xp_sendMail??
just add the date to the @message input variable like stoad suggested.
or are you trying something that all of us don't get?

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-21 : 07:53:47

Hellooooo

No, sorry, I just did not study the two examples closely enough, and did not notice that you used the @message variable - DOH!!! I have tried out your suggestion Stoad and it works perfectly! Thank you both so so so much! I love it when things begin to work - hooray!

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 07:55:21

well as much as i'm glad it worked why did you want to put that in a @query when it's supporsed to be a part of the @message??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources