Procs called from For Databases - issue with NULL parameters
Posted: 13 Apr 2012 5:13
Hi folks,
Within my team we've been seeing some issues with Control-M for Databases that we just can't figure out. I'm hoping someone can advise if this appears something within the Development team or an issue with how the CM works.
Issue: Sybase procedures with optional parameters requiring values within job editing form.
Based on discussion with our development team, the procs in question should be able to run without declaring this @ASOF parameter (it will determine the date automatically if nothing is defined), however when we run the proc through Control-M, we receive the following error.
Error
Request statement:
------------------
+-------------------------------+
| Stored Procedure |
+-------------------------------+
|USP_MED_COMM_LTRS_30DA_LATE |
+---------------+---------------+
|@RETURN_VALUE | |
+---------------+---------------+
|@ASOF | |
+---------------+---------------+
Job failure message:
Parameter #2 has not been set.
If we manually use a date to define @ASOF, it works just fine.
Proc Head
CREATE PROCEDURE dbo.USP_MED_COMM_LTRS_30DA_LATE (@ASOF datetime = NULL)
AS
begin
set nocount on
--error controls
declare @myerror integer, @errortxt varchar(128)
--D-SQL buffer
declare @d_sql varchar(255)
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--TODAY - this variable will hold the today's date wether from @ASOF or GETDATE()
declare @TODAY datetime
if @ASOF is NULL
set @TODAY = convert(char(8),getdate(),112)
else
set @TODAY = @ASOF
-- what is the furthest back we can go
declare @OLDEST datetime
select @OLDEST=
convert(char(6),
dateadd (mm,
-1 , --actual interval
@TODAY), -- the precise date/time
112) + '01' -- the day we're going to use (first of the prior month)
from dbo.UST_MCARE_LTR_30_50_LETTER_SELECT
select getdate()"NOW_IS",@TODAY"@TODAY",@OLDEST"@OLDEST"
Within my team we've been seeing some issues with Control-M for Databases that we just can't figure out. I'm hoping someone can advise if this appears something within the Development team or an issue with how the CM works.
Issue: Sybase procedures with optional parameters requiring values within job editing form.
Based on discussion with our development team, the procs in question should be able to run without declaring this @ASOF parameter (it will determine the date automatically if nothing is defined), however when we run the proc through Control-M, we receive the following error.
Error
Request statement:
------------------
+-------------------------------+
| Stored Procedure |
+-------------------------------+
|USP_MED_COMM_LTRS_30DA_LATE |
+---------------+---------------+
|@RETURN_VALUE | |
+---------------+---------------+
|@ASOF | |
+---------------+---------------+
Job failure message:
Parameter #2 has not been set.
If we manually use a date to define @ASOF, it works just fine.
Proc Head
CREATE PROCEDURE dbo.USP_MED_COMM_LTRS_30DA_LATE (@ASOF datetime = NULL)
AS
begin
set nocount on
--error controls
declare @myerror integer, @errortxt varchar(128)
--D-SQL buffer
declare @d_sql varchar(255)
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--TODAY - this variable will hold the today's date wether from @ASOF or GETDATE()
declare @TODAY datetime
if @ASOF is NULL
set @TODAY = convert(char(8),getdate(),112)
else
set @TODAY = @ASOF
-- what is the furthest back we can go
declare @OLDEST datetime
select @OLDEST=
convert(char(6),
dateadd (mm,
-1 , --actual interval
@TODAY), -- the precise date/time
112) + '01' -- the day we're going to use (first of the prior month)
from dbo.UST_MCARE_LTR_30_50_LETTER_SELECT
select getdate()"NOW_IS",@TODAY"@TODAY",@OLDEST"@OLDEST"