Hi everyone,

I'm wondering how I can use Oracle Transparent Gateway to run a script on a SQL Server that I'm connected to. Currently I'm connected to the SQL Server via the gateway and can run normal Select and Insert SQLs.

I plan to run a script that will generate a 'Scheduled Job' in the SQL Server. I intend to run this script from SQL Plus. The script (i've truncated it as its too long is as below)

*************SQL Server Script *****
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'MyJob')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))

******End truncated script ****

Does anyone know how I can do it? Or is there any other better way do the process?


Thanks a lot !!!

Derek W