Procs called from For Databases - issue with NULL parameters

Everything about Control-M Control Modules
Post Reply
User avatar
jmonlea
Nouveau
Nouveau
Posts: 1
Joined: 20 Jan 2011 12:00

Procs called from For Databases - issue with NULL parameters

Post by jmonlea » 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"

hipikll

Post by hipikll » 15 Apr 2012 10:10

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.

User avatar
Ross_T_Boss
Nouveau
Nouveau
Posts: 21
Joined: 08 Aug 2008 12:00

Post by Ross_T_Boss » 10 Sep 2012 4:09

I did have issues with NULL values & parameters - in v6.3 the parameter field should be left blank; in v7.0 you should enter null into the field or you'll get an error. This was with an Oracle stored proc though.

Post Reply