SQL Server Execute Store Procedure

[gdawson]

I have a task that connects to the SQL server. In the tasks, I open the DB, exec the stored procedure then close the DB. The stored procedure looks like it was executed, but nothing really gets executed. I can run the same command in SSMS and it runs without errors.

Code: Select all

[Actions\Action22]
ActionID=STRING|"A_DB_OPEN"
Enabled=INTEGER|-1
Name=STRING|"DB Open"
Params=FOLDER

[Actions\Action22\Params]
connectionstring=STRING|"****
override=STRING|"1"
password=STRING|"***"
userid=STRING|"sa"

[Actions\Action23]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"DB Exec SQL"
Params=FOLDER

[Actions\Action23\Params]
assignvar=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""exec dbo.sp_process"""
timeout=STRING|"77777"
varname=STRING|"{SQLOUT}"

[Actions\Action24]
ActionID=STRING|"A_DB_CLOSE"
Enabled=INTEGER|-1
Name=STRING|"DB Close"

[Oleg]

About DB Exec SQL action
This actions just execute the SQL and returns the number only (rows affected).
The action does not return any output data.

Another mistake:
When you specify some variable you must use variable name only without braces
use SQLOUT instead of {SQLOUT}

If you want to catch some data row by row (or line by line) you must use DB Loop action and catch the output in the loop line by line