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

SQL task is not running when I pass one parameter via variable and the other 2 hard coded Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 47, Visits: 175
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
Post #1521320
Posted Tuesday, December 10, 2013 10:05 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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?
Post #1521591
Posted Tuesday, December 10, 2013 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 47, Visits: 175
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.
Post #1521652
Posted Wednesday, December 11, 2013 7:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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).
Post #1521885
Posted Wednesday, December 11, 2013 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 47, Visits: 175
Can you paste in the text of the query and a screenshot of the parameter mapping please.
Post #1521994
Posted Wednesday, December 11, 2013 11:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 47, Visits: 175
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
Post #1522000
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse