SQL Call question

[Joe Boyle]

purely guessing, how about trying syntax below from the update procedure -
the update TX is automatically committed on completion of the update
procedure,

		sql in cwdb transaction update &
		call CreateJob (JNAME)	

Also, what is the called stored procedure actually doing ? 

Is it writing / affecting any tables declared in your screen ? It could be
that the tables open in your screen are blocking your SP from executing via
page locks.

Regards, Joe.


-----Original Message-----
From: Syed Shahul Hameed Mustaffa [mailto:sshahulgm at gmail.com] 
Sent: 15 August 2005 14:29
To: powerh-l at lists.sowder.com
Cc: Joe Boyle
Subject: SQL Call 
Dear Gurus,

Quick Intro: I'm migrating PH/MPEIX application to AXIANT-4GL/WINDOWS.

I have the following call to SQL Server 2000 stored procedure.

		sql in cwdb notransaction  &
		call CreateJob (JNAME)	 		
The CreateJob procedure actually creates a SQL server JOB.
I tried to execute this statement from UPDATE procedure and POSTUPDATE
procedure. Axiant does not close the cursor it creates and the sql
server process does not release the locks. To close the cursor, I have
to quit the application.

I refered to the SQL CLOSE statement. But it requires a name. My
command does not have a name for the cluster.
Pls. let me know if there is a way to solve this problem.

The 'CreateJob' stored procedure code is as follows.

CREATE PROCEDURE CreateJob @JobName VARCHAR(20)

AS

DECLARE @c_CmdLine varchar(1000)
SELECT @c_CmdLine = 'cmd /c ' + LTRIM(RTRIM(EXEFILENAME)) + ' dict=' +
LTRIM(RTRIM(PHDICTFILENAME))
+ ' auto=' + LTRIM(RTRIM(PROGFILENAME)) + ' < ' + LTRIM(RTRIM(PARMFILENAME))
FROM SCHEDULED_JOBS
WHERE JOBNAME = @JobName
--PRINT @c_CmdLine

begin tran
EXEC msdb..sp_add_job  @job_name =  @JobName, @enabled = 1,
@description = 'Inventory of Breeding Bulls'

exec MSDB..sp_add_jobstep @job_name = @JobName, @step_id = 1, 
@step_name =  'CWQZ912N',
@subsystem = 'CMDEXEC', 
@command = @c_CmdLine

commit tran

Best Regards,
SYED