Showing posts with label step. Show all posts
Showing posts with label step. Show all posts

Monday, March 26, 2012

running a job issue with ole db connections

Hi,

Got another question. I run my job and it runs the first two steps fine but when I hit the third step i get an error. In the third step I have an ole db connection to an AS400 db and downloading data from it. Is there any way that I can get this to read and accept the ole db connection because it seems like it doesn't recognize it.

Thanks

Brian

What is the third step? A task of some sort? What kind of task- what object did you drag onto the surface?

Donald Farmer

sql

running a job

I have a job with 3 steps. Each step has exec sp_start_job
to kick off another job. Will the job step wait 'till th
child job is completed before going to the next step?
TIADid you try it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jon Templar" <anonymous@.discussions.microsoft.com> wrote in message
news:733b01c4cc24$c209fd20$a301280a@.phx.gbl...
> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA|||When you call sp_start_job from another stored proc or an application, the
sp_start_job starts the job and the SQL in the calling SP or application does
not wait for the job to finish, it just keeps going.
I would expect the same behavior for when you exec sp_start_job in each of
the steps in your SQL Job.
To simulate long running jobs, you can use the WAITFOR command.
--e.g., wait for 10 minutes
waitfor DELAY '000:10:00'
Tea C.
"Jon Templar" wrote:
> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA
>

running a job

I have a job with 3 steps. Each step has exec sp_start_job
to kick off another job. Will the job step wait 'till th
child job is completed before going to the next step?
TIA
Did you try it?
http://www.aspfaq.com/
(Reverse address to reply.)
"Jon Templar" <anonymous@.discussions.microsoft.com> wrote in message
news:733b01c4cc24$c209fd20$a301280a@.phx.gbl...
> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA
|||When you call sp_start_job from another stored proc or an application, the
sp_start_job starts the job and the SQL in the calling SP or application does
not wait for the job to finish, it just keeps going.
I would expect the same behavior for when you exec sp_start_job in each of
the steps in your SQL Job.
To simulate long running jobs, you can use the WAITFOR command.
--e.g., wait for 10 minutes
waitfor DELAY '000:10:00'
Tea C.
"Jon Templar" wrote:

> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA
>
sql

running a job

I have a job with 3 steps. Each step has exec sp_start_job
to kick off another job. Will the job step wait 'till th
child job is completed before going to the next step?
TIADid you try it?
http://www.aspfaq.com/
(Reverse address to reply.)
"Jon Templar" <anonymous@.discussions.microsoft.com> wrote in message
news:733b01c4cc24$c209fd20$a301280a@.phx.gbl...
> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA|||When you call sp_start_job from another stored proc or an application, the
sp_start_job starts the job and the SQL in the calling SP or application doe
s
not wait for the job to finish, it just keeps going.
I would expect the same behavior for when you exec sp_start_job in each of
the steps in your SQL Job.
To simulate long running jobs, you can use the WAITFOR command.
--e.g., wait for 10 minutes
waitfor DELAY '000:10:00'
Tea C.
"Jon Templar" wrote:

> I have a job with 3 steps. Each step has exec sp_start_job
> to kick off another job. Will the job step wait 'till th
> child job is completed before going to the next step?
> TIA
>

Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This of
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
Sally
I use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to run
> these processes serially? It is currently running in parallel mode. This of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally
sql

Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This o
f
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
SallyI use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to r
un
> these processes serially? It is currently running in parallel mode. This
of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally

Wednesday, March 21, 2012

Run SSIS Package from SQL 2000

I am looking for a way to execute an SSIS package as the next step in the SQL 2000 Agent, with the first step being replication. Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Thanks

JWeil wrote:

... Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Yes, should work. Agent in SQL 2000 has a limitation of just one proxy account, but if you are OK with this and the package can be run under this account, it should be fine.

|||

Thanks.

Would I need to install anything on the SQL 2000 server or just reference the executable on the 2005 box?

|||SSIS should be installed on the machine where the package is run, you can't run it by pointing to dtexec on remote share.

If you want to run the package on the same machine, where SQL 2000 is installed - install SSIS on this box.

If you want to run the package on another box where SQL 2005 is installed, you need some other way - e.g. create Agent job on SQL 2005 machine, invoke it remotely using Agent store procedures.

Run SQL T-SQL Job Step across Different Domains

I have two sql 2005 servers in two different domains. I have a SQL job on
one of the servers that runs a T-SQL script that accesses databases on the
SQL box inthe other domain.
I'm getting a message in the job history that the job won't run because of
the security context.
How can I set this up?
Thanks for your help.
--
MWDWhat Operating Systems are these two servers running?
What trusts, if any, are set up between the two domains?
How are you trying to connect?
What is the exact error number and error message?
-Sue
On Thu, 9 Aug 2007 08:10:09 -0700, MWD
<MWD@.discussions.microsoft.com> wrote:

>I have two sql 2005 servers in two different domains. I have a SQL job on
>one of the servers that runs a T-SQL script that accesses databases on the
>SQL box inthe other domain.
>I'm getting a message in the job history that the job won't run because of
>the security context.
>How can I set this up?
>Thanks for your help.sql

Tuesday, March 20, 2012

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

run reports as steps of sql agent job

I have a few reports. I want to run each report as one step of sql agent job. If the report succeeds only then the job should move to the next step ? is it possible

I am able to make separate jobs for separate reports but not as steps of one job.

Thanks in advance

When you say "run the report", what are you trying to accomplish? Pre-creating the intermediary report so subsequent requests are rendered more quickly? Or are you trying to deliver the fully rendered report to a user?

In either case, there are built-in mechanisms for doing that.

Can you elaborate a bit?

Thanks!

Joe|||

I want to deliver the fully rendered report to the user. Actually I have around 50 reports in 50 different scheduled jobs. When the schedule goes as planned its ok. But when the schedule fails for some reason I have to manually run 50 reports one after the other. I want some kind of job that runs all the reports one after the other. i.e. one report in each step.

run reports as steps of sql agent job

I have a few reports. I want to run each report as one step of sql agent job. If the report succeeds only then the job should move to the next step ? is it possible

I am able to make separate jobs for separate reports but not as steps of one job.

Thanks in advance

When you say "run the report", what are you trying to accomplish? Pre-creating the intermediary report so subsequent requests are rendered more quickly? Or are you trying to deliver the fully rendered report to a user?

In either case, there are built-in mechanisms for doing that.

Can you elaborate a bit?

Thanks!

Joe|||

I want to deliver the fully rendered report to the user. Actually I have around 50 reports in 50 different scheduled jobs. When the schedule goes as planned its ok. But when the schedule fails for some reason I have to manually run 50 reports one after the other. I want some kind of job that runs all the reports one after the other. i.e. one report in each step.

Monday, March 12, 2012

Run Maintanance Plan As Agent Job Step

Hi Everyone,
I have created a maintanance plan to generate a full database backup with no
schedule.
I have then created a new Agent job with multiple steps, most of the steps
execute TSQL and these seem to be working. One of the job steps is to execute
the Full database backup SSIS package generated by the Maintanance plan. This
step executes in less than 1 second without an error and does not generate
the backup.
If I execute the maintanance plan directly via the right click EXECUTE
command it works as expected.
I have checked to ensure that the Agent job owner is a member of the
sysadmin so I don't think access rights are an issue.
Does anyone have any ideas?
I have considered copying the TSQL the maintanance plan generated and
executing this in the step instead of the SSIS package but I would like to
know why my existing configuration will not work.
Please help
ACM
On Jun 11, 12:58 pm, ACM <A...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I have created a maintanance plan to generate a full database backup with no
> schedule.
> I have then created a new Agent job with multiple steps, most of the steps
> execute TSQL and these seem to be working. One of the job steps is to execute
> the Full database backup SSIS package generated by the Maintanance plan. This
> step executes in less than 1 second without an error and does not generate
> the backup.
> If I execute the maintanance plan directly via the right click EXECUTE
> command it works as expected.
> I have checked to ensure that the Agent job owner is a member of the
> sysadmin so I don't think access rights are an issue.
> Does anyone have any ideas?
> I have considered copying the TSQL the maintanance plan generated and
> executing this in the step instead of the SSIS package but I would like to
> know why my existing configuration will not work.
> Please help
> ACM
Hi
Try running dtexec with verbose reporting see
http://msdn2.microsoft.com/en-us/library/ms162810(SQL.90).aspx
John

Run Maintanance Plan As Agent Job Step

Hi Everyone,
I have created a maintanance plan to generate a full database backup with no
schedule.
I have then created a new Agent job with multiple steps, most of the steps
execute TSQL and these seem to be working. One of the job steps is to execute
the Full database backup SSIS package generated by the Maintanance plan. This
step executes in less than 1 second without an error and does not generate
the backup.
If I execute the maintanance plan directly via the right click EXECUTE
command it works as expected.
I have checked to ensure that the Agent job owner is a member of the
sysadmin so I don't think access rights are an issue.
Does anyone have any ideas?
I have considered copying the TSQL the maintanance plan generated and
executing this in the step instead of the SSIS package but I would like to
know why my existing configuration will not work.
Please help
ACMOn Jun 11, 12:58 pm, ACM <A...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I have created a maintanance plan to generate a full database backup with no
> schedule.
> I have then created a new Agent job with multiple steps, most of the steps
> execute TSQL and these seem to be working. One of the job steps is to execute
> the Full database backup SSIS package generated by the Maintanance plan. This
> step executes in less than 1 second without an error and does not generate
> the backup.
> If I execute the maintanance plan directly via the right click EXECUTE
> command it works as expected.
> I have checked to ensure that the Agent job owner is a member of the
> sysadmin so I don't think access rights are an issue.
> Does anyone have any ideas?
> I have considered copying the TSQL the maintanance plan generated and
> executing this in the step instead of the SSIS package but I would like to
> know why my existing configuration will not work.
> Please help
> ACM
Hi
Try running dtexec with verbose reporting see
http://msdn2.microsoft.com/en-us/library/ms162810(SQL.90).aspx
John

Friday, March 9, 2012

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
> >>>
> >>>
> >>>
> >>>.
> >>>
> >>>.
> >>>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >.
> >

Wednesday, March 7, 2012

run an external file

I am using sql server 2000 sp3a.
How do I start (run) an external file. ie, run a vbs or exe fro Query
Analyzer or from within a job step.
Thanks,
YanivWithin a jobstep you can choose that the type of job step is "Operation
System command", put your command in here. Another option would be tu
sue the procedure xp_smdshell which call call the commadn interpreter,
therefore you have to choose TSQL Command execution in the job step.
HTH, jens Suessmeyer.|||Run vbs file
EXEC master..xp_cmdshell 'cscript /nologo c:\file.vbs "stringarg" 5'
> sue the procedure xp_smdshell which call call the commadn interpreter,
xp_cmdshell ? :-)))))))))
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138186195.161343.91450@.g49g2000cwa.googlegroups.com...
> Within a jobstep you can choose that the type of job step is "Operation
> System command", put your command in here. Another option would be tu
> sue the procedure xp_smdshell which call call the commadn interpreter,
> therefore you have to choose TSQL Command execution in the job step.
> HTH, jens Suessmeyer.
>|||Thank you both.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23YmvfNaIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Run vbs file
> EXEC master..xp_cmdshell 'cscript /nologo c:\file.vbs "stringarg" 5'
>> sue the procedure xp_smdshell which call call the commadn interpreter,
> xp_cmdshell ? :-)))))))))
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1138186195.161343.91450@.g49g2000cwa.googlegroups.com...
>> Within a jobstep you can choose that the type of job step is "Operation
>> System command", put your command in here. Another option would be tu
>> sue the procedure xp_smdshell which call call the commadn interpreter,
>> therefore you have to choose TSQL Command execution in the job step.
>> HTH, jens Suessmeyer.
>

Saturday, February 25, 2012

RTM Integration Services Job

When I try to create a job step of type SQL Server Integration Services Package I get an error:

===================================

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)


Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Load(JobStepData data)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.typeList_SelectedIndexChanged(Object sender, EventArgs e)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

And here's the version info:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

As far as I can tell the Integration Services service is running and I installed all features for testing.

Any help is appreciated.

Larry

This might be due to an unclean uninstall of BETA 2005. I followed the directions for removing previous versions, but Reporting Services also wasn't working and the entire directory was missing. I did install to the D:\ but otherwise it was a fairly plain install.|||Ok, I uninstalled everything, deleted the orphaned directories and remaining 90 registry keys and reinstalled.

Reporting Services was really funky. It didn't work, the configuration assistant wouldn't let me change anything. I whacked the virtual directories created at install from IIS and had to delete the databases created during install. Stopped the service and then could get through configuration down to Initialization at which point I had to start the service to proceed. It seems to be working finally.

For the original problem:
When I try to create a job step of type SQL Server Integration Services Package I get an error:

It is still happening. Any help would be appreciated.|||Interestingly, I created a database diagram, and a maintenance plan and suddenly the interface for Integration Services jobs started working. No idea why, but I'm not fond of magic interfaces, and yet mildly pleased that it's working.