Thanks Daniel, I am struggling with this concept... I did as you suggested and get processing errors..

Here is the storedProc that is being called from the SQL task

ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))

AS

BEGIN

SET NOCOUNT ON

DECLARE @msg nvarchar(255)

DECLARE @Cancel as int

SET @Cancel = 0

/* check if the batch ID exists */

IF (SELECT count(*) from opsBatch where batchID = @BatchID) = 0

BEGIN

SET @msg = 'Batch ID ' + cast(@BatchID as nvarchar(5)) + ' is not recognised'

raiserror(@msg,1,1)

SET NOCOUNT OFF

RETURN -1 -- quit the routine with error: This should never happen because the data has been extracted from the batch id

END

/* if we have passed all of the above tests then we can update the batch and the lines */

BEGIN TRANSACTION -- all pass or all fail

UPDATE

opsBatchRecord

SET

mailStatus = 'Exported',

MailStatusDate = cast(convert(nvarchar(20),getdate() ,112) as datetime)

WHERE

BatchID = @BatchID

UPDATE

opsBatch

SET

Status = 'Exported',

ExtractFileName = cast(@BatchID as nvarchar(5)) + '_' + @timestamp + '.txt'

WHERE

BatchID = @BatchID

COMMIT TRANSACTION

Return 99

SET NOCOUNT OFF

END

in Mgmt Studio, if you run

EXEC OperationalBatchGenerateFile 1234, '20121113_104300'

Then the procedure runs but does not return a results set.

If you use the right click->Execute on the sp and you get the following script

DECLARE@return_value int

EXEC@return_value = [dbo].[OperationalBatchGenerateFile]

@BatchID = 10111,

@timestamp = N'20121113_114300'

SELECT'Return Value' = @return_value

GO

then the return value is 99 or -1 depending on what happens. It is this value that I want to interrogate.

Can I get at this return value without a results set or alternatively pickup the raiserror()