Stored Procedure to output global variable in CM for dBases

Everything about Control-M Control Modules
Post Reply
User avatar
ThePirate
Nouveau
Nouveau
Posts: 61
Joined: 04 Feb 2008 12:00
Location: Cleveland, OH

Stored Procedure to output global variable in CM for dBases

Post by ThePirate » 19 Dec 2012 7:20

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

User avatar
ThePirate
Nouveau
Nouveau
Posts: 61
Joined: 04 Feb 2008 12:00
Location: Cleveland, OH

Post by ThePirate » 19 Dec 2012 9:43

I found the solution to be that you must enter NULL for the Value field and %%\TEST for the Auto-Edit Variable.

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 01 Jan 2013 4:21

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

User avatar
ThePirate
Nouveau
Nouveau
Posts: 61
Joined: 04 Feb 2008 12:00
Location: Cleveland, OH

Post by ThePirate » 01 Jan 2013 4:36

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.

Post Reply