August 5, 2010 at 1:37 am
...final attempt to get to the bottom of this one... ran trace, set sql agent to trace messages, created output file for CmdExec... result :
i) CmdExec log file empty
ii) No mention of 'failure' in sql server agent logs
iii) trace file shows transaction commited and logout, then sits there with no apparent db activity for 10 minutes when agent logs in writes 'failure' to the logs...
Could be a pretty tough call suggesting that the only fix is a server upgrade!
S
August 5, 2010 at 1:58 am
does it happen every time or is it sporadic?
after i got the error i would just reboot (iirc i looked at all the logs as you did) and it seemed the server would be fine for a while. i suspect(ed) a memory leak type situation. but that doesn't explain how a new server could fix it...unless it has something more to do with a fresh clean installation.
August 5, 2010 at 2:45 am
...occurs 9 times out of 10. As it happens, server was re-booted on Sunday. 'Failure' ( note use of quotes! ) occured Monday, Tuesday and today, but was fine Wednesday...
The fact that there was no mention in the sql agent log suggests to me that sql server does not know what it's right and left hands are doing, so would further suggest that problem is due to something outside of sql servers 'normal operating parameters', if that makes sense!
August 5, 2010 at 2:55 am
obvious question but i'll ask - can you move to sql 2005 or better, 2008? do you have a test server that you could do a clean install? if you don't get the errors on the test, you could just "switch" that one over to the production server and use the server with errors as your test (and reinstall that one eventually).
we are still running 2000 because the work involved in upgrading the 100s of DTS packages outweighs the risks. we're going to move our DW to SAP in a year or less. so we just keep it running as-is for the time being.
August 5, 2010 at 9:25 am
...by a strange coincidence we're upgrading to 2005 in a few months time. Would have been nice to have resolved the issue instead of it just going away tho!
thanks - S
August 5, 2010 at 9:27 am
It has been a long time since I dealt with this issue in our environment, but it seems like changing the job to a SQL step using xp_cmdshell made it work at the time. Most frustrating thing is the inconsistency!
August 6, 2010 at 2:43 am
...thanks for the suggestion, I'll give it a go...
S
September 1, 2010 at 7:36 am
...hhhm, I was happy it was working until I realised it wasn't returning a return code!
When run as a cmdexec, the step either returns 1 or 0. As xp_cmdshell it always returns null?!?
Has anyone experienced this?
S
March 30, 2011 at 11:54 pm
I m experiencing the same issue.
Running a batch file, but it is not.. 🙁
"The step did not generate any output. Process Exit Code 1. The step failed."
Command Line : cmd /c "call D:\StaticBookDataRefresh\SummitBookRefreshFSA.bat >> D:\StaticBookDataRefresh\pm_%date:/=-%.log 2>&1"
July 16, 2014 at 12:26 pm
I work in SQL SSMS 2012. We maintain a primary SQL server 2008 R2 for all the SQL agent jobs.
Many job steps include CMDEXE calling .BAT files.
All run as the same Domain Admin account to ensure access to all network locations.
On occasion, one of these jobs up and fails after having run successfully for months and years.
I just spent hours messing with this myself because there was no explanation, no change in credentials, no change to our network, etc., and no change to the code or the job steps, etc.
One of these jobs in particular, the .BAT script is "IF the file exists, do these things, ELSE GOTO END".
So I decided to handle the ELSE another way instead of simply exiting the program. I changed ELSE to "run this .vbs that emails a failure message" and VOILA, for some reason the SQL job runs perfectly fine now. I changed NOTHING else.
Does Microsoft no longer like ELSE statements that simply exit a program? What happened to just IF something and nothing else? Not sure why that worked exactly, but really nothing else changed and the SQL Job Agent worked the very next several times I tested it.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply