Database for Control-M : How using query result

[Scalap]

Hi all,

I want to use a query result via control Module Databse for Control-M

With Open Query in DATABASE tab, i have a "Select count ...." and i want to use the result of the query into control-M

I think the Steps tab is the best way with "On Statement ..." but how doing that ? With a standard variable, anything else ?

For example : if the result return 0 ==> i do mail, it the result > 0 i do another thing

Thanks for your help

[randrade]

Same issue.
Have you found a solution?

[Scalap]

Not with database control Module, but via SQL Script.

Use "Declare" to set a Result variable, exec your request and then user "xp_cmdshell" to launche a ctmvar of your result

See the script :

DECLARE @CMD SYSNAME
DECLARE @CTMVAR varchar(100)
DECLARE @RESULT1 INT
DECLARE @RESULTtext SYSNAME

SELECT @RESULT1 = COUNT (distinct ID_PERSONNE)
FROM xxx.dbo.NOTE_FR
WHERE DATE_COMP >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and DATE_COMP <=GETDATE()

SET @RESULTtext = cast(@RESULT1 as nvarchar(4))
SET @CMD = 'ctmvar -action SET -var "%%\FRS_Not_R1" -varexpr ' + @RESULTtext
EXEC @CTMVAR = xp_cmdshell @CMD


With "Step Tabs" into your CTM Jobs, you can use "%%FRS_Not_R1"

[ThePirate]

I have the job below that looks for a date and if the dates match need to
Force jobs. The query output is searched through a POSTCMD batch job. This job runs everyday and only will Fore the job on the one date that matches the output. You may be able to get some ideas to assist.

Below is my SQL query convertind date to text.

SELECT TOP 1 DateKeyID
FROM dbo.DimDate
WHERE MidMonthCycleDayIndicator = 'Cycle' AND Date >= CONVERT(datetime, (CONVERT(nvarchar(10), GETDATE(), 101))) ORDER BY DateKeyID

Below is the sysout.

Job output:
-----------
SELECT TOP 1 DateKeyID
FROM dbo.DimDate
WHERE MidMonthCycleDayIndicator = 'Cycle' AND Date >= CONVERT(datetime, (CONVERT(nvarchar(10), GETDATE(), 101))) ORDER BY DateKeyID
+-----------+
|DateKeyID |
+-----------+
|20121013 |
+-----------+

Below is the batch job that forces jobs to run today and next day..

@ECHO OFF

REM After running select statement for next mid-month settelement date, script will search sysout to determine date and force job if matches today().
REM Use it in the postcmd of the job that you want to check. Example: POSTCMD = someThing.bat %%$DATE myTABLE myJob Y

REM Detect value in sysout and then force job to active environment
REM Arg1 is the text to look for
REM Arg2 is the TABLE1 name to order
REM Arg3 is the Job to order in TABLE1 specified by Arg2
REM Arg4 is the Job to order in TABLE1 specified by Arg2
REM Arg5 is the flag to force Y=Force N=No Force
REM Arg6 is the Tommorrow's Date
REM Arg7 is the Job to order in TABLE1 specified by Arg2
REM Arg8 is the TABLE2 name to order
REM Arg9 is the Job to order in TABLE2 specified by Arg8

SET SEARCH=%~1
SET TABLE1=%~2
SET JOB1=%~3
SET JOB2=%~4
SET FORCE=%~5
SET APJ001RUNDATE=%~6
SET JOB3=%~7
SET TABLE2=%~8
SET JOB4=%~9

ECHO.
ECHO HP3K-APJ001 Run Date: "%APJ001RUNDATE%"

ctmvar -ACTION SET -var "%%%%\APJ001RUNDATE" -VAREXPR "%APJ001RUNDATE%"
REM Make Char RUNNO Hexidecimal Value

SET RNUM=%RUNNO%

IF %RNUM%==00001 SET RUNNUM=1
IF %RNUM%==00002 SET RUNNUM=2
IF %RNUM%==00003 SET RUNNUM=3

REM Set the Accounting Period End Date

ECHO.

for /f "delims=" %%a in ('FINDSTR /R "^20.*$" "\\Wwr-control-m\BMCSoftware\CONTROL-M Agent\Default\SYSOUT\%MEMNAME%_%ORDERNO%_%RUNNUM%.dat"') do set FOUND=%%a

echo Found Date: "%FOUND%"

ctmvar -ACTION SET -var "%%%%\ACCTPERIODENDDATE" -VAREXPR "%FOUND%"

ECHO.

FIND /I "%SEARCH%" "\\Wwr-control-m\BMCSoftware\CONTROL-M Agent\Default\SYSOUT\%MEMNAME%_%ORDERNO%_%RUNNUM%.dat"

REM IF ELSE syntax must be as seen below to execute.

IF %ERRORLEVEL% EQU 0 (
ECHO Text string: "%SEARCH% Found"
) ELSE (
ECHO Text string: "%SEARCH% Not found") & (EXIT
)

REM Order job in the POSTCMD on both Production and Test

REM (ORDER SYNTAX) ctmorder -SCHEDTAB %TABLE?% -JOBNAME "%JOB?%" -ODATE ODAT -FORCE %FORCE%

REM Order MEJ200 to run today
ctmorder -SCHEDTAB %TABLE1% -JOBNAME "%JOB1%" -ODATE ODAT -FORCE %FORCE%

REM Order APJ001 to run tomorrow on Production
ctmorder -SCHEDTAB %TABLE1% -JOBNAME "%JOB2%" -ODATE %APJ001RUNDATE% -FORCE %FORCE% -odate_option RUN_DATE

REM Email MidMonth Settlement day notice using BLAT

blat - -body "Mid-Month Settlement Processing is Today. You should receive a notice tomorrow that HP3K-AMJ001 has run on the HP3000. If you fail to receive the notice the Accounting Period End Date has not been updated and jobs will fail." -to hpsysmgr@weltman.com -subject "Mid-Month Settlement Processing is Today."