Running a job via osql?

Hello All,
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?

Thanks in advance,
TonyYes, just create a bat file with the osql commands in it and execute the bat file..I would imagine you would have to have osql on the oracle box...so if it's unix, then I'm not sure...|||Thanks Brad!|||ummmm...That's Brett

Or "x002548"

Or "HEY YOU"

Or "You look like a man who needs a drink"

Or "Coach" (As in hey coach..ummm...can I play forward? 13 voices at once)

Don't forget to redirect the output to a log so you can track what happened...that would be from a second bat file calling the one with the osql commands...

I like this approach, because it kind reminds me of my old JCL home

Weep no more, my lady,
Oh weep no more today!
We will sing one song for the old JCL home,
For the old JCL home far away.|||Brett,
My sincerest apologies!! That's what I get for multi-tasking & not paying attention when I type.

Our scheduling software has a windows client version that would allow us to excute the .bat file on the SQL Server box.

In order to call the job, do I just refer to the Job Name in the /Q [query] portion of the osql "statement"?

Server- PROD01
User- Tony
Password- Test
Job Name- Get1|||See the attachments

The txt file should be the bat, but it didn't let me upload a bat file|||ok, it only allows 1 upload per post..

This is a sample sql that osql executes

Well maybe it would allow more than 1, it didn't like the *.sql extension which is what it was|||Very Cool!! Once again, many thanks. I know that yours was executing a .sql script. How do I get it to recognize a pre-exisiting SQL Server Job?|||Place something like this in the *.sql file

EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'|||The job and subsequently the script that calls the job need to have an error trapping mechanism. With current syntax of both batch file and SQL script Control-M will report SUCCESS even if OSQL part fails. At a minimum add -b to OSQL to force it to fail if error occurs.|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL

EDIT:

-b

Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.|||The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.|||Good point.

But I still would interogate the log anyway...but thanks for the info...

Anything with a severity error of less than 10 though still returns a zero...

EXINFO 1 Informational, nonerror.
EXUSER 2 User error.
EXNONFATAL 3 Nonfatal error.
EXCONVERSION 4 Error in DB-Library data conversion.
5 The server has returned an error flag.
EXTIME 6 Time-out period exceeded while waiting for
a response from the server; the DBPROCESS is still alive.
EXPROGRAM 7 Coding error in user program.
EXRESOURCE 8 Running out of resources; the DBPROCESS may be dead.
EXCOMM 9 Failure in communication with server; the DBPROCESS is dead.
EXFATAL 10 Fatal error; the DBPROCESS is dead.
EXCONSISTENCY 11 Internal software error; notify your primary support provider|||Sample fool-proof batch for Control-M:

OSQL...
if errorlevel 1 goto ErrorHandler
goto end

:ErrorHandler
echo OSQL ended with code %ERRORLEVEL%!
exit /B 1

:end|||Fine, but what about the severity errors below 10?

You're not suggesting that they completed. And since the return code will be 0, you still have to check.

No?|||Sample fool-proof SQL script (pseudo):

<sql_statement>
if @.@.error != 0 begin
raiserror ('The last statement failed (<WHAT STATEMENT? BE SPECIFIC!>)', 15, 1)
return
end
...|||are you sure it's not brad?

run time error: Encountered the symbol "S" when expecting one of the following:

ocasionally, in the testing environment we receive following error:
(we using Oracle 9i), error is reported by the EXCEPTION
WHEN OTHERS THEN (see below)

upon logging ad posting status for 250
Normal -6550 ORA-06550: line 4, column 308:
PLS-00103:
Encountered the symbol "S" when expecting one of the following:
. ( ) , * @. % & | = - + < / > at in is mod not range rem => .. <>
or != or ~= >= <= <> and or like between ||
************************************************** *******

I am providing a complete PL/SQL, this error only occurs at the run-time

PROCEDURE logPostingStatus(p_session_id IN NUMBER,
p_person_id IN NUMBER,
p_ad_id IN NUMBER,
p_production IN VARCHAR2 DEFAULT 'y',
p_status IN VARCHAR2 DEFAULT 'started',
p_files IN VARCHAR2 DEFAULT null,
p_note IN VARCHAR2 DEFAULT null,
p_site_profile_id IN NUMBER DEFAULT -1,
p_site_id IN NUMBER DEFAULT -1)
AS

v_posted_before DATE;
v_change_type VARCHAR2(32);
v_job_num NUMBER;

v_files_passed VARCHAR2(2048) DEFAULT replace(ltrim(rtrim(p_files)), ',', '<br>');
v_files_just_pushed VARCHAR2(2048);
v_files_pushed_before VARCHAR2(2048);
v_files_to_purge VARCHAR2(2048);
v_filename VARCHAR2(256);
v_status VARCHAR2(16) DEFAULT p_status; -- the new status
v_pos NUMBER;
v_phase VARCHAR2(16);

BEGIN

UPDATE ad_post_status
SET end_time = SYSDATE,
-- update the status in the posting status, repeating status => ended
status = decode(status, 'aborted', 'aborted',
lower(p_status), 'ended',
lower(p_status)),
-- append the files pushed by this thread.
files_pushed = decode(files_pushed, null, v_files_passed,
files_pushed || '<br>' || v_files_passed),
note = decode(note, null, p_note, note || '. ' || p_note)
WHERE session_id = p_session_id and ad_id = p_ad_id and production = p_production
RETURNING files_pushed, status INTO v_files_just_pushed, v_status;
-- files_pushed now holds the files pushed by the both threads
-- v_status is the new status

IF (SQL%ROWCOUNT = 0) THEN

-- no log exists for the job yet: we are starting a new job (status will be
-- 'started', the column default)
INSERT INTO ad_post_status (session_id, ad_id, production, note, initiated_by)
VALUES(p_session_id, p_ad_id, p_production, p_note, p_person_id);

END IF;

-- log the session ID in the site/site profile if it is site/site profile roll-out
IF (p_site_profile_id > 0) THEN

UPDATE site_profile
SET roll_session = p_session_id
WHERE site_profile_id = p_site_profile_id and roll_session is null;

ELSIF (p_site_id > 0) THEN

UPDATE site
SET roll_session = p_session_id
WHERE site_id = p_site_id and roll_session is null;

END IF;

-- if two threads complete successfully ...
IF (v_status = 'ended') THEN

-- update posting time
IF (p_production <> 'y') THEN

UPDATE ad SET posted_preview = SYSDATE where ad_id = p_ad_id;

ELSE

SELECT posted_production, change_type
INTO v_posted_before, v_change_type
FROM ad
WHERE ad_id = p_ad_id;

-- ad descriptor_status: off (non-deleted) -> on
UPDATE ad SET descriptor_status = 'on',
dtor_status_modified_by = p_person_id,
dtor_status_modified_reason = 'ad approved and files pushed sucessfully'
WHERE ad_id = p_ad_id
AND status not in ('deleted','failed')
AND descriptor_status = 'off';

-- ad status pending -> live
UPDATE ad SET status = 'live',
status_modified_by = p_person_id,
status_modified_reason = 'ad approved and files pushed successfully'
WHERE ad_id = p_ad_id AND status = 'pending';

UPDATE ad SET posted_production = SYSDATE,
change_type = null
WHERE ad_id = p_ad_id;

-- decrement the live ad count as we are rolling out site profile changes
IF (p_site_profile_id > 0) THEN

UPDATE site_profile
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_profile_id = p_site_profile_id;

ELSIF (p_site_id > 0) THEN

UPDATE site
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_id = p_site_id;

END IF;

-- send traffic reminder email in 24 hours if the ad was approved not for the first time
-- (and not trafficed in 24 hours)
IF (v_posted_before is not null and instr(v_change_type, 'T') > 0) THEN

DBMS_JOB.SUBMIT(v_job_num,
'notify.trafficReminder(' || p_ad_id || ',' || p_person_id || ');',
SYSDATE + 1);

END IF;

/*
* handle akamai file purge: store the files to be pushed;
* detect if there is any file that needs to be purged,
* if yes, send reminder email
*/

v_files_to_purge := '';

IF (v_files_just_pushed || '' = '') THEN
UPDATE ad_post_status
SET files_pushed = '(no file to push)'
WHERE session_id = p_session_id and ad_id = p_ad_id and production = p_production;
RETURN;
END IF;

BEGIN
-- files just pushed
SELECT value INTO v_files_pushed_before
FROM ad_prop
WHERE ad_id = p_ad_id AND name = 'files_pushed';

-- find files to be purged, e.g., files that are passed from p_files and already
-- in the ad_prop 'files_pushed'
LOOP
-- parse the filenames from files_pushed
EXIT WHEN (length(v_files_just_pushed || 'x') = 1);
v_pos := instr( v_files_just_pushed, '<br>' );
IF (nvl(v_pos, 0) = 0) THEN
v_pos := length(v_files_just_pushed) + 1;
END IF;

v_filename := ltrim(rtrim(substr(v_files_just_pushed,1,v_pos-1)));

IF (length(v_filename) > 0) THEN
IF (instr(v_files_pushed_before, v_filename) <= 0) THEN
UPDATE ad_prop
SET value = value || '<br>' || v_filename
WHERE ad_id = p_ad_id
AND name = 'files_pushed';
ELSE
-- the string @.@. is used as a place-holder for the site tag file path
-- the string !! is used as a place-holder for the ad file path
-- the string ## is used for the creative (generic) file path
IF (instr(v_filename, '_sitetag.txt') > 0) THEN
v_files_to_purge := v_files_to_purge || '..@.@.' || v_filename;
ELSIF (instr(v_filename, 'SiteId_') = 1) THEN
v_files_to_purge := v_files_to_purge || '..!!' || v_filename;
ELSE
v_files_to_purge := v_files_to_purge || '..##' || v_filename;
END IF;
END IF;
END IF;
-- jump over the delimiter <br>
v_files_just_pushed := substr( v_files_just_pushed, v_pos+4 );

END LOOP;

-- notify of files to be purged
IF (v_files_to_purge || 'x' <> 'x') THEN
notify.filesToPurge(p_ad_id, v_files_to_purge, p_person_id, p_site_profile_id, p_site_id);
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no file has been pushed before: insert the ad_prop
INSERT INTO ad_prop (ad_id, type, name, value)
VALUES(p_ad_id, '-', 'files_pushed', v_files_just_pushed);

END;

END IF;

ELSIF (v_status = 'aborted') THEN

-- determine phase to be written into error notification
IF (p_production = 'y') THEN
v_phase := 'production';
ELSE
v_phase := 'live preview';
END IF;

-- decrement the live ad count as we are rolling out site profile changes
IF (p_site_profile_id > 0) THEN

UPDATE site_profile
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_profile_id = p_site_profile_id
AND roll_ad_count > 0;

ELSIF (p_site_id > 0) THEN

UPDATE site
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_id = p_site_id
AND roll_ad_count > 0;

END IF;

notify.postError(p_ad_id, p_person_id, p_site_profile_id, p_site_id, v_phase, p_note);

END IF;

EXCEPTION
WHEN OTHERS THEN
library.logError('upon logging ad posting status for ' || to_char(p_ad_id));
--raise_application_error( -20002, 'logging ad posting status failed');

END logPostingStatus;I can't see where the error is coming from, but try this: comment out (or remove) all the exception handling:

--EXCEPTION
--WHEN OTHERS THEN
--library.logError('upon logging ad posting status for ' || to_char(p_ad_id));

Then the error message will inform you of precisely which line of code the error occured on.

One error I did spot (but is irrelevant to your problem):

IF (v_files_just_pushed || '' = '') THEN

This will never be true, because in Oracle '' is equivalent to NULL, and NULL = NULL is never true. Your test should be:

IF v_files_just_pushed IS NULL THENsql

run time error

I create an application with Visual Basic 6 and Crystal Report 10
with IDE Environment.While Developing the application it works fine
in my system.but after i made the application
setup, and when while I am deploying (install)the application
in the same system (widows xp) it show run time error. i ignore the
error and continue the installiation till the end.
and then when i run the application it works ok but when i use print
command it show error(crviewer file not found). and the application
closed.so what is the problem here.please help
to solve it.

thanksEither you have not included the crviewer.dll file in your setup package or there might have been a problem while copying/registering the file. Try registering the File manually using regsvr32.

Run Single Step In Job on SQL Server 2000

I am working in an environment where application devs don't have the
ability to run SQL Server Jobs that are setup already in production.
Our jobs are multi-step and have a lot of steps. What I am looking for
is a way for the operators who have privileges in production but know
nothing about SQL Server to run a single step inside of a job without
triggering other steps. SQL Server 2000 allows for the ability to
start a job at a certain step but what I want to happen is for only
that particular step to run. Any thoughts?
SpencerBecause of the seperation of duties in the organization I am also
looking at creating an "On Demand" job that application devs will have
privileges to update a particular .sql file and then put in a runsheet
to have operations kick off the on demand job. What I do want the on
demand job to do however is do some logging such as who kicked the job
off, what sql ended up being executed, rows affected, etc. Maybe an
email as well. I know there have got to be lots of environments doing
something similar.
Spencer|||On 11 May 2006 18:21:08 -0700, "stabbert" <spencer@.tabbert.net> wrote:
>I am working in an environment where application devs don't have the
>ability to run SQL Server Jobs that are setup already in production.
>Our jobs are multi-step and have a lot of steps. What I am looking for
>is a way for the operators who have privileges in production but know
>nothing about SQL Server to run a single step inside of a job without
>triggering other steps. SQL Server 2000 allows for the ability to
>start a job at a certain step but what I want to happen is for only
>that particular step to run. Any thoughts?
>Spencer
It's possible to create code that parses out the job step and creates
a new job that you can then run immediately, but short of something
zippy like this, I don't know how else you could do it.
Josh

Run Single Step In Job on SQL Server 2000

I am working in an environment where application devs don't have the
ability to run SQL Server Jobs that are setup already in production.
Our jobs are multi-step and have a lot of steps. What I am looking for
is a way for the operators who have privileges in production but know
nothing about SQL Server to run a single step inside of a job without
triggering other steps. SQL Server 2000 allows for the ability to
start a job at a certain step but what I want to happen is for only
that particular step to run. Any thoughts?
SpencerBecause of the seperation of duties in the organization I am also
looking at creating an "On Demand" job that application devs will have
privileges to update a particular .sql file and then put in a runsheet
to have operations kick off the on demand job. What I do want the on
demand job to do however is do some logging such as who kicked the job
off, what sql ended up being executed, rows affected, etc. Maybe an
email as well. I know there have got to be lots of environments doing
something similar.
Spencer|||On 11 May 2006 18:21:08 -0700, "stabbert" <spencer@.tabbert.net> wrote:
>I am working in an environment where application devs don't have the
>ability to run SQL Server Jobs that are setup already in production.
>Our jobs are multi-step and have a lot of steps. What I am looking for
>is a way for the operators who have privileges in production but know
>nothing about SQL Server to run a single step inside of a job without
>triggering other steps. SQL Server 2000 allows for the ability to
>start a job at a certain step but what I want to happen is for only
>that particular step to run. Any thoughts?
>Spencer
It's possible to create code that parses out the job step and creates
a new job that you can then run immediately, but short of something
zippy like this, I don't know how else you could do it.
Josh