-
Running a script frm OTN
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
-
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"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|