Monday, November 08, 2010

SQL Server code-named 'Denali' introduces a powerful alternative to identity columns. There is a new CREATE SEQUENCE statement that can be used to create global sequence generators.

( on title to see more...)

Thursday, September 30, 2010

If you've gotten this message trying to drop a database in SQL Server:

Cannot drop database "DatabaseName" because it is currently in use.

then there is an easy solution. The error message tells you that there are users still connected to the database. Instead of figuring out who they are (with sp_who2/sys.sysprocesses) and executing numerous KILL commands, just set the database to single user mode:


This will disconnect all the users from DatabaseName and remove DatabaseName. From Books Online:

When SINGLE_USER is specified, one user at a time is allowed to connect to the database. All other user connections are broken.

SQL Server Management Studio executes precisely this command if you check "Close existing connections" checkbox when deleting a database.

Friday, September 10, 2010

UPSERT functionality refers to either updating table rows based on some search condition, or inserting new rows if the search condition is not satisfied. This is intuitively seen from the word UPSERT - a combination of UPDATE (needed rows are present) or INSERT (needed rows are not present).

The most common way to implement UPSERT requires multiple statements. Let's say we have a simple table A_Table with just two columns - a key column (Id) and a data column (Data).

    Id        INT    IDENTITY(1,1) NOT NULL,
    Data    VARCHAR(50)
INSERT INTO A_Table (data)

At this point we have one row in A_Table:

The traditional UPSERT will look like this:

-- UPDATE or INSERT based on SELECT search predicate

SELECT @key = Id
    -- Search predicates
    Data = 'data_searched'

    -- Update part of the 'UPSERT'
        Data = 'data_searched_updated'
    -- INSERT part of the 'UPSERT'
    INSERT A_Table (Data)
    VALUES ('data_searched')


SELECT * FROM A_TABLE                            

We'll see two rows:

Fortunately, the UPSERT functionality can be implemented in one statement using MERGE statement. MERGE first appeared in SQL Server 2008.

Here is one-statement equivalent to the multi-statement UPSERT above.

    (SELECT 'data_searched' AS Search_Col) AS SRC
    -- Search predicates
    ON A_Table.Data = SRC.Search_Col
    -- Update part of the 'UPSERT'
        Data = 'data_searched_updated'
    -- INSERT part of the 'UPSERT'
    INSERT (Data)
    VALUES (SRC.Search_Col);    

If we run this query on existing two-row A_Table, we'll get this table (second row was matched and updated):

Benefits of the MERGE statement over separate SELECT/INSERT/UPDATE include:

  1. Faster performance. The Engine needs to parse, compile, and execute only one query instead of three (and no temporary variable to hold the key).
  2. Neater and simpler T-SQL code (after you get proficient in MERGE).
  3. No need for explicit BEGIN TRANSACTION/COMMIT. MERGE is a single statement and is executed in one implicit transaction.
  4. Greater functionality. MERGE can delete rows that are not matched by source (SRC table above). For example, we can delete row 1 from A_Table because its Data column does not match Search_Col in the SRC table. There is also a way to return inserted/deleted values using the OUTPUT clause.

For more information, consult MERGE help page in SQL Server Books Online.

Tuesday, June 01, 2010

SQL Server 2008 R2 setup program supports installation via a configuration file (but not for Express editions). The file is created by running the normal GUI setup and cancelling it on the Ready To Install dialog.

ReadyToInstall SQL Setup dialog

All selected configuration settings will be saved in ConfigurationFile.ini file located in the Setup Bootstrap directory for the current installation, which is:

%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMMSS>

For example, for installation started on June 01, 2010 at 5:32:15pm:

C:\ProgramFiles\Microsoft SQL Server\100\Setup Bootstrap\Log\20100601_173215\ConfigurationFile.ini

Then use /CONFIGURATIONFILE setup.exe parameter to specify answers from an existing configuration file. To have a fully unattended install, you also need to pass /Q (or /QS), /IACCEPTSQLSERVERLICENSETERMS, and startup account password(s) if not using a system account like NT AUTORITY\NETWORK SERVICE. For example:

setup.exe /Q /CONFIGURATIONFILE="C:\SQLSetup\ConfigurationFile.ini"

For more information, see How To: Install SQL Server 2008 R2 from the Command Prompt.

Monday, May 17, 2010

Office 2010 has a script to find out licensing information and activate Office 2010 from command-line.

Script name: "%ProgramFiles%\Microsoft Office\Office14\ospp.vbs"

or, for 32-bit Office installation on 64-bit Windows:

%ProgramFiles(x86)%\Microsoft Office\Office14\ospp.vbs

It must be run under cscript: cscript %ProgramFiles%\Microsoft Office\Office14\ospp.vbs.

Find out license information for installed product keys.

cscript %ProgramFiles%\Microsoft Office\Office14\ospp.vbs /dstatus.

This will tell you whether any Office products are activated or not.

Activate all Office products on local machine.

cscript %ProgramFiles%\Microsoft Office\Office14\ospp.vbs /act.

Other options.

Here is what cscript %ProgramFiles%\Microsoft Office\Office14\ospp.vbs /?


cscript ospp.vbs /Option:Value ComputerName User Password
     ComputerName: Name of remote computer. If a computer name is not passed local computer is used.
     User: Account with required privilege on remote computer.
     Password: Password for the account. If a User account and password are not passed current credentials are used.
     Value: Required for outlined options.
Global /Options Description
/act Activate installed Office 2010 product keys.
/inpkey:value Install a product key (replaces existing key) with user-provided product key. Value parameter applies.
/unpkey:value Uninstall an installed product key with user-provided partial product key (as displayed by the /dstatus option). Value parameter applies.
/inslic:value Install a license with user-provided path to the .xrm-ms license. Value parameter applies.
/dstatus Display license information for installed product keys.
/dstatusall Display license information for installed licenses.
/dhistoryacterr Display MAK/Retail activation failure history.
/dinstid Display installation ID for offline activation.
/actcid:value Activate product with user-provided confirmation ID. Value parameter applies.
/puserops Permit administrator-privileged license operations including internet/telephone activation by standard users.
/duserops Deny administrator-privileged license operations including internet/telephone activation by standard users.
/ddescr:value Display the description for a user-provided error code. Value parameter applies.
KMS client /Options Description
/dhistorykms Display KMS client activation history.
/dcmid Display KMS client machine ID (CMID).
/sethst:value Set a KMS host name with user-provided host name. Value parameter applies.
/setprt:value Set a KMS port with user-provided port number. Value parameter applies.
/remhst Remove KMS host name (sets port to default).
OSPPSVC /Options Description
/osppsvcrestart Restart Office Software Protection Platform service.
/osppsvcauto Set Office Software Protection Platform service startup type to automatic.
Token /Options Description
/dtokils Display installed token-based activation issuance licenses.
/rtokil:value Uninstall an installed token-based activation issuance license with user-provided license id (as displayed by the /dtokils option). Value parameter applies.
/stokflag Set token-based activation only flag.
/ctokflag Clear token-based activation only flag.
/dtokcerts Display token-based activation certificates.
/tokact:value1:value2 Token activate with a user-provided thumbprint (as displayed by the /dtokcerts option) and a user-provided PIN (optional). Value parameter applies.

Thursday, May 13, 2010

Find out when the current license ends.

In a command-line window, run:

slmgr /exp

The output will be something like:

Windows license expiration date

( on the title to see more...)

Friday, May 07, 2010

As a follow up to my Windows 7 unattended installation post, here is an example of how to install Windows Server 2008 R2 using unattend.xml.

The basic answer file settings are the same, except the server version has Roles to install. Roles are mapped to packages and specified under <servicing><package> tags. To me, the most important Roles are Hyper-V and IIS with ASP.NET, so my example answer file installs these packages.

Note: ASP.NET Role Service of IIS has several dependencies. If you just Enable it at the IIS-ApplicationDevelopment level in System Image Manager, you will get the following validation error:

Windows Feature is enabled but one or more of its dependencies have not been enabled in the answer file. Packages/Foundation/amd64_Microsoft-Windows-Foundation-Package_6.1.7600.16385__31bf3856ad364e35_/IIS-WebServerRole/IIS-WebServer/IIS-ApplicationDevelopment/IIS-ASPNET

ASP.NET has dependencies on:


packages. IIS-ASPNET should be selected under these as shown on the picture.

IIS-ASPNET in System Image Manager

 ASP.NET also needs .NET Framework, which is installed by NetFx3 Role/package.

How to include custom drivers.

Server OS installation frequently needs drivers that not included in the standard OS image. For example, a RAID controller is not recognized without an external driver. Fortunatelly, there are two places in an answer file to specify the location(s) for both setup and installed OS drivers.

1. To specify driver for setup, include <PathAndCredentials> tag under Microsoft-Windows-PnpCustomizationsWinPE component. There can be multiple such tags with different wcm:keyValue attributes.

<component name="Microsoft-Windows-PnpCustomizationsWinPE" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="" xmlns:xsi="">
    <!-- Custom drivers for setup. -->
    <!-- Put RAID or Network driver package in the directory pointed by <Path>. -->
        <PathAndCredentials wcm:action="add" wcm:keyValue="Path1">

2. To specify drivers for installed OS image, use the same tag, but under Microsoft-Windows-PnpCustomizationsNonWinPE component.

<component name="Microsoft-Windows-PnpCustomizationsNonWinPE" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35"  language="neutral" versionScope="nonSxS" xmlns:wcm="" xmlns:xsi="">
    <!-- Custom drivers for the installed OS image. -->
        <PathAndCredentials wcm:action="add" wcm:keyValue="Path1">

One point to keep in mind - if the storage controller is not recognized by setup and, consequently, the setup does not see and hard drives, the plugged in USB key containing the answer file will be assigned Disk 0. This means it will be visible to setup under letter C:. Examples above store drivers on the same USB key that contains the answer file, under \Drivers directory.

Specify OS image for installation (Full or Core).

Several Windows Server 2008 R2 editions include two installation options: Full (with GUI) and Core (no GUI, just command-line). Both of these images are contained on the installation media, in \sources\install.wim file. If installation type is not specified, setup will throw a dialog.

You can list images in any .wim file with "imagex /info install.wim" command included in WAIK. However, a quicker way to find the image name is look at Catalog .clg file names in the \sources directory. They will have the image name appended after the "_" character. For example, on Server Enterprise media, there are the following catalog files:

Windows Image Catalog files

Therefore, the two Windows images available for installation are:

  1. Windows Server 2008 R2 SERVERENTERPRISE.
  2. Windows Server 2008 R2 SERVERENTERPRISECORE.

The needed Windows image can be specified in an answer file under Microsoft-Windows-Setup | ImageInstall | OSImage | InstallFrom | MetaData location. The <Key> tag of the Metadata should be /IMAGE/NAME, and the <Value> tag contains the image name. For example:

<settings pass="windowsPE">
    <component name="Microsoft-Windows-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="" xmlns:xsi="">
                <!-- Specify image to install.
                For Server Core installation, the Value is "Windows Server 2008 R2 SERVERENTERPRISECORE" -->
                    <MetaData wcm:action="add">
                        <Value>Windows Server 2008 R2 SERVERENTERPRISE</Value>

Disable opening of Server Manager at Logon.

If you don't want the Server Manager GUI to appear on first logon, there is a specialize pass setting for that:

<component name="Microsoft-Windows-ServerManager-SvrMgrNc" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="" xmlns:xsi="">

Disable Initial Configuration Tasks at Logon.

This specialize pass setting will not open Initial Configuration Tasks:

<component name="Microsoft-Windows-OutOfBoxExperience" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35"
language="neutral" versionScope="nonSxS" xmlns:wcm=""

Disable Internet Explorer Enhanced Security Configuration (ESC).

There is a specialize pass setting to disable IE ESC (removing the annoying need to add every Internet site to Trusted Sites). The setting is different for Administrators and regular users.

<component name="Microsoft-Windows-IE-ESC" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35"
language="neutral" versionScope="nonSxS" xmlns:wcm=""

Join computer to a domain.

It is possible to automatically join the computer to a domain. Here is the specialize pass setting to join the computer to a sample "fabrikam" domain:

<component name="Microsoft-Windows-UnattendedJoin" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="" xmlns:xsi="">
            <!-- Specify user and password of the account that will join the computer to the domain. -->
            <Password>password (change!)</Password>
            <Username>user (change!)</Username>

Sample answer file.

Here is a sample unattend.xml file for Windows Server 2008 R2 Enterprise Full installation type. It includes the customizations above plus the following:

  • QuickEdit mode for cmd.exe.
  • File extensions shown in Windows Explorer.
  • Run command shown on the Start menu.
  • Administrative Tools shown on the Start menu.
  • cmd.exe pinned to Taskbar (Taskbar link).
  • Auto-logon (once) of Administrator.

(note: these will only work for the Administrator account, because it logs in first).

Roles installed: Hyper-V, IIS with ASP.NET.

Features installed:

    Role Administration Tools

        Hyper-V Tools

        Web Server (IIS) Tools  

    .NET Framework 3.5.1.

Important! The answer file will format Disk 1 and install the OS there. Please make sure this is the location you want to install to. Disk 1 will be the first storage controller disk if the storage controller requires a custom driver (i.e. it is not visible to the standard OS image).

You will also need to specify Administrator password in the appropriate (commented) places.

Download Autounattend.xml.

Tuesday, April 20, 2010

Today Microsoft announced release to manufacturing (RTM) of SQL Server 2008 R2. The main defining themes of this SQL Server release are:

  1. Self-service Business Intelligence for reporting and analysis. By installing PowerPivot for Excel 2010 add-in (a free download from Microsoft website), Excel ribbon adds a PowerPivot tab from which a PowerPoint window can be created to add and prepare data. PowerPivot data will be stored directly in the Excel .xlsx file (up to 2GB limit) alongside with Excel presentation objects (PivotTables, PivotCharts). Such an Excel workbook can be uploaded to Sharepoint 2010 with PowerPivot Services, automatically refreshed with data from external sources, and displayed in a web browser by Sharepoint 2010 Excel Services.
  2. Greater than 64-processor scalability. Up to SQL Server 2008, the product ran only on the maximum of 64 logical processors (partly because Windows Server itself did not support more than 64 logical processors). With Windows Server 2008 R2, the new Datacenter Edition of SQL Server 2008 R2 can run on up to 256 logical processors. There are TPC-E and TPC-H benchmarks published on servers with up to 96 cores (

Additional important features:

  1. Master Data Services. Organizations can create a single source of master data and maintain an auditable record of that data. Applications then use a central, accurate source of information. Master data is defined through Models that contain Entities (similar to data tables), which in turn contain Attributes (similar to table columns) and Members (similar to table rows). Members from different entities can be organized into Hierarchies and Collections.
  2. SQL Azure Cloud Database support by SQL Management Studio. SQL Server 2008 Management Studio did not fully support SQL Azure connection.
  3. StreamInsight complex event processing (CEP). This is querying stream data without storing it in a database (hence, low latency). Database's possible purpose here is to contain static reference data. For example, a power sensor can stream its readings to StreamInsight application that raises an alarm when power consumption exceeds limit values stored in a SQL database.

    StreamInsight server (standalone or DLL) is accessed from a .NET language (currently only C#) via LINQ. External events need to be standardized into StreamInsight's format by writing Input Adapters.

  4. Backup Compression in Standard Edition. Database Compression is included in the Enterprise Edition.

Pricing is adjusted upwards for Per Processor licensing, but not for CAL licensing. "Per Processor" still means per CPU socket, not CPU core like Oracle and IBM. However, the Enterprise Edition is now limited to 8 sockets (previously unlimited). Unlimited socket licensing is available on the new premium Datacenter Edition.

Friday, December 18, 2009

With growing popularity of netbooks and light notebooks without a DVD drive, it may seem challenging to install Windows OS on such machines. Fortunately, it is fairly easy to use a USB flash drive as the installation media.

( the title to read more...)

Thursday, December 17, 2009


Windows 7 can be installed without user intervention by placing a specially formatted XML file on a USB drive or floppy disk before booting from the installation DVD. The file's name is either Autounattend.xml (my preference) or Unattend.xml. The file is placed in the root directory of a USB drive or floppy disk. 

The best way to create Autounattend.xml is to install Windows Automated Installation Kit (AIK). Then you have to copy install.wim file from the Windows 7 installation media, open install.wim in Windows System Image Manager, and create a catalog for it (takes a long time) before you can create the first Autounattend.xml. To spare all these steps, I provide a sample Autounattend.xml that has worked for me many times.

Structure of Autounattend.xml file.

This file consists of <settings> tags that correspond to different setup passes (passes are explained in Windows AIK documentation) and <component> tags inside them that specify the actual settings. The root tag is <unattend>:

<unattend xmlns="urn:schemas-microsoft-com:unattend">
    <settings pass="windowsPE">
    <settings pass="specialize">

The most interesting setup passes are: windowsPE (customize setup itself), specialize (customize installed Windows 7), and oobeSystem (customize Out-Of-Box-Experience, e.g. Windows Welcome UI pages).

The <component> tag has a name attribute that specifies component name, processorArchitecture attribute that specifies architecture (amd64, x86, ia64) to which this component applies, and some additional attributes that you don't need to worry about. Here is an example:

<component name="Microsoft-Windows-Shell-Setup"

In the examples below, I list a component name followed by '|', followed by XML tag name(s) (also separated by '|") for the component setting. For example, this notation:

Microsoft-Windows-International-Core-WinPE | SetupUILanguage | UILanguage

means this XML:

<component name="Microsoft-Windows-International-Core-WinPE"


Basic settings.

The minimum settings required for an Unattended Installation are:

  • Language to use during Windows Setup (Microsoft-Windows-International-Core-WinPE | SetupUILanguage | UILanguage).
  • Language to use on the installed Windows operating system (Microsoft-Windows-International-Core-WinPE | UILanguage).
  • Acceptance of Microsoft Software License Terms for setup (Microsoft-Windows-Setup | UserData | AcceptEula).
  • Product Key, which is used to specify which Windows edition to install (Microsoft-Windows-Setup | UserData | Product Key | Key).
  • Installation location (Microsoft-Windows-Setup | ImageInstall | OSImage | InstallTo | DiskID/PartitionID).

Additional settings.

You may want to add these customizations to your installation:

  • Format installation partition (Microsoft-Windows-Setup | DiskConfiguration | Disk | ModifyPartitions | ModifyPartition | Format).
  • Assign label to installation partition (Microsoft-Windows-Setup | DiskConfiguration | Disk | ModifyPartitions | ModifyPartition | Label).

Automate Windows Welcome screen.

When Windows first boots having been fully installed, it displays several UI pages. The responses to those UI pages can also be automated. Here are some useful settings:

  • Default input locale (Microsoft-Windows-International-Core | InputLocale).
  • Default system locale (Microsoft-Windows-International-Core | SystemLocale).
  • Default UI language (Microsoft-Windows-International-Core | UILanguage).
  • Default user locale (Microsoft-Windows-International-Core | UserLocale).
  • Acceptance of Microsoft Software License Terms for the installed OS (Microsoft-Windows-Shell-Setup | OOBE | HideEULAPage).
  • Product Key, if not specified in basic setup settings (Microsoft-Windows-Shell-Setup | ProductKey).
  • User name (Microsoft-Windows-Shell-Setup | UserAccounts).
  • Computer name (Microsoft-Windows-Shell-Setup | ComputerName).
  • Windows Protection (Microsoft-Windows-Shell-Setup | OOBE | ProtectYourPC).
  • Time and Date (Microsoft-Windows-Shell-Setup | TimeZone).
  • Computer's current network location, e.g. Home, Work, Public (Microsoft-Windows-Shell-Setup | OOBE | NetworkLocation).
  • Wireless network selection (Microsoft-Windows-Shell-Setup | OOBE | HideWirelessSetupInOOBE).
  • Administrator's password (Microsoft-Windows-Shell-Setup | UserAccounts | AdministratorPassword | Value).

Automate Internet Explorer 8 Welcome Screen.

Use the following settings to bypass IE8 search provider and other selection screen:

  • Microsoft-Windows-IE-InternetExplorer | DisableAccellerators.
  • Microsoft-Windows-IE-InternetExplorer | DisableOOBAccelerators.
  • Microsoft-Windows-IE-InternetExplorer | Home_Page.
  • Microsoft-Windows-IE-InternetExplorer | SuggestedSitesEnabled.

Enable Built-in Administrator account.

In Windows 7 the built-in Administrator account is disabled by default. To enable it, you have to specify AutoLogon setting using Administrator account and set a non-blank Administrator password:

  • Microsoft-Windows-Shell-Setup | AutoLogon | Password | Value.
  • Microsoft-Windows-Shell-Setup | AutoLogon | Username (must be Administrator).
  • Microsoft-Windows-Shell-Setup | LogonCount (must be at least 1).
  • Microsoft-Windows-Shell-Setup | UserAccounts | AdministratorPassword.

Disable automatic activation.

Disable automatic 3-day Windows 7 activation by specifying false at this specialize pass setting:

  • Microsoft-Windows-Security-SPP-UX | SkipAutoActivation.

Example Autounattend.xml.

I've added a few other customizations, like:

  • QuickEdit mode for cmd.exe.
  • File extensions shown in Windows Explorer.
  • Run command shown on the Start menu.
  • Administrative Tools shown on the Start menu.
  • cmd.exe pinned to Taskbar (Taskbar link).

(note: these will only work for the Administrator account, because it logs in first).

Here is a sample file for Windows 7 x64: Autounattend.xml

It formats partition 1 of disk 0 to NTFS and installs OS there.

You'll need to change the install disk partition, computer name, product key, and Administrator password to your specific values before using this file.

Note: if you don't change the product key, you will get an error during the setup.



Copyright © Sergey Vasilevskiy