Recently, I had to restore StarTeam 2005 on a Windows 2003 Server.
An out-of-the-box install using MSDE 2000 does not want to run as a service.
This post shows you how I solved that problem. Read the rest of this entry »
Archive for the ‘Database Development’ Category
StarTeam 2005 on Windows 2003 Server using MSDE: service dependencies
Posted by jpluimers on 2010/08/24
Posted in Database Development, Development, MSDE 2000, Power User, Software Development, Source Code Management, SQL Server, SQL Server 2000, StarTeam, Windows, Windows Server 2003 | Leave a Comment »
SQL Sever: batch files to find instances and sqlcmd.exe/osql.exe
Posted by jpluimers 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.
As for preferring SQLCMD: [WayBack] sql server – What are the differences between osql, isql, and sqlcmd? – Stack Overflow
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:
- Clearing an environment varible by asigning an empty string to it
(set sqlcmd=) - 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. - Splitting the body of the for loop over multiple lines using parenthesis ().
- 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) - 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:
- 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. - Use the ampersand (&) to run two commands on one line
(the set sqlinstance=%2 and set sqlservice=%1). - the sc query command, which queries the Service Controller for the configured services
- The use of call :bare-service to call a subroutine at label :bare-service inside a for loop
- 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
Share this:
Posted in CommandLine, Database Development, Development, Software Development, SQL Server | 8 Comments »
10 years of Firebird
Posted by jpluimers on 2010/08/01
Has it been that long ago? Yes, yesterday, Firebird celebrated its 10th anniversary as an open source project.
It was stable for almost 2 years, and then Firebird 1.0 was released more than 8 years go.
The next version, 1.5, also took less than 2 years, and was released more than 6 years ago.
Version 2.0 was a major release, and took a bit longer: 2 years ago.
Version 2.5 is taking the same speed as 1.5 did, and is about to be released.
Work has already begun on 3.0, and a lot of new features are in the pipeline, including a new ODS.
–jeroen
Via: Firebird – The RDBMS that’s going where you’re going.
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
CodePlex: choosing a license
Posted by jpluimers on 2010/07/29
One of the toughest parts on creating a new [Archive.is] CodePlex project is choosing a license.
As Jeff Attwood wrote a couple of years ago, choosing a license – any license – is important, because if you don’t, you declare an implicit copyright without explaining how others could use your code.
In addition to that, Jeff published a list of licenses with a one-line phrase describing them, so it becomes easier to start making a choice.
Last year, ShreevastaR extended that list in his answer to this StackOverflow.com question on CodePlex licensing.
Brian Campbell did the same a few months later on another StackOverflow question about CodePlex licensing.
There are many more StackOverflow.com threads like those 2, and they give similar results.
The reason I want to put up a CodePlex project, is to put my sample code for conferences, blog articles and course examples on-line so they are easier to share with other people.
Most is from Visual Studio or Delphi projects using languages C#, VB.NET and Delphi.
Some of it are batch-files, XSD, XSLT, or other small snippets to get stuff working. Read the rest of this entry »
Share this:
Posted in .NET, Access, CodePlex, Database Development, Delphi, Development, Firebird, InterBase, Software Development, SQL Server | 4 Comments »
SQL Server: conditionally performing a DROP TRIGGER
Posted by jpluimers on 2010/07/19
Sometimes you need to drop a trigger, and depending on your SCM, your database might not actually have it.
Many people start blinding querying the system tables for this (a few of those examples are revealed by this Google search query).
This might be caused by old Microsoft documentation showing this as an example back in SQL Server 2000.
This is not needed for DML triggers (that react on data changes), querying the system tables is only needed for DDL triggers (that react on DDL actions).
For DML triggers (the vast majority!), you can use OBJECT_ID in stead. Read the rest of this entry »
Share this:
Posted in Database Development, Development, SQL Server | Leave a Comment »
SQL Server: SELECT multiple declared variables
Posted by jpluimers on 2010/07/14
Declared @local_variables and parameters in stored procedures are a very handy feature of SQL Server.
They allow for local storage, and for passing information around without storing them in your database first.
You can assign values by using both SET @local_variable, and SELECT @local_variable.
Note that these are different than a regular SET or regular SELECT.
In addition, SET @local_variable and SELECT @local_variable are different too.
Though Microsoft recommends using SET, there are a differences between SET and SELECT, which can make SELECT a lot faster in loops.
Assigning multiple @local_variables in a single SELECT query
There seem to be no examples (or very few: as I could not find them) about using the SELECT @local_variable to select values into multiple @local_variables from a query.
This can be very useful, so here is a small example: Read the rest of this entry »
Share this:
Posted in Database Development, Development, SQL Server | Leave a Comment »
SQL Server 2005 script to restore .BAK file to a database in a specific directory
Posted by jpluimers on 2010/07/13
Recently I needed a way to automate some testing on SQL Server 2005.
The easiest way was to start with restoring some predetermined database state from a .BAK file.
Hence I wrote the script below.
Next to SQLCMD, this script uses SubInACL, which you can download here (don’t worry that version seems to be only for Windows 2000..2003: it still works in Windows 7).
Microsoft does not have much documentation (except the built-in by calling “SubInACL /help /full”.
But this SubInACL page has some more insight on how to use it. Read the rest of this entry »
Share this:
Posted in Database Development, Development, SQL Server | Leave a Comment »
small script to list SQL Server databases and their data/log files
Posted by jpluimers on 2010/06/29
For my own memory: a small script to list SQL Server databases and their data/log files.
"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name from sys.databases" "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name,filename from model.sys.sysfiles" "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name,filename from master.sys.sysfiles" "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "exec sp_msforeachdb 'select ''?'',name,filename from ?.dbo.sysfiles'"
SQL Server maintains a list of databases in the system table sys.databases.
It also has an unsupported procedure sp_msforeachdb that you can use to investigate for instance performance issues.
In the above example, sp_msforeachdb just builds a statement (filling the ? with the database name) to execute.
–jeroen
Share this:
Posted in Database Development, Development, SQL Server | Leave a Comment »
SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed
Posted by jpluimers on 2010/06/25
Recently, I had to configure a pretty up-to-date Windows XP SP3 VM with SQL Server 2005 for a proof of concept.
The SQL Server 2005 setup failed consistently on installing MSXML 6.
I tried making the XP VM really up-to-date.
But the SQL Server 2005 setup still failed at the same reason.
In the end it appeared that the MSXML 6 install that is included in the SQL Server 2005 install barfs when MSXML 6 SP2 is already installed. Read the rest of this entry »
Share this:
Posted in Database Development, Development, SQL Server | 5 Comments »
SQL Server 2008 R2 got to RTM last week
Posted by jpluimers on 2010/04/29
After the excitement of .NET 4.0 and all the tools around it, I totally forgot to mention that SQL Server 2008 R2 got to RTM.
If you are an MSDN subscriber you can download it there.
If you are not, you can download the Microsoft SQL Server 2008 R2 RTM – Express with Management Tools.
Brent Ozar wrote a pretty nice FAQ on the new SQL Server 2008 R2 features, much better than the “what’s new” and press release by Microsoft.
Feature conclusion so far:
- nice new stuff for Business Intelligence
- no new stuff on the engine and T-SQL front
- watch your licensing when doing virtualization; the non-R2 is far cheaper for that
–jeroen (who just turned 41 today)
Share this:
Posted in .NET, Database Development, Delphi, Development, Software Development, SQL Server | 2 Comments »
The Wiert Corner - irregular stream of stuff 