January 17, 2003 at 6:32 pm
Is there a way to run a BAT file on my hard drive using a SQL command line?
January 17, 2003 at 6:38 pm
You have to either:
a) use xp_cmdshell
b) schedule a SQL Server job with a CmdExec step.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 18, 2003 at 7:38 am
But don't forget!
xp_cmdshell is running on your server and not on your workstation. So if your BAT file are located on your WS your TSQL wont work unless youwill decribe your UNC path the the data
Bye
Gabor
January 18, 2003 at 6:41 pm
True, but most every T-SQL command would be expected to run wherever the "server" resides.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 22, 2003 at 3:17 pm
well, i am running it from the server. although xp_cmdshell gives me an error message that says that only administrators can run it.
the reason i'm trying to do it this way in the first place is that i am a database administrator, but not a system administrator; and the scheduling of jobs is a real hurdle for non-system administrators.
i have a procedure with about 40 steps in it, most of which are standard SQL commands. I can schedule those with T-SQL; but there are a few BAT files that need to be run for this entire procedure to complete, and so I need a way to call these files in T-SQL.
January 23, 2003 at 12:30 pm
in order to run xp_cmdshell as a non admin you (your server admin) have to set the proxy-windows-nt-account in your sql-server.
1. create a win-nt account
2. enter this nt-account as proxy-account (EM->management->sql agent)
January 23, 2003 at 3:40 pm
I do stuff similar to this - multiple steps against the file system then multiple SQL steps. I use VBscript on a separate machine under a login that has the relevant file system authority and with SQL authority to update the appropriate tables.
The steps are like this:
1. look for files on the file systems, test for date (make sure they are from the right run)
2. open an adodb connection to the server
3. loop through the files (there are 6) and, using a subroutine add the data to the 6 relevant tables.
4. still using the connection, run a stored proc which then loads some of the new data into another table (an 'enrichment' process).
If steps fail I can log them and also generate emails to indicate the failure (I use blat.exe because the machine doesn't have any email clients).
James
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply