December 9, 2013 at 3:16 pm
exec [dbo].[sp_EmailNotification] ?, 'General Ledger - Department Integration Failed', 'Truncation of the Departments General Ledger Table Failed'
On the parameter mapping form I used a variable named that hold me email account, direction = input, data type = varchar, parameter = 0 (I also tried putting in the procs parameter name @msgRecipients), parameter size = 500
It executes, but I never get an email. But if I hard code the first parameter in it works.
Thanks,
Phil
December 10, 2013 at 10:05 am
I suspect this might be the issue.
From here:
Using Parameters with OLE DB Connection Managers
When the Execute SQL task uses the OLE DB connection manager, the BypassPrepare property of the task is available. You should set this property to true if the Execute SQL task uses SQL statements with parameters.
What do you have the property set to, assuming this is an ole db connection. If it is not ole db, what is it?
December 10, 2013 at 12:59 pm
BypassPrepare is already set to true. I could probably\will probably have to use all parameters or none. The problem is it doesn't seem to like mixing parameters with hard coded lines.
EG.
Create Proc Getsomething
@Value1 varchar(5),
@Value2 varchar(5)
AS
...
i can't call this with a SQL task and have my statement = exec Getsomething ?, 'TODAY'
and set my Parameter mapping to pass in my variable to parameter Name = 0 using a variable I am loading in the package.
December 11, 2013 at 7:10 am
Tried to duplicate but I could not. It lets me mix parameters and hardcodes fine (both with and without BypassPrepare).
Are you sure the variable holds the value you want (it can't change at runtime)?
In the meantime, if you can get it going with all params, you may as well do that (or maybe use an expression to build the command so there is no parameter).
December 11, 2013 at 11:10 am
Can you paste in the text of the query and a screenshot of the parameter mapping please.
December 11, 2013 at 11:35 am
I ran profiler and got this
exec sp_executesql N'exec [dbo].[usp_EmailNotification]
@msgRecipients = @P1,
@msgSubject = ''General Ledger - Department Integration Failed'',
@msgBody = ''Truncation of the Departments General Ledger Table Failed''',N'@P1 varchar(26)','''PPutzback@gmail.com'''
I then pasted that into a SSMS query window and ran it and the message it gave me was "Mail (id: 26) queued"
but I never got the message
Then I ran this
exec [chw].[usp_EmailNotification]
@msgRecipients = 'PPutzback@gmail.com',
@msgSubject = 'General Ledger - Department Integration Failed',
@msgBody = 'Truncation of the Departments General Ledger Table Failed'
and the message went through.
Phil
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply