The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140417-Windows-8.1-Update-says-it-failed-but-it-succeeded

    20140417-Windows-8.1-Update-says-it-failed-but-it-succeeded--Windows-Help-and-Support--cannot-find-any-info--how-surprising-it-never-does

    20140329-VMware-vSphere-Client-4.1-retry-with-compatibility-settings

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,251 other followers

SQL Sever: batch files to find instances and sqlcmd.exe/osql.exe

Posted by Jeroen Pluimers on 2010/08/19

In my VM’s, I often run different instances and/or versions of SQL Server.

Finding the right instance of SQL server, and the right version of SQLCMD.EXE / OSQL.EXE can be a pain.

That’s why I have written the two batch-files shown below for that.
They are not perfect, but they do work for me, and show a few interesting batch-file tricks.

find-sql-sqlcmd.bat

This finds the most up-to-date SQLCMD.EXE (or OSQL.EXE for SQL Server 2000) and puts the location of it in the sqlcmd environment variable.

@echo off
rem find the highest version of SQLCMD.EXE or OSQL.EXE and put it in the sqlcmd environment variable
rem this prefers SQLCMD.EXE over OSQL.EXE

set sqlcmd=

for %%d in ("%ProgramFiles%", "%ProgramFiles(x86)%") do for %%v in (80, 90, 100) do for %%f in (OSQL, SQLCMD) do (
call :sqlcmdtest "%%~d\Microsoft SQL Server\%%v\Tools\Binn\%%f.EXE" %1
)

if !!==!%sqlcmd%! for %%f in (OSQL, SQLCMD) do (
call :find-on-path %%f.EXE
)

if !%1!==!! echo SQLCMD: %sqlcmd%
goto :exit

rem be carefull not to specify the .EXE in the %%f FOR loop-expression, otherwise it tries to dine SQLCMD.EXE and OQSL.EXE in the current directory

rem http://msdn.microsoft.com/en-us/library/ms178653.aspx
rem 80 = SQL Server 2000
rem 90 = SQL Server 2005
rem 100 = SQL Server 2008 and 2008 R2

:find-on-path
set sqlcmd=%~f$PATH:1
if not ""=="%sqlcmd%" set sqlcmd="%sqlcmd%"
goto :exit

:sqlcmdtest
if exist %1 if !%2!==!! echo found %1
if exist %1 set sqlcmd=%1

:exit

Tricks used:

  1. Clearing an environment varible by asigning an empty string to it
    (set sqlcmd=)
  2. Nested for loops
    Note that when working with string literals in for loops, you should not put any file extension on it (if you do, the for loop will only match filenames).
    So: In stead of having (OSQL.EXE, SQLCMD.EXE), you see (OSQL, SQLCMD) in the for loop, and the .EXE is concatenated later on.
  3. Splitting the body of the for loop over multiple lines using parenthesis ().
  4. Put the value you prefer at the end of the for loop
    (so the last value that is found will be put in the sqlcmd environment variable)
  5. The mapping of SQL Server verions to numbers used in the directories (see also the documentation of the SQL Server sp_dbcmptleve stored procedure):
    80 = SQL Server 2000
    90 = SQL Server 2005
    100 = SQL Server 2008

find-sql-instance.bat

This batch file finds the SQL Server instances on the local machines from the naming of the SQL Server services that are running.
Note that it won’t work if you choose custom names for your SQL Server services (but that will probably break a lot of other stuff out there as well).

@echo off
rem find best matching instance of SQL Server on this machine
set sqlinstance=
set sqlservice=
for /f "usebackq tokens=1,2,3 delims=:$" %%i in (`sc query`) do (
rem %%j is " MSSQL" because we dropped space as a delimiter
if "%%i"=="SERVICE_NAME" call :bare-service %%j %%k
)
if !%1!==!! echo SQL Instance=%sqlinstance%
if !%1!==!! echo SQL Service=%sqlservice%
goto :exit

:bare-service
rem %1 equals "MSSQL" because of the command-line parsing trims spaces
rem the order is important: we favour named instances over unnamed:
if "%1"=="MSSQLSERVER" call :process-instance %1 .
if "%1"=="MSSQL" call :process-instance MSSQL$%2 .\%2
goto :exit

:process-instance
if !%1!==!! echo found service "%1" providing instance "%2"
if "%sqlinstance%"=="" set sqlinstance=%2& set sqlservice=%1
for /f "usebackq tokens=1,2,3,4" %%s in (`sc query %1`) do (
if "%%s"=="STATE" if !%1!==!! echo state of %1 is %%v
)
goto :exit

:exit

Tricks used:

  1. use the for /f command to parse the output of sc query;
    note the use of the usebackq, tokens and delims parameters in this command,
    and the use of `backquotes` to parse the output of `sc query` which outputs fragments like shown below.
  2. Use the ampersand (&) to run two commands on one line
    (the set sqlinstance=%2 and set sqlservice=%1).
  3. the sc query command, which queries the Service Controller for the configured services
  4. The use of call :bare-service to call a subroutine at label :bare-service inside a for loop
  5. The fact that a leading space in for loop variable %%j is trimmed when calling the :bare-service label.

sc query sample fragment:

SERVICE_NAME: MSSQL$SQLEXPRESS
DISPLAY_NAME: SQL Server (SQLEXPRESS)
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0	(0x0)
        SERVICE_EXIT_CODE  : 0	(0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

SQLRUN – tying it together

My final batch-file ties both together:

set sqlrun=
call %~dp0sql-find-sqlcmd.bat %*
call %~dp0sql-find-instance.bat %*
if not !!==!%sqlcmd%! if not !!==!%sqlinstance%! set sqlrun=%sqlcmd% -S %sqlinstance% -E
if !%1!==!! echo SQLRUN=%sqlrun%

The output of that batch-file is like this:

found "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE"
found "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE"
found "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE"
found "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SQLCMD: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
found ".\SQLEXPRESS"
state of service MSSQL$SQLEXPRESS with SQL Server instance .\SQLEXPRESS is RUNNING
found "MSSQLSERVER" (.)
state of service MSSQLSERVER with SQL Server instance . is RUNNING
SQL Instance=.\SQLEXPRESS
SQLRUN="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -E

Hope this helps a few people.

–jeroen

8 Responses to “SQL Sever: batch files to find instances and sqlcmd.exe/osql.exe”

  1. Thomas Pfister said

    Jeroen,
    thanks for the script, looks great!

  2. Steven Kamradt said

    Jeroen,

    The first batch file find-sql-sqlcmd.bat actually finds the instance not the sqlcmd file specified.

  3. [...] a couple of batch files similar to find-sql-cmd.bat to find the location of the tools, and find which SQL Server instance to [...]

  4. Thomas Lee said

    Great script, but would like to know, what if the machine have more than one instance?

    • jpluimers said

      It prefers the default instance (.) over named instances.
      If there are only named instances, it takes the first one it finds.
      –jeroen

  5. Thomas Lee said

    Understood, so I have just using the :process-instance, then each service identified as a SQL Server instance it will run the script to it.

    Now just thinking how to handle, if the user executing batch file do not have privilege to either one or more instance, how to capture the return code from OSQL/SQLCMD, so can alert the user try to use SQL Account to login to specific instance and try again.

    • jpluimers said

      That requires parsing magic similar to what the batch file already does.

      It is a matter of following the patterns I created,and a lot of careful testing using echo to send intermediate results to the console.
      –jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 1,251 other followers

%d bloggers like this: