December 4, 2023 at 2:47 pm
Hello experts,
I see this error in a SQL Agent job. It happens during a step that restores a database, and it is supposed to temporarily enabled xp_cmdshell to read some backup files, then disable xp_cmdshell.
Does anyone know why this error happens, and why it happens only sometimes? Thanks for any help.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) One or more of the options (nounload, stats, stats=, file) are not supported for this statement. Review the documentation for supported options. [SQLSTATE 42000] (Error 3032). The step failed
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 4, 2023 at 3:03 pm
It has nothing to do with xp_CmdShell. There's something wrong with the RESTORE command or the file wasn't found by the xp_CmdShell command.
If it were me, I'd modify the job to capture the dynamic SQL that it probably runs and save to a file or a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2023 at 5:58 pm
If it is windows, check the event viewer for more details on the error on the server.
I would enable 'xp_cmdshell' as a first step in the job and disable as a the last step to ensure that files are accessible during the entire restore duration.
=======================================================================
December 5, 2023 at 8:43 am
Why are you not just selecting the needed files from msdb and execute the restore statement directly in your sqlagent job using tsql?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 25, 2023 at 4:18 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply