Hi experts, I'm trying to make this email distribution more dynamic and flexible whereby I can simply retrieve the list of recipients from a table. I'm retrieving the list of email addresses but it fails in the send mail SQL task:
DECLARE @body NVARCHAR(MAX)
DECLARE @Recipients varchar(500);
DECLARE @RecipientsInternal varchar(500);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@body = @body,
@recipients = @RecipientsInternal,
@subject = 'My Subject Line';
The error is: At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients"
The variable @RecipientsInternal is populated in an earlier (SSIS) task.
Any ideas? Thanks
If set in an earlier task why are you resetting it again?
DECLARE @RecipientsInternal varchar(500);
... More info. When I force a value into @RecipientsInternal like
Set @RecipientsInternal = 'MyEmailAddy' the send mail works.
So apparently the way I'm retrieving the value for @RecipientsInternal is not correct. I'll post back in this thread or maybe a new one.
Thanks.....
Its not going to magically grab the value from ssis workflow
1 Like
Good question, @yosiasz. I did that because without it, I got error" Must declare scalar
variable @RecipientsInternal". Ah I need to tweak the SQL task to bring in the value as you say. Thanks
SSIS has a send mail task - is there a reason you are calling out to SQL Server to send an email when that task is available?
You can setup the task to use package variables and expressions to set the package variables to your expected values. That is generally much easier to setup and manage than using a SQL task and calling out to SQL Server to send the email.
I'm stuck.
DECLARE @body NVARCHAR(MAX)
DECLARE @Recipients varchar(500);
DECLARE @WhoToSendTo varchar(500);
--DECLARE @RecipientsInternal varchar(500);
Set @WhoToSendTo = @[User::RecipientsInternal]
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@body = @body,
@recipients = @RecipientsInternal,
@subject = 'My SUbject Line';
I still get "Must declare scalar variable @RecipientsInternal".
even after adding
Good question, @jeffw8713 I thought the SQl Mail task doesn't have visibility to package variables. Is that not true? Anyway, I'll experiment with it
Update... I don't think the SQL Mail task is robust enough to handle email addresses as a variable. I'm stuck as to a viable solution.
I've confirmed that the SQL Task which select a row then assigns the column value to the variable
@RecipientsExternal IS working correctly. The variable contains 3 email addys.
But I'm not sure how to get the SQL task to use the variable in the sp_send_dbmail statement. Sorry to be obtuse.
Thanks.
Now you are just hacking.
What component are you using to run that sp_send_dbmail part?
then use the Parameter Mapping. I think you need to do a lot more reading documentation. A little bit more curiosity and digging.
I went down that road but maybe not far enough. I encountered errors but I'll revisit.
