It is currently 24 Apr 2019 6:36


Post new topic  Reply to topic  [ 3 posts ] 
Author Message
PostPosted: 13 Apr 2012 5:13 
Offline
Nouveau
Nouveau
User avatar

Joined: 20 Jan 2011 12:00
Posts: 1
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"


Top
   
 Post subject:
PostPosted: 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.


Top
   
 Post subject:
PostPosted: 10 Sep 2012 4:09 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Aug 2008 12:00
Posts: 21
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.


Top
   
Display posts from previous:  Sort by  
Post new topic  Reply to topic  [ 3 posts ] 

All times are UTC+02:00


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Limited
The site created by Franck YOT. Images, logo, content and trademarks about Control-M products are property of BMC Software.
All the comments are property of their posters. Images, logo, content and design are © copyright by Scheduler-Usage.com. All Rights Reserved