1

I would like to enhance an existing bat file to log execution duration to a SQL Server table. The current bat file has a single line that calls a command line utility.

I thought I would leverage something like this, SQL Statements in a Windows Batch File. Pseudo code:

StartTime = Now()
hyperioncommandlineshell.cmd /a:parm1 /b:parm2 /c:parm3
sqlcmd.exe -b -S myhost -E -d mydatabase -Q "Insert Into MyTable Values (Current_Timestamp, 'MyProcess', Now() - StartTime)" -W

Some questions:

  1. The server that this bat file runs on doesn't have the SQL tools, and I see from this post that it does require an installation (you can't just copy over the sqlcmd.exe file). This will meet with resistance. Is there another way to execute a SQL statement from a batch file without having to install software?

  2. I don't have experience with BAT files. Can someone provide guidance on how to get the duration of a process (like grabbing the start time, and calculating the difference at the end)?

I would probably try using another tool I'm more familiar with, but I'm trying to do this in bat so that the change only affects one existing object, and doesn't require additional objects.

Community
  • 1
  • 1
user1886039
  • 13
  • 3
  • You need to address the "resistance" first: you haven't mentioned anything about your versions of SQL Server or Windows or why sqlcmd.exe is not allowed, but the bottom line is that you can't connect to a SQL Server without client libraries and you can't execute a SQL script without some sort of tool. You can write [your own tool](http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp) to run SQL scripts but that seems pointless and riskier than using sqlcmd.exe in the first place. – Pondlife Dec 07 '12 at 17:42

1 Answers1

1

Windows computers come with ODBC drivers already installed, so you likely have an ODBC driver for SQL Server. If so, then you might be able to get Microsoft's osql utility to run T-SQL statements from DOS. Here's the docs for it on MSDN:

http://msdn.microsoft.com/en-us/library/aa214012(v=SQL.80).aspx

It was designed for SQL Server 2000, so there may be some issues connecting to later versions of SQL Server, but it is worth a try. If it works, then you won't have to install anything special to connect to your SQL server (though you may need to create an ODBC data source name for the server...). From Windows Vista+, click Start and type ODBC to open the ODBC Data Source Editor.

Using SQLCMD will require that you install the Native Client, or at least SNAC (discussion thread: http://us.generation-nt.com/answer/how-install-only-sqlcmd-exe-utility-help-87134732.html) to simply run SQLCMD without installing the entire Native Client (though, SNAC still needs to be installed). I haven't heard of SNAC before, so that will take a bit of research. I assume installing anything will be met with the same resistance, so if you can overcome that resistance, installing the Native Client is probably your best bet.

As for the elapsed time. You can use %DATE% %TIME% to get the current date/time. So you could use something like the following to capture the start time, run your process and then capture the end time -- posting them all to the database:

set StartTime=%DATE% %TIME%
hyperioncommandlineshell.cmd /a:parm1 /b:parm2 /c:parm3
set EndTime=%DATE% %TIME%
sqlcmd.exe -b -S myhost -E -d mydatabase -Q "Insert Into MyTable Values ('%StartTime%', 'MyProcess', '%EndTime%')" -W

You won't be able to do the StartTime - EndTime computation with DOS itself, but you can store both the start and end times in the table an use SQL to do it.

The format of %DATE% and %TIME% are based on the format that the machine is setup to use. You can type echo %DATE% %TIME% at a DOS prompt to see how it is formatted for you. You will likely have to store these values in varchar fields since the format may not automatically convert to a datetime value. If it does automatically convert, then you could do the computation in the SQL statement from DOS, like this:

sqlcmd.exe -b -S myhost -E -d mydatabase -Q "Insert Into MyTable Values ('%EndTime%' - '%StartTime%', 'MyProcess')" -W

(FYI - I used your pseudo-code for all examples, so nothing is tested.)

James L.
  • 9,384
  • 5
  • 38
  • 77
  • Whether or not you can copy just osql.exe to the server is a legal question, not a technical one. I don't know if Microsoft allows it to be redistributed alone or bundled with a custom application, or if they require you to install a full SQL Server client. Unfortunately licensing issues are often rather cryptic, but they still have to be considered. See also [this question](http://stackoverflow.com/questions/4128909/run-osql-on-systems-which-do-not-have-sql-server-installed) although unfortunately there is no source quoted. – Pondlife Dec 07 '12 at 21:29
  • Thanks James L, this is very helpful. When I went to find the osql utility, a microsoft.com redirect took me to the page for SSEUtil.exe. This utility doesn't require an installation, much like osql. My date time format is Fri 12/07/2012 15:45:07.96, which does insert nicely into a datetime data field. Getting closer! – user1886039 Dec 07 '12 at 21:48