Re: Run Batch Files with SQL Server Agent
From: Olu Adedeji (anonymous_at_email.com)
Date: 02/29/04
- Next message: Uri Dimant: "Re: problems when backup transaction log"
- Previous message: ranm: "problems when backup transaction log"
- In reply to: DeeJay Puar: "Re: Run Batch Files with SQL Server Agent"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 29 Feb 2004 13:16:35 -0000
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
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>.
> >>>>>>>>
> >>>>>>>.
> >>>>>>>
> >>>>>>.
> >>>>>>
> >>>>>.
> >>>>>
> >>>>.
> >>>>
> >>>.
> >>>
> >>.
> >>
> >.
> >
- Next message: Uri Dimant: "Re: problems when backup transaction log"
- Previous message: ranm: "problems when backup transaction log"
- In reply to: DeeJay Puar: "Re: Run Batch Files with SQL Server Agent"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
