Using a parameter in a procedure for sp_send_dbmail, the mail never gets received

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » Using a parameter in a procedure for...

Using a parameter in a procedure for sp_send_dbmail, the mail never gets received

Rate Topic

Display Mode

Topic Options

Author

Message

a deslandes

Posted Friday, September 14, 2012 2:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:35 PM
Points: 4, Visits: 24

I have a peculiar problem here.

I have a procedure that needs to be called at the end of each package, which in turn does a few things for house keeping and then sends out an email. This is in an SSIS task for SQL 2008, so I call a procedure passing the name of the package as a parameter. This procedure we hope to use for each task we write.

The exec sp_send_dbmail is set up as below, and I use a variable for the @Subject = @P part of it.

EXEC MSDB.dbo.sp_send_dbmail @profile_name = 'SQL99',
@recipients = @TO,
@copy_recipients = @CC,
@blind_copy_recipients = @BC,
@subject = @P,
@body = @Msg,
@query = @Q,
@query_result_width = 512,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt'

It runs fine, and everything returns green. The problem is is that it never shows up in my mailbox.

If I use @subject = 'test',, the email shows up.

If I do the following, it shows up.

DECLARE @P VARCHAR(1000)
SET @P = 'Package Name: '
with
@subject = @P,

If I do anything with the actual variable I have passed, it does not show up.

DECLARE @P VARCHAR(1000)
SET @P = 'Package Name: ' + CONVERT(NVARCHAR(900),@PackageName)

I stopped sending the variable to the procedure, and tried to determine the package name within the procedure, but it still did not work. Any time I use the package name from any source it does not work. This has my completely confused.

Any suggestions?

Post #1359634

Lynn Pettis

Posted Friday, September 14, 2012 2:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 21,589, Visits: 27,395

How is @P getting its value when you aren't getting the email?

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1359636

a deslandes

Posted Friday, September 14, 2012 2:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:35 PM
Points: 4, Visits: 24

Under ideal conditions I pass a parameter @PackageName.

If I use @subject = @PackageName, no mail is received

If I use SET @P = @PackageName
and then use @subject = @P, no mail is received

Post #1359639

Lynn Pettis

Posted Friday, September 14, 2012 2:13 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 21,589, Visits: 27,395

a deslandes (9/14/2012)

Under ideal conditions I pass a parameter @PackageName.

If I use @subject = @PackageName, no mail is received

If I use SET @P = @PackageName
and then use @subject = @P, no mail is received

And how does the parameter @PackageName get populated?

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1359642

a deslandes

Posted Friday, September 14, 2012 2:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:35 PM
Points: 4, Visits: 24

In the SSIS Execute SQL task I have the following:

EXEC SSIS_MGMT.dbo.LogPackageEnd
@PackageLogID=?
, @BatchLogID = ?
, @EndBatchAudit = ?
, @PackageName = ?

And I have set the System::PackageName as the fourth parameter.

Post #1359646

Lynn Pettis

Posted Friday, September 14, 2012 2:34 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 21,589, Visits: 27,395

Okay, so you have no idea if there is ever a value passed down to the procedure, do you.

My guess, untested, is that the value being passed in is null and even though the procedure apparently returns success, nothing is being sent as a result.

I guess I will leave it to you to test this theory.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1359656

a deslandes

Posted Friday, September 14, 2012 9:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:35 PM
Points: 4, Visits: 24

Actually I have tested it. I have saved it into another table and the package name is there. It is also a System variable in the SSIS package itself, so I have some assurance that it knows it's own name.

I agree that it sounds like the value is null, but even when I use ISNULL() on the parameter, either in an IF statement or ISNULL(@P, 'use this') the email is not being received.

Bottom line is if I use the parameter, the email is not being received. All signs show that it is being sent, and other emails without the parameter are being sent and received.

This is why I am frustrated about it! But thanks for listening, some times that can solve things, and thanks for the suggestions.

Post #1359774

« Prev Topic | Next Topic »

Permissions