Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a parameter in a procedure for sp_send_dbmail, the mail never gets received Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:17 AM
Points: 4, Visits: 27
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
Posted Friday, September 14, 2012 2:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
How is @P getting its value when you aren't getting the email?



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
Posted Friday, September 14, 2012 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:17 AM
Points: 4, Visits: 27
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
Posted Friday, September 14, 2012 2:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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?




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 #1359642
Posted Friday, September 14, 2012 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:17 AM
Points: 4, Visits: 27
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
Posted Friday, September 14, 2012 2:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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.



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 #1359656
Posted Friday, September 14, 2012 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:17 AM
Points: 4, Visits: 27
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 »

Add to briefcase

Permissions Expand / Collapse