How to pass arguments using Execute Database Task action?

V5 Question
1

I want to execute a custom SQL script via Action and pass in 6 parameters.
I successfully called a JScript function via {CALL:handler.function} and in that script I passed stored SQL into either sql.Query(x) or sql,ExecSQL(x) but couldn’t solve the problem of it always throwing an Exception, even though the SQL script works fine in SSMS.

Now using Execute Database Task instead (thanks to @Jesse for the suggestion)
Each time the Action is fired, an InventoryTransaction record is created, but right now the field values are set to some default test values which the SQL script uses if any input parameters are NULL.

I was thinking the arguments specified in the Execute Database Task action would feed into the SQL script as variables but it doesn’t seem to work using @1, @2, @3 etc.

How can I use the arguments in the SQL script?

Here is my calling action:

Execute Database Task2.png788×261 8.68 KB

and at the top of the sql script:

2

I would like to see the script. I can understand that it works in SSMS, but I am guessing there is something amiss when you try execution via JS sql.ExecSQL… and I do not think it is a script length problem.

PM it to me if you want to keep it a secret for some reason, and supply the error as well.

I need to look at that Action - never used it, and don’t know what the Arguments parameter is supposedly used for, but I doubt it is for feeading @vars … could be wrong, but it might be more like an execution using sqlcmd using parameters, so your script might need to receive them differently.

3

Thanks for responding. The problem was that i was passing unquoted string values into the SQL.
Silly mistake, but the lack of error info makes it a very time consuming task to find out what’s happening.

One must be careful to surround string parameters with quotes when passing into SQL scripts from Action arguments.

About the Execute Database Task action, i still have no idea how to pass in arguments. I’m guessing it was made for batch files but not SQL scripts.
Executing script files like that in a live environment is probably not a good idea anyway.

4

It was made for SQL scripts. First without the arguments are you able to execute the task at all? If not then you probably have it linked wrong in the action.

5

I mean… about passing arguments,… was that option in the Execute Database Task made for passing arguments into SQL scripts?
I notice there is shared functionality with other types of scripts. cmd batch files, and VB scripts. But i didnt yet find any reference or examples of how to pass arguments.

Itis worth mentionong again that in a live enviroment one would need to make sure non-priveleged users couldnt modify that Database Tasks folder.

6

I am not sure I have never used that action however I can say that you setup variables wrong. You should set up a single [:Variable] and then enter your arguments comma separated in the action.

EDIT: Ok I am wrong sorry scratch what I said above.

I am not sure how to use arguments. @emre can you elaborate? How do we use Arguments for Execute Database Tasks action.

1 Like
7

yes, the script ran fine when called by the action except none of the input parameters were passed in afaik.
Instead of trying to guess how this works… @Emre could you shed some light on this?

2 Likes