SQL Clone
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
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93213 Visits: 38955
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
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93213 Visits: 38955
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
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93213 Visits: 38955
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
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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.
cbrammer1219
cbrammer1219
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 427
Ok so I have similar problem.

I have a package that reads a file processes it and inserts the data into a table, very simple.
I have all that working, now I want to be notified if the package fails, I can get that to work, however getting the package name in the email, I haven't been able to do, I don't want to hard code the package name, otherwise I'd have one for each package.
I am try to get the system variable passed to the stored procedure. Here's my stored procedure. I have tried to set the Parameter mappings and setting the Result Set but it errors with sp expects param @PKG but was not supplied, any help would be appreciated. It's SQL2012, This is also in the event handler at package level, OnError


CREATE PROCEDURE [dbo].[spSalesDataErrorsV2] @PKG VARCHAR(200)

AS

declare @recipients varchar(255)

declare @subject varchar(255)

EXEC msdb.dbo.sp_send_dbmail @recipients= 'abc@email.com',

@subject = @PKG,----Currently I have the package name hard coded

@body = 'The package failed'

GO


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