SQL Call question

[Syed Shahul Hameed Mustaffa]

Dear Joe,
A very big thanks to u.
It worked.

The procedure is actually creating SQL Server jobs using parameters
from a table call SCHEDULED_JOBS (user table).

When I executed the procedure from AXIANT, I could not see it in SQL
Enterprise Manager (as it was not committed). Further SQL EM was
hanging because the database table (from MSDB system database) it was
trying to read was locked by Axiant. SQL EM stays hung until I quit
the Axiant application.

Best Regards,
syed.

On 8/15/05, Joe Boyle <atla38 at dsl.pipex.com> wrote:
> 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
> 
>