Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
a deslandes
a deslandes
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24195 Visits: 37959
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)
a deslandes
a deslandes
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24195 Visits: 37959
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

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)
a deslandes
a deslandes
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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:TongueackageName as the fourth parameter.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24195 Visits: 37959
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

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)
a deslandes
a deslandes
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search