Running an SSIS package as a job that connects to a DB

I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.

There are no OnError lines in the logging.

Anyone have any Ideas or tricks to try.

Hi, have you seen this article?

http://support.microsoft.com/?kbid=918760

Your issue does still sound 'permission' related. Perhaps not an issue with a SQL connecton but maybe access to the file share where the files are located. One thing the article points out is how to get some logging data back to the console\file via executing the SSIS package using the command exec sub system (in agent) rather than the SSIS subsystem.

Hope that helps

|||

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

|||Anything new about this? I got the same Problem and in fact have completely lost a whole day because of this completele useless and meaningless type of errorhandling. >:(|||

Is there any resolution for this?

Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:09:19 PM. The return value was unknown. The process exit code was -532459699. The step failed.

|||

HeatherS2006 wrote:

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

But it's not the account that Agent runs under, it's the account that SQL Server runs under that you need to look at.|||

any resolution?

It's been like this since August?

|||

The account is a domain account. It is part of the Local Administrator Group.

It belongs to the BUILTIN/Administrators in SQL.

Did I overlook anything?

sql

Running an SSIS package as a job that connects to a DB

I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.

There are no OnError lines in the logging.

Anyone have any Ideas or tricks to try.

Hi, have you seen this article?

http://support.microsoft.com/?kbid=918760

Your issue does still sound 'permission' related. Perhaps not an issue with a SQL connecton but maybe access to the file share where the files are located. One thing the article points out is how to get some logging data back to the console\file via executing the SSIS package using the command exec sub system (in agent) rather than the SSIS subsystem.

Hope that helps

|||

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

|||Anything new about this? I got the same Problem and in fact have completely lost a whole day because of this completele useless and meaningless type of errorhandling. >:(|||

Is there any resolution for this?

Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:09:19 PM. The return value was unknown. The process exit code was -532459699. The step failed.

|||

HeatherS2006 wrote:

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

But it's not the account that Agent runs under, it's the account that SQL Server runs under that you need to look at.|||

any resolution?

It's been like this since August?

|||

The account is a domain account. It is part of the Local Administrator Group.

It belongs to the BUILTIN/Administrators in SQL.

Did I overlook anything?

Runnable jobs

Hi,
Jobs panel of SQL server Agent has columns which show if job is Enabled,
Runnable, Scheduled.
How to disable Runnable value?
Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
such column.
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola Milicif you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||if you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Runnable jobs

Hi,
Jobs panel of SQL server Agent has columns which show if job is Enabled,
Runnable, Scheduled.
How to disable Runnable value?
Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
such column.
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola Milicif you delete all child job steps from the job, the "runnable" will show
"NO". oh, you cannot do it from within enterprise manager.
here is a quick demo. run the query below to create an empty job then check
EM for the runnable status.
exec msdb.dbo.sp_add_job @.job_name = N'test'
-oj
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:uiHdBpClGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>|||Nikola Milic wrote:
> Hi,
> Jobs panel of SQL server Agent has columns which show if job is Enabled,
> Runnable, Scheduled.
> How to disable Runnable value?
> Job interface doesn't support such option, nor table msdb.dbo.sysjobs has
> such column.
>
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
If you right click on the job on select properties you have an "Enabled"
check box on the first pane. By unclicking this you disable the job.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

runaway jobs

Hi,
Some of my developers have scheduled jobs which run for hours...how can i get an alert on job that have been running for more than 30 minutes.Use SP_HELP_JOB repeatedly to get the execution status.

Also make sure to identify why those jobs are taking too long to return the status/completion.|||msdb..sysjobhistory tables has fields that could help you:

run_status (int) - Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

run_date (int) - Date the job or step started execution. For an In Progress history, this is the date/time the history was written.|||MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.|||knock the developers heads together ? maybe then they will be a bit more thoughtfull about the performace of their jobs ...|||NetIQ has a piece of software called DiagnosticsManager for SQL that will monitor problem queries.|||Hi I am writing a script to check quickly jobs which failed or running endlessly.

I am checking the sysjobhistory run_status field

run_status (int) - Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

After lots of tests, I never saw status 4 when a job is running so I cannot perform my task correctly...|||Originally posted by Satya
MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.

Wierd !!!

I did not get this one ...Running this much code instead of one single query is recommended by MS

CREATE PROCEDURE sp_help_job
-- Individual job parameters
@.job_id UNIQUEIDENTIFIER = NULL, -- If provided should NOT also provide job_name
@.job_name sysname = NULL, -- If provided should NOT also provide job_id
@.job_aspect VARCHAR(9) = NULL, -- JOB, STEPS, SCEDULES, TARGETS or ALL
-- Job set parameters
@.job_type VARCHAR(12) = NULL, -- LOCAL or MULTI-SERVER
@.owner_login_name sysname = NULL,
@.subsystem NVARCHAR(40) = NULL,
@.category_name sysname = NULL,
@.enabled TINYINT = NULL,
@.execution_status INT = NULL, -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions
@.date_comparator CHAR(1) = NULL, -- >, < or =
@.date_created DATETIME = NULL,
@.date_last_modified DATETIME = NULL,
@.description NVARCHAR(512) = NULL -- We do a LIKE on this so it can include wildcards
AS
BEGIN
DECLARE @.retval INT
DECLARE @.category_id INT
DECLARE @.job_id_as_char VARCHAR(36)
DECLARE @.res_valid_range NVARCHAR(200)

SET NOCOUNT ON

-- Remove any leading/trailing spaces from parameters (except @.owner_login_name)
SELECT @.job_name = LTRIM(RTRIM(@.job_name))
SELECT @.job_aspect = LTRIM(RTRIM(@.job_aspect))
SELECT @.job_type = LTRIM(RTRIM(@.job_type))
SELECT @.subsystem = LTRIM(RTRIM(@.subsystem))
SELECT @.category_name = LTRIM(RTRIM(@.category_name))
SELECT @.description = LTRIM(RTRIM(@.description))

-- Turn [nullable] empty string parameters into NULLs
IF (@.job_name = N'') SELECT @.job_name = NULL
IF (@.job_aspect = '') SELECT @.job_aspect = NULL
IF (@.job_type = '') SELECT @.job_type = NULL
IF (@.owner_login_name = N'') SELECT @.owner_login_name = NULL
IF (@.subsystem = N'') SELECT @.subsystem = NULL
IF (@.category_name = N'') SELECT @.category_name = NULL
IF (@.description = N'') SELECT @.description = NULL

IF ((@.job_id IS NOT NULL) OR (@.job_name IS NOT NULL))
BEGIN
EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT
IF (@.retval <> 0)
RETURN(1) -- Failure
END

SELECT @.job_id_as_char = CONVERT(VARCHAR(36), @.job_id)

-- If the user provided a job name or id but no aspect, default to ALL
IF ((@.job_name IS NOT NULL) OR (@.job_id IS NOT NULL)) AND (@.job_aspect IS NULL)
SELECT @.job_aspect = 'ALL'

-- The caller must supply EITHER job name (or job id) and aspect OR one-or-more of the set
-- parameters OR no parameters at all
IF (((@.job_name IS NOT NULL) OR (@.job_id IS NOT NULL))
AND ((@.job_aspect IS NULL) OR
(@.job_type IS NOT NULL) OR
(@.owner_login_name IS NOT NULL) OR
(@.subsystem IS NOT NULL) OR
(@.category_name IS NOT NULL) OR
(@.enabled IS NOT NULL) OR
(@.date_comparator IS NOT NULL) OR
(@.date_created IS NOT NULL) OR
(@.date_last_modified IS NOT NULL)))
OR
((@.job_name IS NULL) AND (@.job_id IS NULL) AND (@.job_aspect IS NOT NULL))
BEGIN
RAISERROR(14280, -1, -1)
RETURN(1) -- Failure
END

IF (@.job_id IS NOT NULL)
BEGIN
-- Individual job...

-- Check job aspect
SELECT @.job_aspect = UPPER(@.job_aspect)
IF (@.job_aspect NOT IN ('JOB', 'STEPS', 'SCHEDULES', 'TARGETS', 'ALL'))
BEGIN
RAISERROR(14266, -1, -1, '@.job_aspect', 'JOB, STEPS, SCHEDULES, TARGETS, ALL')
RETURN(1) -- Failure
END

-- Generate results set...

IF (@.job_aspect IN ('JOB', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
RAISERROR(14213, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14213)) / 2)
END
EXECUTE sp_get_composite_job_info @.job_id,
@.job_type,
@.owner_login_name,
@.subsystem,
@.category_id,
@.enabled,
@.execution_status,
@.date_comparator,
@.date_created,
@.date_last_modified,
@.description
END

IF (@.job_aspect IN ('STEPS', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14214, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14214)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobstep @.job_id = ''' + @.job_id_as_char + ''', @.suffix = 1')
END

IF (@.job_aspect IN ('SCHEDULES', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14215, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14215)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobschedule @.job_id = ''' + @.job_id_as_char + '''')
END

IF (@.job_aspect IN ('TARGETS', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14216, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14216)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobserver @.job_id = ''' + @.job_id_as_char + ''', @.show_last_run_details = 1')
END
END
ELSE
BEGIN
-- Set of jobs...

-- Check job type
IF (@.job_type IS NOT NULL)
BEGIN
SELECT @.job_type = UPPER(@.job_type)
IF (@.job_type NOT IN ('LOCAL', 'MULTI-SERVER'))
BEGIN
RAISERROR(14266, -1, -1, '@.job_type', 'LOCAL, MULTI-SERVER')
RETURN(1) -- Failure
END
END

-- Check owner
IF (@.owner_login_name IS NOT NULL)
BEGIN
IF (SUSER_SID(@.owner_login_name) IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@.owner_login_name', @.owner_login_name)
RETURN(1) -- Failure
END
END

-- Check subsystem
IF (@.subsystem IS NOT NULL)
BEGIN
EXECUTE @.retval = sp_verify_subsystem @.subsystem
IF (@.retval <> 0)
RETURN(1) -- Failure
END

-- Check job category
IF (@.category_name IS NOT NULL)
BEGIN
SELECT @.category_id = category_id
FROM msdb.dbo.syscategories
WHERE (category_class = 1) -- Job
AND (name = @.category_name)
IF (@.category_id IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@.category_name', @.category_name)
RETURN(1) -- Failure
END
END

-- Check enabled state
IF (@.enabled IS NOT NULL) AND (@.enabled NOT IN (0, 1))
BEGIN
RAISERROR(14266, -1, -1, '@.enabled', '0, 1')
RETURN(1) -- Failure
END

-- Check current execution status
IF (@.execution_status IS NOT NULL)
BEGIN
IF (@.execution_status NOT IN (0, 1, 2, 3, 4, 5, 7))
BEGIN
SELECT @.res_valid_range = FORMATMESSAGE(14204)
RAISERROR(14266, -1, -1, '@.execution_status', @.res_valid_range)
RETURN(1) -- Failure
END
END

-- If a date comparator is supplied, we must have either a date-created or date-last-modified
IF ((@.date_comparator IS NOT NULL) AND (@.date_created IS NOT NULL) AND (@.date_last_modified IS NOT NULL)) OR
((@.date_comparator IS NULL) AND ((@.date_created IS NOT NULL) OR (@.date_last_modified IS NOT NULL)))
BEGIN
RAISERROR(14282, -1, -1)
RETURN(1) -- Failure
END

-- Check dates / comparator
IF (@.date_comparator IS NOT NULL) AND (@.date_comparator NOT IN ('=', '<', '>'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_comparator', '=, >, <')
RETURN(1) -- Failure
END
IF (@.date_created IS NOT NULL) AND
((@.date_created < '1 Jan 1990 12:00:00am') OR (@.date_created > '31 Dec 9999 11:59:59pm'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_created', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
RETURN(1) -- Failure
END
IF (@.date_last_modified IS NOT NULL) AND
((@.date_last_modified < '1 Jan 1990 12:00am') OR (@.date_last_modified > 'Dec 31 9999 11:59:59pm'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_last_modified', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
RETURN(1) -- Failure
END

-- Generate results set...
EXECUTE sp_get_composite_job_info @.job_id,
@.job_type,
@.owner_login_name,
@.subsystem,
@.category_id,
@.enabled,
@.execution_status,
@.date_comparator,
@.date_created,
@.date_last_modified,
@.description
END

RETURN(0) -- Success
END|||Hi !

In effect this store proc returns the fields I need but I wonder how can I perform a select from the result of this store proc ?

Shourl I redirect the result of the stored proc to a temporary table and if yes what is the redirection instruction ?

Could you help please ?|||INSERT INTO myTable99 EXEC sp_whatever..

my favorite line of MS code is in sp_help

-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @.objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @.objid = xusertype from systypes where name = @.objname

-- IF NOT IN SYSTYPES, GIVE UP
if @.objid is null
begin
select @.dbname=db_name()
raiserror(15009,-1,-1,@.objname,@.dbname)
return(1)
end|||Thanks ! I have all I need to check jobs still running !|||.. More or less :-(

insert into ##sp_help_job_table exec sp_help_job

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.|||Originally posted by Satya
MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.

Since u said that, I was wondering why its discouraged? thx|||I am also discouraged of using system store procs...
No issue to my problem...|||"MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information."

Screw M$.

If they had their way we'd all be using wizards and nobody would have a clue what was going on in the background. "Pay no attention to the little man behind the curtain!"

Reference the system tables and you'll learn something about how Microsoft works. If a newly released version requires you to change your code, then learn how the new system works. I don't think that's a bad thing.

blindman|||ok...I can't find sp_help_job...where does it live?

never mind

I thought it was because you couldn't use temp tables with an EXEC...

but

CREATE TABLE wrk_sp_help_Job(
job_id varchar(255)
, job_name sysname
, job_aspect VARCHAR(9)
, job_type VARCHAR(12)
, owner_login_name sysname
, subsystem NVARCHAR(40)
, category_name sysname
, enabled TINYINT
, execution_status INT
, date_comparator CHAR(1)
, date_created DATETIME
, date_last_modified DATETIME
, [description] NVARCHAR(512)
)
GO

INSERT INTO wrk_sp_help_job EXEC msdb..sp_help_job

DROP TABLE wrk_sp_help_job

oesn't work either...it seems like since the sproc is calling other sprocs (nested) that it won't work...

gotta be a way...|||I remember looking into this little rat-hole before. I got as far as an extended stored procedure called xp_sqlagent_enum_jobs. The current run status is returned by this procedure, but you have to supply two parameters (at least on SP3). So the full syntax is:

master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

This returns all of the jobs on my test server, but I do not have anything owned by regular users. I always yank the guest user out of MSDB, before letting any of the masses on to the box ;-).

Brett, you should be able to drop this into a temp table, then join that temp table back to sysjobs for readable names and the like.|||Wow...it's a dll

and it store the dates and times separately as char data...

You go M$, you go...

maybe I can sell donughts...sql

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David
>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run Batch Files with SQL Server Agent

Hi,
I am trying to run a batch file using the SQL Server Agent
(I have scheduled a job). I have chosen the step type to
be 'Operating System Command (CmdExec)'. But I am not sure
how to code the actual command.
The batch file is located at: e:\sqlbackups\delete.bat.
How do specify the Agent to call this batch file?
Thanks,
DeeJayHi,
In SQL Agent Jobs -- Step option, after selecting 'Operating System Command
(CmdExec)' , In the command window you can type
e:\sqlbackups\delete.bat
(This will call the batch file delete.bat based on the schedule interval)
Thanks
Hari
MCDBA
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:14f3201c3fa33$599d4120$a001280a@.phx
.gbl...
> Hi,
> I am trying to run a batch file using the SQL Server Agent
> (I have scheduled a job). I have chosen the step type to
> be 'Operating System Command (CmdExec)'. But I am not sure
> how to code the actual command.
> The batch file is located at: e:\sqlbackups\delete.bat.
> How do specify the Agent to call this batch file?
> Thanks,
> DeeJay
>|||Hi
Thanks, but that's exactly how I have been trying to run
it, but it does not work.
I am running the job with an administrative account that
has the required permissions.
I have tested the script by running is manually on the
server and it works fine.
I wonder what gives.
DeeJay
>--Original Message--
>Hi,
>In SQL Agent Jobs -- Step option, after
selecting 'Operating System Command
>(CmdExec)' , In the command window you can type
>e:\sqlbackups\delete.bat
>(This will call the batch file delete.bat based on the
schedule interval)
>Thanks
>Hari
>MCDBA
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:14f3201c3fa33$599d4120$a001280a@.phx
.gbl...
Agent
sure
>
>.
>|||Do you get any error message, what happens when you try to
run the job ?
How long the batch file takes to run ? We had an issue
where sql agent timed out if the batch file took more than
30 minutes to run. It happened only once and we ran the
file manually from command prompt. You can also try to run
it as a tsql command using Exec
master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
hth.

>--Original Message--
>Hi
>Thanks, but that's exactly how I have been trying to run
>it, but it does not work.
>I am running the job with an administrative account that
>has the required permissions.
>I have tested the script by running is manually on the
>server and it works fine.
>I wonder what gives.
>DeeJay
>selecting 'Operating System Command
>schedule interval)
>Agent
to
>sure
>.
>|||That's the weird part...I do not get any errors. Actually
according to the agent the job completes successfully, but
the files are not deleted.
I tried your suggestion and it did not work. But this time
I do get an error:
The process could not be created for step 1 of job
0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped I/O
operation is in progress). The step failed.
This is really confusing. I do not want to run the
manually every other day.
Thanks,
DeeJay

>--Original Message--
>Do you get any error message, what happens when you try
to
>run the job ?
>How long the batch file takes to run ? We had an issue
>where sql agent timed out if the batch file took more
than
>30 minutes to run. It happened only once and we ran the
>file manually from command prompt. You can also try to
run
>it as a tsql command using Exec
>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>hth.
>
>to
e:\sqlbackups\delete.bat.
>.
>|||For real time error info, try to execute xp_cmdshell from
query analyzer.
I wonder if it is a permission issue, if the job owner is
a sysadmin then then sql server will use service startup
account to execute xp_cmdshell, otherwise it will use a
proxy account. Does sql startup account has permissions to
delete those files ?
hth.

>--Original Message--
>That's the weird part...I do not get any errors. Actually
>according to the agent the job completes successfully,
but
>the files are not deleted.
>I tried your suggestion and it did not work. But this
time
>I do get an error:
>The process could not be created for step 1 of job
>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
I/O
>operation is in progress). The step failed.
>This is really confusing. I do not want to run the
>manually every other day.
>Thanks,
>DeeJay
>
>to
>than
>run
run
that
type
not
>e:\sqlbackups\delete.bat.
>.
>|||I tested running the job with me as the owner and I am a
local administrator on this server. I have also tried
using 'sa' and the service startup and nothing is working.
I have tried running the xp_cmdshell procedure in QA and
it also does not work...I just get the output as 'NULL'.
I wonder if it is ONLY using the proxy account. That
account may not have the required permissions.
I will check that.
DeeJay
>--Original Message--
>For real time error info, try to execute xp_cmdshell from
>query analyzer.
>I wonder if it is a permission issue, if the job owner is
>a sysadmin then then sql server will use service startup
>account to execute xp_cmdshell, otherwise it will use a
>proxy account. Does sql startup account has permissions
to
>delete those files ?
>hth.
>
Actually
>but
>time
>I/O
>run
>that
the
Server
>type
>not
>.
>|||Hi,
I forgot that SQL Server 2000 does not have this proxy
account on the local server. I did find a domain
account 'SQLServerAcctCmdExec' and I added this to the
local administrators account and it still did not work.
DeeJay
>--Original Message--
>I tested running the job with me as the owner and I am a
>local administrator on this server. I have also tried
>using 'sa' and the service startup and nothing is working.
>I have tried running the xp_cmdshell procedure in QA and
>it also does not work...I just get the output as 'NULL'.
>I wonder if it is ONLY using the proxy account. That
>account may not have the required permissions.
>I will check that.
>DeeJay
from
is
>to
>Actually
try
the
the
>the
>Server
>.
>|||Make a little change to the batch file and add folloiwng
line at different places:
@.echo In the batch file at line <#>
when you run the batch file in QA it should display these
messages in results window.
In Enterprise Manager, if you open properties of SQL
Server Agent, click on Job System, you will see option to
specify proxy account, use a local admin for it, and then
restart sql services.
hope this helps.

>--Original Message--
>Hi,
>I forgot that SQL Server 2000 does not have this proxy
>account on the local server. I did find a domain
>account 'SQLServerAcctCmdExec' and I added this to the
>local administrators account and it still did not work.
>DeeJay
working.
>from
>is
startup
>try
issue
>the
to
>the
message
>.
>|||I made the change and the script did not return all of the
expected lines. The scripts is doing a 'for' loop so that
could be the reason for not returning all of the lines.
I tried to enter the proxy account, but I am getting
errors:
'Error executing extended stored procedure: Specified User
can not login'.
I am connected to SQL Server with 'sa' and I tried the
local administrator account (will it be a problem if the
local administrator account has been renamed?) and I also
tried with my own domain ID (sysadmin on sql server) and
same results. I did not think I would need change the
account since 'only sysadmins' can run cmdshell jobs and I
have been trying to run the jobs/commands with sysadmin
rights.
Here is the actual script which works fine manually on the
server:
@.echo off
setlocal
:: *** Root path of the directory where the files are to
be deleted:
set
Root=E:\sqlbackups\archtlog\CopyArchdTlo
gBackups\kdirect
:: *** Number of files to keep:
set Keep=590
if not exist "%Root%" goto :err_Root
for /f "skip=%Keep% tokens=*" %%a in ('dir /b /a:-d /o:-
d "%Root%" 2^>NUL') do (
echo Deleting %%a ...
:: *** Remove the "echo" in the next line to 'arm' the
script:
del "%Root%\%%a"
)
goto leave
:err_Root
echo The directory "%Root%" does not exist.
:leave
This is really bugging me!
DeeJay

>--Original Message--
>Make a little change to the batch file and add folloiwng
>line at different places:
>@.echo In the batch file at line <#>
>when you run the batch file in QA it should display these
>messages in results window.
>In Enterprise Manager, if you open properties of SQL
>Server Agent, click on Job System, you will see option to
>specify proxy account, use a local admin for it, and then
>restart sql services.
>hope this helps.
>
a
>working.
and
as 'NULL'.
>startup
a
permissions
successfully,
Overlapped
>issue
more
>to
to
>message
am
>.
>

Run Batch Files with SQL Server Agent

Hi,
I am trying to run a batch file using the SQL Server Agent
(I have scheduled a job). I have chosen the step type to
be 'Operating System Command (CmdExec)'. But I am not sure
how to code the actual command.
The batch file is located at: e:\sqlbackups\delete.bat.
How do specify the Agent to call this batch file?
Thanks,
DeeJayHi,
In SQL Agent Jobs -- Step option, after selecting 'Operating System Command
(CmdExec)' , In the command window you can type
e:\sqlbackups\delete.bat
(This will call the batch file delete.bat based on the schedule interval)
Thanks
Hari
MCDBA
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
> Hi,
> I am trying to run a batch file using the SQL Server Agent
> (I have scheduled a job). I have chosen the step type to
> be 'Operating System Command (CmdExec)'. But I am not sure
> how to code the actual command.
> The batch file is located at: e:\sqlbackups\delete.bat.
> How do specify the Agent to call this batch file?
> Thanks,
> DeeJay
>|||Hi
Thanks, but that's exactly how I have been trying to run
it, but it does not work.
I am running the job with an administrative account that
has the required permissions.
I have tested the script by running is manually on the
server and it works fine.
I wonder what gives.
DeeJay
>--Original Message--
>Hi,
>In SQL Agent Jobs -- Step option, after
selecting 'Operating System Command
>(CmdExec)' , In the command window you can type
>e:\sqlbackups\delete.bat
>(This will call the batch file delete.bat based on the
schedule interval)
>Thanks
>Hari
>MCDBA
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>> Hi,
>> I am trying to run a batch file using the SQL Server
Agent
>> (I have scheduled a job). I have chosen the step type to
>> be 'Operating System Command (CmdExec)'. But I am not
sure
>> how to code the actual command.
>> The batch file is located at: e:\sqlbackups\delete.bat.
>> How do specify the Agent to call this batch file?
>> Thanks,
>> DeeJay
>
>.
>|||Do you get any error message, what happens when you try to
run the job ?
How long the batch file takes to run ? We had an issue
where sql agent timed out if the batch file took more than
30 minutes to run. It happened only once and we ran the
file manually from command prompt. You can also try to run
it as a tsql command using Exec
master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
hth.
>--Original Message--
>Hi
>Thanks, but that's exactly how I have been trying to run
>it, but it does not work.
>I am running the job with an administrative account that
>has the required permissions.
>I have tested the script by running is manually on the
>server and it works fine.
>I wonder what gives.
>DeeJay
>>--Original Message--
>>Hi,
>>In SQL Agent Jobs -- Step option, after
>selecting 'Operating System Command
>>(CmdExec)' , In the command window you can type
>>e:\sqlbackups\delete.bat
>>(This will call the batch file delete.bat based on the
>schedule interval)
>>Thanks
>>Hari
>>MCDBA
>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>> Hi,
>> I am trying to run a batch file using the SQL Server
>Agent
>> (I have scheduled a job). I have chosen the step type
to
>> be 'Operating System Command (CmdExec)'. But I am not
>sure
>> how to code the actual command.
>> The batch file is located at: e:\sqlbackups\delete.bat.
>> How do specify the Agent to call this batch file?
>> Thanks,
>> DeeJay
>>
>>.
>.
>|||That's the weird part...I do not get any errors. Actually
according to the agent the job completes successfully, but
the files are not deleted.
I tried your suggestion and it did not work. But this time
I do get an error:
The process could not be created for step 1 of job
0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped I/O
operation is in progress). The step failed.
This is really confusing. I do not want to run the
manually every other day.
Thanks,
DeeJay
>--Original Message--
>Do you get any error message, what happens when you try
to
>run the job ?
>How long the batch file takes to run ? We had an issue
>where sql agent timed out if the batch file took more
than
>30 minutes to run. It happened only once and we ran the
>file manually from command prompt. You can also try to
run
>it as a tsql command using Exec
>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>hth.
>>--Original Message--
>>Hi
>>Thanks, but that's exactly how I have been trying to run
>>it, but it does not work.
>>I am running the job with an administrative account that
>>has the required permissions.
>>I have tested the script by running is manually on the
>>server and it works fine.
>>I wonder what gives.
>>DeeJay
>>--Original Message--
>>Hi,
>>In SQL Agent Jobs -- Step option, after
>>selecting 'Operating System Command
>>(CmdExec)' , In the command window you can type
>>e:\sqlbackups\delete.bat
>>(This will call the batch file delete.bat based on the
>>schedule interval)
>>Thanks
>>Hari
>>MCDBA
>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>> Hi,
>> I am trying to run a batch file using the SQL Server
>>Agent
>> (I have scheduled a job). I have chosen the step type
>to
>> be 'Operating System Command (CmdExec)'. But I am not
>>sure
>> how to code the actual command.
>> The batch file is located at:
e:\sqlbackups\delete.bat.
>> How do specify the Agent to call this batch file?
>> Thanks,
>> DeeJay
>>
>>.
>>.
>.
>|||For real time error info, try to execute xp_cmdshell from
query analyzer.
I wonder if it is a permission issue, if the job owner is
a sysadmin then then sql server will use service startup
account to execute xp_cmdshell, otherwise it will use a
proxy account. Does sql startup account has permissions to
delete those files ?
hth.
>--Original Message--
>That's the weird part...I do not get any errors. Actually
>according to the agent the job completes successfully,
but
>the files are not deleted.
>I tried your suggestion and it did not work. But this
time
>I do get an error:
>The process could not be created for step 1 of job
>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
I/O
>operation is in progress). The step failed.
>This is really confusing. I do not want to run the
>manually every other day.
>Thanks,
>DeeJay
>>--Original Message--
>>Do you get any error message, what happens when you try
>to
>>run the job ?
>>How long the batch file takes to run ? We had an issue
>>where sql agent timed out if the batch file took more
>than
>>30 minutes to run. It happened only once and we ran the
>>file manually from command prompt. You can also try to
>run
>>it as a tsql command using Exec
>>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>>hth.
>>--Original Message--
>>Hi
>>Thanks, but that's exactly how I have been trying to
run
>>it, but it does not work.
>>I am running the job with an administrative account
that
>>has the required permissions.
>>I have tested the script by running is manually on the
>>server and it works fine.
>>I wonder what gives.
>>DeeJay
>>--Original Message--
>>Hi,
>>In SQL Agent Jobs -- Step option, after
>>selecting 'Operating System Command
>>(CmdExec)' , In the command window you can type
>>e:\sqlbackups\delete.bat
>>(This will call the batch file delete.bat based on the
>>schedule interval)
>>Thanks
>>Hari
>>MCDBA
>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>> Hi,
>> I am trying to run a batch file using the SQL Server
>>Agent
>> (I have scheduled a job). I have chosen the step
type
>>to
>> be 'Operating System Command (CmdExec)'. But I am
not
>>sure
>> how to code the actual command.
>> The batch file is located at:
>e:\sqlbackups\delete.bat.
>> How do specify the Agent to call this batch file?
>> Thanks,
>> DeeJay
>>
>>.
>>.
>>.
>.
>|||I tested running the job with me as the owner and I am a
local administrator on this server. I have also tried
using 'sa' and the service startup and nothing is working.
I have tried running the xp_cmdshell procedure in QA and
it also does not work...I just get the output as 'NULL'.
I wonder if it is ONLY using the proxy account. That
account may not have the required permissions.
I will check that.
DeeJay
>--Original Message--
>For real time error info, try to execute xp_cmdshell from
>query analyzer.
>I wonder if it is a permission issue, if the job owner is
>a sysadmin then then sql server will use service startup
>account to execute xp_cmdshell, otherwise it will use a
>proxy account. Does sql startup account has permissions
to
>delete those files ?
>hth.
>>--Original Message--
>>That's the weird part...I do not get any errors.
Actually
>>according to the agent the job completes successfully,
>but
>>the files are not deleted.
>>I tried your suggestion and it did not work. But this
>time
>>I do get an error:
>>The process could not be created for step 1 of job
>>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
>I/O
>>operation is in progress). The step failed.
>>This is really confusing. I do not want to run the
>>manually every other day.
>>Thanks,
>>DeeJay
>>--Original Message--
>>Do you get any error message, what happens when you try
>>to
>>run the job ?
>>How long the batch file takes to run ? We had an issue
>>where sql agent timed out if the batch file took more
>>than
>>30 minutes to run. It happened only once and we ran the
>>file manually from command prompt. You can also try to
>>run
>>it as a tsql command using Exec
>>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>>hth.
>>--Original Message--
>>Hi
>>Thanks, but that's exactly how I have been trying to
>run
>>it, but it does not work.
>>I am running the job with an administrative account
>that
>>has the required permissions.
>>I have tested the script by running is manually on the
>>server and it works fine.
>>I wonder what gives.
>>DeeJay
>>--Original Message--
>>Hi,
>>In SQL Agent Jobs -- Step option, after
>>selecting 'Operating System Command
>>(CmdExec)' , In the command window you can type
>>e:\sqlbackups\delete.bat
>>(This will call the batch file delete.bat based on
the
>>schedule interval)
>>Thanks
>>Hari
>>MCDBA
>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>> Hi,
>> I am trying to run a batch file using the SQL
Server
>>Agent
>> (I have scheduled a job). I have chosen the step
>type
>>to
>> be 'Operating System Command (CmdExec)'. But I am
>not
>>sure
>> how to code the actual command.
>> The batch file is located at:
>>e:\sqlbackups\delete.bat.
>> How do specify the Agent to call this batch file?
>> Thanks,
>> DeeJay
>>
>>.
>>.
>>.
>>.
>.
>|||Hi,
I forgot that SQL Server 2000 does not have this proxy
account on the local server. I did find a domain
account 'SQLServerAcctCmdExec' and I added this to the
local administrators account and it still did not work.
DeeJay
>--Original Message--
>I tested running the job with me as the owner and I am a
>local administrator on this server. I have also tried
>using 'sa' and the service startup and nothing is working.
>I have tried running the xp_cmdshell procedure in QA and
>it also does not work...I just get the output as 'NULL'.
>I wonder if it is ONLY using the proxy account. That
>account may not have the required permissions.
>I will check that.
>DeeJay
>>--Original Message--
>>For real time error info, try to execute xp_cmdshell
from
>>query analyzer.
>>I wonder if it is a permission issue, if the job owner
is
>>a sysadmin then then sql server will use service startup
>>account to execute xp_cmdshell, otherwise it will use a
>>proxy account. Does sql startup account has permissions
>to
>>delete those files ?
>>hth.
>>--Original Message--
>>That's the weird part...I do not get any errors.
>Actually
>>according to the agent the job completes successfully,
>>but
>>the files are not deleted.
>>I tried your suggestion and it did not work. But this
>>time
>>I do get an error:
>>The process could not be created for step 1 of job
>>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
>>I/O
>>operation is in progress). The step failed.
>>This is really confusing. I do not want to run the
>>manually every other day.
>>Thanks,
>>DeeJay
>>--Original Message--
>>Do you get any error message, what happens when you
try
>>to
>>run the job ?
>>How long the batch file takes to run ? We had an issue
>>where sql agent timed out if the batch file took more
>>than
>>30 minutes to run. It happened only once and we ran
the
>>file manually from command prompt. You can also try to
>>run
>>it as a tsql command using Exec
>>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>>hth.
>>--Original Message--
>>Hi
>>Thanks, but that's exactly how I have been trying to
>>run
>>it, but it does not work.
>>I am running the job with an administrative account
>>that
>>has the required permissions.
>>I have tested the script by running is manually on
the
>>server and it works fine.
>>I wonder what gives.
>>DeeJay
>>--Original Message--
>>Hi,
>>In SQL Agent Jobs -- Step option, after
>>selecting 'Operating System Command
>>(CmdExec)' , In the command window you can type
>>e:\sqlbackups\delete.bat
>>(This will call the batch file delete.bat based on
>the
>>schedule interval)
>>Thanks
>>Hari
>>MCDBA
>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>>> Hi,
>>>
>>> I am trying to run a batch file using the SQL
>Server
>>Agent
>>> (I have scheduled a job). I have chosen the step
>>type
>>to
>>> be 'Operating System Command (CmdExec)'. But I am
>>not
>>sure
>>> how to code the actual command.
>>>
>>> The batch file is located at:
>>e:\sqlbackups\delete.bat.
>>>
>>> How do specify the Agent to call this batch file?
>>>
>>> Thanks,
>>>
>>> DeeJay
>>>
>>
>>.
>>.
>>.
>>.
>>.
>.
>|||Make a little change to the batch file and add folloiwng
line at different places:
@.echo In the batch file at line <#>
when you run the batch file in QA it should display these
messages in results window.
In Enterprise Manager, if you open properties of SQL
Server Agent, click on Job System, you will see option to
specify proxy account, use a local admin for it, and then
restart sql services.
hope this helps.
>--Original Message--
>Hi,
>I forgot that SQL Server 2000 does not have this proxy
>account on the local server. I did find a domain
>account 'SQLServerAcctCmdExec' and I added this to the
>local administrators account and it still did not work.
>DeeJay
>>--Original Message--
>>I tested running the job with me as the owner and I am a
>>local administrator on this server. I have also tried
>>using 'sa' and the service startup and nothing is
working.
>>I have tried running the xp_cmdshell procedure in QA and
>>it also does not work...I just get the output as 'NULL'.
>>I wonder if it is ONLY using the proxy account. That
>>account may not have the required permissions.
>>I will check that.
>>DeeJay
>>--Original Message--
>>For real time error info, try to execute xp_cmdshell
>from
>>query analyzer.
>>I wonder if it is a permission issue, if the job owner
>is
>>a sysadmin then then sql server will use service
startup
>>account to execute xp_cmdshell, otherwise it will use a
>>proxy account. Does sql startup account has permissions
>>to
>>delete those files ?
>>hth.
>>--Original Message--
>>That's the weird part...I do not get any errors.
>>Actually
>>according to the agent the job completes successfully,
>>but
>>the files are not deleted.
>>I tried your suggestion and it did not work. But this
>>time
>>I do get an error:
>>The process could not be created for step 1 of job
>>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
>>I/O
>>operation is in progress). The step failed.
>>This is really confusing. I do not want to run the
>>manually every other day.
>>Thanks,
>>DeeJay
>>--Original Message--
>>Do you get any error message, what happens when you
>try
>>to
>>run the job ?
>>How long the batch file takes to run ? We had an
issue
>>where sql agent timed out if the batch file took more
>>than
>>30 minutes to run. It happened only once and we ran
>the
>>file manually from command prompt. You can also try
to
>>run
>>it as a tsql command using Exec
>>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>>hth.
>>--Original Message--
>>Hi
>>Thanks, but that's exactly how I have been trying to
>>run
>>it, but it does not work.
>>I am running the job with an administrative account
>>that
>>has the required permissions.
>>I have tested the script by running is manually on
>the
>>server and it works fine.
>>I wonder what gives.
>>DeeJay
>>>--Original Message--
>>>Hi,
>>>
>>>In SQL Agent Jobs -- Step option, after
>>selecting 'Operating System Command
>>>(CmdExec)' , In the command window you can type
>>>
>>>e:\sqlbackups\delete.bat
>>>
>>>(This will call the batch file delete.bat based on
>>the
>>schedule interval)
>>>
>>>Thanks
>>>Hari
>>>MCDBA
>>>
>>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in
message
>>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>>> Hi,
>>>
>>> I am trying to run a batch file using the SQL
>>Server
>>Agent
>>> (I have scheduled a job). I have chosen the step
>>type
>>to
>>> be 'Operating System Command (CmdExec)'. But I am
>>not
>>sure
>>> how to code the actual command.
>>>
>>> The batch file is located at:
>>e:\sqlbackups\delete.bat.
>>>
>>> How do specify the Agent to call this batch file?
>>>
>>> Thanks,
>>>
>>> DeeJay
>>>
>>>
>>>
>>>.
>>>
>>.
>>.
>>.
>>.
>>.
>.
>|||I made the change and the script did not return all of the
expected lines. The scripts is doing a 'for' loop so that
could be the reason for not returning all of the lines.
I tried to enter the proxy account, but I am getting
errors:
'Error executing extended stored procedure: Specified User
can not login'.
I am connected to SQL Server with 'sa' and I tried the
local administrator account (will it be a problem if the
local administrator account has been renamed?) and I also
tried with my own domain ID (sysadmin on sql server) and
same results. I did not think I would need change the
account since 'only sysadmins' can run cmdshell jobs and I
have been trying to run the jobs/commands with sysadmin
rights.
Here is the actual script which works fine manually on the
server:
@.echo off
setlocal
:: *** Root path of the directory where the files are to
be deleted:
set
Root=E:\sqlbackups\archtlog\CopyArchdTlogBackups\kdirect
:: *** Number of files to keep:
set Keep=590
if not exist "%Root%" goto :err_Root
for /f "skip=%Keep% tokens=*" %%a in ('dir /b /a:-d /o:-
d "%Root%" 2^>NUL') do (
echo Deleting %%a ...
:: *** Remove the "echo" in the next line to 'arm' the
script:
del "%Root%\%%a"
)
goto leave
:err_Root
echo The directory "%Root%" does not exist.
:leave
This is really bugging me!
DeeJay
>--Original Message--
>Make a little change to the batch file and add folloiwng
>line at different places:
>@.echo In the batch file at line <#>
>when you run the batch file in QA it should display these
>messages in results window.
>In Enterprise Manager, if you open properties of SQL
>Server Agent, click on Job System, you will see option to
>specify proxy account, use a local admin for it, and then
>restart sql services.
>hope this helps.
>>--Original Message--
>>Hi,
>>I forgot that SQL Server 2000 does not have this proxy
>>account on the local server. I did find a domain
>>account 'SQLServerAcctCmdExec' and I added this to the
>>local administrators account and it still did not work.
>>DeeJay
>>--Original Message--
>>I tested running the job with me as the owner and I am
a
>>local administrator on this server. I have also tried
>>using 'sa' and the service startup and nothing is
>working.
>>I have tried running the xp_cmdshell procedure in QA
and
>>it also does not work...I just get the output
as 'NULL'.
>>I wonder if it is ONLY using the proxy account. That
>>account may not have the required permissions.
>>I will check that.
>>DeeJay
>>--Original Message--
>>For real time error info, try to execute xp_cmdshell
>>from
>>query analyzer.
>>I wonder if it is a permission issue, if the job owner
>>is
>>a sysadmin then then sql server will use service
>startup
>>account to execute xp_cmdshell, otherwise it will use
a
>>proxy account. Does sql startup account has
permissions
>>to
>>delete those files ?
>>hth.
>>--Original Message--
>>That's the weird part...I do not get any errors.
>>Actually
>>according to the agent the job completes
successfully,
>>but
>>the files are not deleted.
>>I tried your suggestion and it did not work. But this
>>time
>>I do get an error:
>>The process could not be created for step 1 of job
>>0xC38683087ECAA14AB6614A1A74F27555 (reason:
Overlapped
>>I/O
>>operation is in progress). The step failed.
>>This is really confusing. I do not want to run the
>>manually every other day.
>>Thanks,
>>DeeJay
>>--Original Message--
>>Do you get any error message, what happens when you
>>try
>>to
>>run the job ?
>>How long the batch file takes to run ? We had an
>issue
>>where sql agent timed out if the batch file took
more
>>than
>>30 minutes to run. It happened only once and we ran
>>the
>>file manually from command prompt. You can also try
>to
>>run
>>it as a tsql command using Exec
>>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>>hth.
>>>--Original Message--
>>>Hi
>>>
>>>Thanks, but that's exactly how I have been trying
to
>>run
>>>it, but it does not work.
>>>
>>>I am running the job with an administrative account
>>that
>>>has the required permissions.
>>>
>>>I have tested the script by running is manually on
>>the
>>>server and it works fine.
>>>
>>>I wonder what gives.
>>>
>>>DeeJay
>>>--Original Message--
>>>Hi,
>>>
>>>In SQL Agent Jobs -- Step option, after
>>>selecting 'Operating System Command
>>>(CmdExec)' , In the command window you can type
>>>
>>>e:\sqlbackups\delete.bat
>>>
>>>(This will call the batch file delete.bat based on
>>the
>>>schedule interval)
>>>
>>>Thanks
>>>Hari
>>>MCDBA
>>>
>>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in
>message
>>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
>>> Hi,
>>>
>>> I am trying to run a batch file using the SQL
>>Server
>>>Agent
>>> (I have scheduled a job). I have chosen the step
>>type
>>to
>>> be 'Operating System Command (CmdExec)'. But I
am
>>not
>>>sure
>>> how to code the actual command.
>>>
>>> The batch file is located at:
>>e:\sqlbackups\delete.bat.
>>>
>>> How do specify the Agent to call this batch file?
>>>
>>> Thanks,
>>>
>>> DeeJay
>>>
>>>
>>>
>>>.
>>>
>>>.
>>>
>>.
>>.
>>.
>>.
>>.
>.
>|||Hi Deejay,
2 things,
if you have set up SQL Agent to use proxy account, proxy account will only
be used if the owner of the SQLAgentJob is NOT a member of the local
administrators group.
Secondly if the SQLProxyaccount is a member of the local administrator but
Builtin\Admin has been removed as a part of the a security policy then you
need to add SQLProxy account as a login and grant this account the necessary
rights to execute xp_cmdshell.
I hope this helps your situation.
--
Olu Adedeji
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:295d01c3fca7$4b3ddfb0$a501280a@.phx.gbl...
> I made the change and the script did not return all of the
> expected lines. The scripts is doing a 'for' loop so that
> could be the reason for not returning all of the lines.
> I tried to enter the proxy account, but I am getting
> errors:
> 'Error executing extended stored procedure: Specified User
> can not login'.
> I am connected to SQL Server with 'sa' and I tried the
> local administrator account (will it be a problem if the
> local administrator account has been renamed?) and I also
> tried with my own domain ID (sysadmin on sql server) and
> same results. I did not think I would need change the
> account since 'only sysadmins' can run cmdshell jobs and I
> have been trying to run the jobs/commands with sysadmin
> rights.
> Here is the actual script which works fine manually on the
> server:
> @.echo off
> setlocal
> :: *** Root path of the directory where the files are to
> be deleted:
> set
> Root=E:\sqlbackups\archtlog\CopyArchdTlogBackups\kdirect
> :: *** Number of files to keep:
> set Keep=590
>
> if not exist "%Root%" goto :err_Root
> for /f "skip=%Keep% tokens=*" %%a in ('dir /b /a:-d /o:-
> d "%Root%" 2^>NUL') do (
> echo Deleting %%a ...
> :: *** Remove the "echo" in the next line to 'arm' the
> script:
> del "%Root%\%%a"
> )
> goto leave
>
> :err_Root
> echo The directory "%Root%" does not exist.
>
> :leave
>
> This is really bugging me!
> DeeJay
> >--Original Message--
> >Make a little change to the batch file and add folloiwng
> >line at different places:
> >
> >@.echo In the batch file at line <#>
> >
> >when you run the batch file in QA it should display these
> >messages in results window.
> >
> >In Enterprise Manager, if you open properties of SQL
> >Server Agent, click on Job System, you will see option to
> >specify proxy account, use a local admin for it, and then
> >restart sql services.
> >
> >hope this helps.
> >
> >>--Original Message--
> >>Hi,
> >>
> >>I forgot that SQL Server 2000 does not have this proxy
> >>account on the local server. I did find a domain
> >>account 'SQLServerAcctCmdExec' and I added this to the
> >>local administrators account and it still did not work.
> >>
> >>DeeJay
> >>--Original Message--
> >>I tested running the job with me as the owner and I am
> a
> >>local administrator on this server. I have also tried
> >>using 'sa' and the service startup and nothing is
> >working.
> >>
> >>I have tried running the xp_cmdshell procedure in QA
> and
> >>it also does not work...I just get the output
> as 'NULL'.
> >>
> >>I wonder if it is ONLY using the proxy account. That
> >>account may not have the required permissions.
> >>
> >>I will check that.
> >>
> >>DeeJay
> >>--Original Message--
> >>For real time error info, try to execute xp_cmdshell
> >>from
> >>query analyzer.
> >>
> >>I wonder if it is a permission issue, if the job owner
> >>is
> >>a sysadmin then then sql server will use service
> >startup
> >>account to execute xp_cmdshell, otherwise it will use
> a
> >>proxy account. Does sql startup account has
> permissions
> >>to
> >>delete those files ?
> >>
> >>hth.
> >>
> >>--Original Message--
> >>That's the weird part...I do not get any errors.
> >>Actually
> >>according to the agent the job completes
> successfully,
> >>but
> >>the files are not deleted.
> >>
> >>I tried your suggestion and it did not work. But this
> >>time
> >>I do get an error:
> >>
> >>The process could not be created for step 1 of job
> >>0xC38683087ECAA14AB6614A1A74F27555 (reason:
> Overlapped
> >>I/O
> >>operation is in progress). The step failed.
> >>
> >>This is really confusing. I do not want to run the
> >>manually every other day.
> >>
> >>Thanks,
> >>DeeJay
> >>
> >>--Original Message--
> >>Do you get any error message, what happens when you
> >>try
> >>to
> >>run the job ?
> >>
> >>How long the batch file takes to run ? We had an
> >issue
> >>where sql agent timed out if the batch file took
> more
> >>than
> >>30 minutes to run. It happened only once and we ran
> >>the
> >>file manually from command prompt. You can also try
> >to
> >>run
> >>it as a tsql command using Exec
> >>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
> >>
> >>hth.
> >>
> >>>--Original Message--
> >>>Hi
> >>>
> >>>Thanks, but that's exactly how I have been trying
> to
> >>run
> >>>it, but it does not work.
> >>>
> >>>I am running the job with an administrative account
> >>that
> >>>has the required permissions.
> >>>
> >>>I have tested the script by running is manually on
> >>the
> >>>server and it works fine.
> >>>
> >>>I wonder what gives.
> >>>
> >>>DeeJay
> >>>--Original Message--
> >>>Hi,
> >>>
> >>>In SQL Agent Jobs -- Step option, after
> >>>selecting 'Operating System Command
> >>>(CmdExec)' , In the command window you can type
> >>>
> >>>e:\sqlbackups\delete.bat
> >>>
> >>>(This will call the batch file delete.bat based on
> >>the
> >>>schedule interval)
> >>>
> >>>Thanks
> >>>Hari
> >>>MCDBA
> >>>
> >>>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in
> >message
> >>>news:14f3201c3fa33$599d4120$a001280a@.phx.gbl...
> >>> Hi,
> >>>
> >>> I am trying to run a batch file using the SQL
> >>Server
> >>>Agent
> >>> (I have scheduled a job). I have chosen the step
> >>type
> >>to
> >>> be 'Operating System Command (CmdExec)'. But I
> am
> >>not
> >>>sure
> >>> how to code the actual command.
> >>>
> >>> The batch file is located at:
> >>e:\sqlbackups\delete.bat.
> >>>
> >>> How do specify the Agent to call this batch file?
> >>>
> >>> Thanks,
> >>>
> >>> DeeJay
> >>>
> >>>
> >>>
> >>>.
> >>>
> >>>.
> >>>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >.
> >

Run a scheduled job from the command promt

Hi
I was wondering if it is possible to run a scheduele from the command
promt.
I have a job that runs every night that updates my sales in my
datawarehouse. That jobs executes several DTS-packages. But sometimes
some of my users wish to update the sales in the middle of the day.
So now I wondered if I could create a .bat file that executed the job.
I know that I can use the DTSRUN command to execute a package, but
thats not flexible enough for me
Regards and thanks in advance
FlemmingHi
Take a look at OSQL utility in the BOL.
"Flemming Nielsen" <flemming.delph@.gmail.com> wrote in message
news:fccae6c9.0506080010.5b48d526@.posting.google.com...
> Hi
> I was wondering if it is possible to run a scheduele from the command
> promt.
> I have a job that runs every night that updates my sales in my
> datawarehouse. That jobs executes several DTS-packages. But sometimes
> some of my users wish to update the sales in the middle of the day.
> So now I wondered if I could create a .bat file that executed the job.
> I know that I can use the DTSRUN command to execute a package, but
> thats not flexible enough for me
> Regards and thanks in advance
> Flemming