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 09, 2013 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:04 AM
Points: 37, Visits: 144
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:21 AM
Points: 547, Visits: 1,877
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: Wednesday, April 09, 2014 11:04 AM
Points: 37, Visits: 144
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:21 AM
Points: 547, Visits: 1,877
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: Wednesday, April 09, 2014 11:04 AM
Points: 37, Visits: 144
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: Wednesday, April 09, 2014 11:04 AM
Points: 37, Visits: 144
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