Can't run SQLFULL script from SQL agent

[osi9400]

I created a SQLFULL.CMD script for a full backup. The script runs fine when exectud from within windows. However, it fails when ran from a SQLagent or a query. I have enabled xp_cmdshell.

In the query window:
This works: exec xp_cmdshell 'ping localhost';
This fails with "null": exec xp_cmdshell 'C:\Progra~1\Micros~1\MSSQL.1\MSSQL\Backup\sqlfull.cmd';

I am not getting any info in the event log or the dsierror.log file to help troubleshoot.

The baffling part is that I can run it withing Windows but not SQL. any ideas?

 

[osi9400]

wow that looks strange. It should read:

exec xp_cmdshell 'C:\Progra~1\Micros~1\MSSQL.1\MSSQL\Backup\sqlfull.cmd';

 

[osi9400]

I can assure there is no space in the actual query. I'm not sure why there is one when I paste here. If the path is wrong (I have deliberately mistyped it) then I get a path not found error.

\sqlfull .cmd';

That is a space, right? In between the red and the blue? I can't imagine that would work, if it is.

 

[osi9400]

Remember, the script runs when exectued directly in Explorer, but not as a query in Studio.


set tsm_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_dir=C:\Progra~1\Micros~3\MSSQL.1\MSSQL\Backup

date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log

%tsm_dir%\tdpsqlc backup * full /tsmnode=zzzzzzzzz /tsmp=xxxxxxxx /tsmoptfile=%tsm_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

Tell you what...how about you post the contents of sqlfull.cmd? Maybe there's a variable that isn't being defined, or something.

 

[moon-buddy]

Remember, the script runs when exectued directly in Explorer, but not as a query in Studio.


set tsm_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_dir=C:\Progra~1\Micros~3\MSSQL.1\MSSQL\Backup

date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log

%tsm_dir%\tdpsqlc backup * full /tsmnode=zzzzzzzzz /tsmp=xxxxxxxx /tsmoptfile=%tsm_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

Try running the SQL backup script by not invoking the separate shell as called by your script.

 

[osi9400]

I'm not a DBA. How do you run external scripts in a SQL Agent without xp_cmdshell?

Also, check out this article: http://support.microsoft.com/kb/q255749/

Again, I know next to nothing about xp_cmdshell...but a Google search showed me a lot of forums with users having flaky little problems with it.

 

[moon-buddy]

I'm not a DBA. How do you run external scripts in a SQL Agent without xp_cmdshell?

Just open a DOS prompt and run the SQL script minus the XPcmd_shell...

 

[rore]

Hi,
How do you know that execxp_cmdshell'ping localhost' works? Any log?

Try this:
exec xp_cmdshell 'start /w C:\Progra~1\Micros~1\MSSQL.1\MSSQL\Backup\sqlfull.cmd';

Rudy

Edit: without the space after sqlfull.