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"
Procs called from For Databases - issue with NULL parameters
Did you enter any value (datetime) for @ASOF?
How your job definition form for DB looks like?
It can be, that your form contains the assignation of @ASOF value to anything.
The datetime value is not possible to enter from DB form. It appends just a string value (varchar), it does not metter, that you define datetime value type.
take care of it. (maybe it is not your case, but test it). I had a problem with oracle DB, when there was need to enter datetime as parameter. I was forced to change a procedure to convert varchar to datetime and then use it in main procedure.
How your job definition form for DB looks like?
It can be, that your form contains the assignation of @ASOF value to anything.
The datetime value is not possible to enter from DB form. It appends just a string value (varchar), it does not metter, that you define datetime value type.
take care of it. (maybe it is not your case, but test it). I had a problem with oracle DB, when there was need to enter datetime as parameter. I was forced to change a procedure to convert varchar to datetime and then use it in main procedure.
- Ross_T_Boss
- Nouveau
- Posts: 21
- Joined: 08 Aug 2008 12:00