This driving our collective minds nuts. I have a batch file that uses SQLCMD.exe to call an sql script file that does some transaction log backups. This batch file is call by an piece of third party scheduling software (Tivoli - dont ask!). Now, in theory this does not matter as the user that the third party piece logs in always impersonates a user with sufficient rights to carry out any tasks on the sql server. In this particular case, the user is a member of the local administrators group and also has the log on as a batch priveledge. In reality, the batch file runs fine when called from anywhere by this user, but NEVER works when invoked via Tivoli. My tivoli team keep telling me its nothing to do with them as the routines run under the local user, Tivoli just makes the request at the sceduled time - but we always get the same error, it cannot find SQLCMD. It is on the machine, it is in the the path, the user has admin rights. Whats left to look at? (one minor point, via service packs and patches there appears to be several sqlcmd.exe on this machine - W2K3 SQL Server 2005 Ent - but there is definitely one in the \Binn directory which I assume is the one that Tivoli can never find!!
Have you tried including the path where sqlcmd.exe is in your batch file? Plus, check the permissions/ACLs on the sqlcmd.exe file as well as the cmd.exe file on %SystemRoot%\system32 (don't ask why, too) Make sure the local user account has Execute permissions on both files
I did try allof the above, but still nojoy. A little more digging into the problem has thrown up a tad more evidence. As the .sql file called by SQLCMD.exe via the .bat file carries out transaction log backups, the batch file has >> redirection to a log file of its progress, which receives the stats back from the BACKUP LOG command. Now, this log fileis ful of entries every 15 minutes as per schedule up until 07:2- on April 1st. So this did work, indeed it worked fine for its first 4 days of running. Now there are 4 of these batch files called by Tivoli and they all worked up until some catastrophic event stopped them from finding SQLCMD. There is nothing to indicate what the event was in the SQL Server logs, or a profiler trace that was running on vaious assorted events that day which also shows nothing (does show lots of nice SQLCMD events up till 01/04 though which was nice) Anyway, I've requested the archive of the windows event logs for that day to see if that may clue me what was done to the server that resulted in these jobs failing - Anybody out there think what it might be?