hiteksoftware.com

[Brett]

Can Automize capture stdout from executed tasks - in this case I am trying to capture output of print statements in T-sql procedures executed on sql server. Task executes fine but dosen't capture trace messages from the sql stdout.

Thanks
Brett.

[Brett]

An additional query to this is that setting a return code inside some sql dosen't appear to get picked up - seems to return code = 0 every time. In other words the successful execution of the call not the result.

Sorry to keep posting in this thread but there is no SQL thread.

Thanks

[Robert]

Which task are you using to run the SQL query?

Is this a database SQL task?
or
Are you running a batch file that calls some code/program that runs the SQL query?

[Brett]

I am running the task as an sql query - below is a sample procedure I have been using for testing

ALTER PROCEDURE TestProc
AS
BEGIN
IF EXISTS ( SELECT 1 FROM DQ.dbo.METRIC_RUNS MR
WHERE MR.RUN_SEQ = 605 )
BEGIN
PRINT 'EXISTS - FAILURE' -- 605
RETURN 2
END
ELSE
BEGIN
PRINT 'SUCCESS'
RETURN 0
END
END

This is run as a SQL Task - using 'exec testproc' - however none of the print output is picked up in the task or output logs and the return codes are not recognised.

However from further testing I can now force an abort using the t-sql RAISERROR statement - so the failure leg now uses raiserror instead of return and terminates with a non-zero status code. This allows me to stop the task run at that point - it would be an added bonus to be able to collect the print statement output for success as well as failure.

Hope this explains whats going on - I have a solution with raiseerror but if return code or print output could be captured that would be great.

Thanks.

[Robert]

Hi, thanks for the feedback, but I did not get which Automize task type you are using?
WinCommand
Command
Database SQL

[Brett]

Database SQl - thats what I have been testing with mainly - although I did try the orhers using OSQL to execute a command line sql call - but that has the same problems. What seems to happen is that the executing function passes back return code zero to say it has succesfully executed the storred procedure and appears to miss the return code being set inside the proc. From one view that is quite correct it has succesfully executed the procedure with no problem hence zero - but obviously a record not found condition may require the job stream to halt hence the return codes.

Thanks

[Brett]

Little bit of further testing - changing the sql statement on the database sql task to the following

DECLARE @res int EXEC @res=testproc SELECT @res

results in the value 2 being recorded in the results file specified for the task. No much use but interesting that its sitting in there somewhere

[Robert]

yes, the wincommand and command tasks will only return the main process exit code. Only, the command task provides stdout and stderr support. I am surprised that stdout and stderr were not output to the output and tasklogs using the command task.

[Brett]

I will in the spirit of experimentation try the command options again - thanks for the response and will keep you posted.

[Brett]

You are correct - I must have tested command line with windows command not plain command

executing through command line

osql -E -d dqcrods_605 -Q "exec testproc"

still gets return code zero but the print statements are captured and displayed in the output log.