SQL Call question

[Joe Boyle]

The suggestion below might work as a useful test,

Create a screen comprised of the few lines below,

Proc init
Begin

Let JNAME = CreateJob
sql in cwdb transaction update &
call CreateJob (JNAME)	

Return

end

Then execute it from a subscreen button - being sure to add 'mode E' ( I
think other modes use a query TX by default) which should run in the update
TX.

Regards, Joe.



-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Joe Boyle
Sent: 15 August 2005 14:56
To: 'Syed Shahul Hameed Mustaffa'; powerh-l at lists.sowder.com
Subject: RE: SQL Call question

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

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.