Page 1 of 1

Stored Procedure to output global variable in CM for dBases

Posted: 19 Dec 2012 7:20
by ThePirate
I am trying to output to a global variable using a stored procedure using the CM for Databases. I would like to use that variable in a dependent job. The stored proc is completing successfully but does not output to the global variable. My simple stored proc is below and on the Databases tab there are two fields I can populate (Value & Auto-edit Variable). I have tried many combinations and none are successful. I check the value of the %%TEST variable using ctmvar -ACTION LIST. My proc is below. Any thoughts?

USE [wwrADWH]
GO
/****** Object: StoredProcedure [ediSend].[usp_GetJobParameters] Script Date: 12/17/2012 11:57:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ediSend].[usp_GetJobParameters]
@result nvarchar(256) OUTPUT
AS BEGIN

SET NOCOUNT ON
BEGIN TRY

SELECT TOP 1 @result = Parameter1
FROM ediSend.JobParameters;


END TRY
BEGIN CATCH
DECLARE @errNbr INT
EXEC @errNbr = dbo.prcThrowError
RETURN @errNbr
END CATCH
END

Posted: 19 Dec 2012 9:43
by ThePirate
I found the solution to be that you must enter NULL for the Value field and %%\TEST for the Auto-Edit Variable.

Posted: 01 Jan 2013 4:21
by mauriziog
To have a global variable list you can use the utility ctmvar in a job:
ctmvar -action LIST

I think that for your purpose can be more simple
Regards

Posted: 01 Jan 2013 4:36
by ThePirate
If you look at my post it states:

I check the value of the %%TEST variable using ctmvar -ACTION LIST.

I was using your suggestion to check if the variable is created and found that by only using %%TEST it only created the variable on the STEPS tab and I needed a Global variable to use in another job therefore I needed to use %%\TEST.