Running a script frm OTN

[dwtk9]

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

[skhanal]

I have not used Transparent Gateway, but if you are running Oracle on a NT box, you could save the script in a file, then call osql.exe to run a script in SQL Server.

You must have osql.exe (comes with sql client) in your oracle server.

Check SQL Server books online for syntax.

Here is the minimum you need.


osql -U login_id -P password -S server_name[\instance_name]] [-d db_name]
-Q "query"