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

Migrating SQL Server 2000 ISQLW Utility to SQL Server 2008 R2 SSMS command-line parameters (SQL Query Analyzer; SQL Server Management Studio)

Posted by Jeroen Pluimers on 2012/04/17

If you followed my blog, you probably already guessed that I’m assisting a client to prepare for a big SQL Server 2000 to SQL Server 2008 R2 migration. When not, you know now :)

I’m a fan of commandline, tools, and preconfigured settings. Which means that I’m in the midst of reconfiguring all my shortcuts to SQL Server 2000 tools to SQL Server 2008 equivalents.

When inspecting and changing LNK shortcut files, two tools are important:

  • dumplnk: dumps a shortcut lnk file from the commandline
  • shurtcut: creates a shortcut lnk file from the commandline

One of tools I many shortcuts for is the ISQLW aka SQL Query Analyzer, which I use far more than the SQL Server Enterprise Manager (more on SSEM in a future post).

Depending on the mode of authentication you use to connect to your SQL Server, there basically are two forms of shortcuts:

  • SQL Server Authentication (using SQL Server username and password):
    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe" /S"servername[\instancename]" /U"username" /P"password"
  • Windows Authentication (using the credentials of the currently logged in windows user):
    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe" /S"servername[\instancename]" /E

The former is less secure (so better to only store those shortcuts in a place that no other users can access).

From the ISQLW.exe syntax documentation:

Syntax

isqlw
[-?] |
[
[-S server_name[\instance_name]]
[-d database]
[-E] [-U user] [-P password]
[{-i input_file} {-o output_file} [-F {U|A|O}]]
[-f file_list]
[-C configuration_file]
[-D scripts_directory]
[-T template_directory]
]

The parameters /S, /U, /P and /E are very similar to the -S, -U, -P and -E ones from SSMS.exe (SQL Server Management Studio) with one distinction: there MUST be a space between each parameter and the value:

  • SQL Server Management Studio (using SQL Server username and password):
    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S "servername[instancename]" -U "username" -P "password"
  • SQL ServerManagement Studio (using credentials for the currently logged in windows user):
    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S "servername[instancename]" -E

Syntax

Ssms
[scriptfile] [projectfile] [solutionfile]
[-S servername] [-d databasename] [-U username] [-P password]
[-E] [-nosplash] [-?]

Note that a non-standard port number must be specified as “servername,portnumber”.

The default path for SSMS.exe is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe, which is outside of the SQL Server binn directory.

ISQLW.exe is in the same directory as SQLCMD.exe and OSQL.exe (OSQL has been deprecated for ages, but SQL Server 2000 didn’t have SQLCMD.exe). I find the location of SQLCMD.exe (or OSQL.exe) using this batch file (that works for all SQL Server versions I tested so far):

@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=

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
rem 110 = SQL Server 2012

rem prefer the right-most element from the lists in the for loops
for %%d in ("%ProgramFiles%", "%ProgramFiles(x86)%") do for %%v in (80, 90, 100, 110) do for %%f in (OSQL, SQLCMD) do (
call :sqlcmdtest "%%~d\Microsoft SQL Server\%%v\Tools\Binn\%%f.EXE" %1
)

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

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

call :setSqlDir

if !%1!==!! echo SQLCMD: %sqlcmd%
if !%1!==!! echo SQLDIRECTORY: %sqldirectory%

goto :exit

:setSqlDir
::  ~dp does not work for regular environment variables
::  ~dp only works for batch file parameters and loop indexes
  for %%d in (%sqlcmd%) do set sqldirectory="%%~dpd"
goto :exit

: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

–jeroen

via: isqlw Utility.

5 Responses to “Migrating SQL Server 2000 ISQLW Utility to SQL Server 2008 R2 SSMS command-line parameters (SQL Query Analyzer; SQL Server Management Studio)”

  1. IL said

    After Upgrading the Database Engine
    http://msdn.microsoft.com/en-us/library/bb933942(v=SQL.105).aspx
    dbcc updateusage(0)
    exec sp_updatestats
    alter database set page_verify checksum
    –consider compatibility level
    –tracing for Deprecation, Errors and warnings
    –reconsider hints like FORCE ORDER
    –inplace upgrade tasks

  2. Sharepoint Foundation…

    [...]Migrating SQL Server 2000 ISQLW Utility to SQL Server 2008 R2 SSMS command-line parameters (SQL Query Analyzer; SQL Server Management Studio) « The Wiert Corner – irregular stream of Wiert stuff[...]…

  3. […] With lots of production database, I have a directory full of shorcuts that pre-fill server/database/… on the command-line. […]

  4. […] of the things I thought were missing from SSMS when coming from an ISQLW background was the “registered servers” […]

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: