SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL task is not running when I pass one parameter via variable and the other 2 hard coded


SQL task is not running when I pass one parameter via variable and the other 2 hard coded

Author
Message
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
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
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3436 Visits: 3149
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?
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
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.
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3436 Visits: 3149
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).
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
Can you paste in the text of the query and a screenshot of the parameter mapping please.
Phillip.Putzback
Phillip.Putzback
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 227
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
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