Congratulations to Dimitar Dimitrov a MCP, MCDBA, and MCAD of Hebrosbank Plovdiv, Bulgaria, who won first prize of $100 for the best solution to the January Reader Challenge, "Deploying a Startup Parameter on All Servers." Only one submitter met the expectations set by the problem for the January challenge. Here’s a recap of the problem and the solution to the January Reader Challenge.


Charlie is a database administrator who manages a combination of more than 50 installations of SQL Server 2000 and SQL Server 7.0. He wants to add a new trace flag as a startup parameter on all the servers to generate a report in the event of a SQL Server deadlock. For this problem, assume that the trace flag that generates this deadlock report is 1204. How can Charlie quickly deploy this new startup parameter to all the servers?


Charlie can use the SQL Distributed Management Objects (SQL-DMO) API to connect to each SQL Server and add the new trace flag 1204 startup parameter, specifically the Configuration.Parameters collection of the SQLServer object. After connecting to a SQL Server instance by using the SQLServer object, the Configuration.Parameters collection is used to enumerate all the startup parameters currently configured. Charlie can then add the trace flag 1204 startup parameter to the Parameters collection. Below, a sample VBScript program, AddSqlStartup.vbs, shows how to use SQL-DMO to add a startup parameter to a SQL Server instance. Now Charlie can simply call AddSqlStartup.vbs for each server he wants to add the new startup parameter to. Charlie can do this by writing a command script using the FOR command to enumerate a list of servers specified in a text file and calling AddSqlStartup.vbs for each server. Alternatively, he can incorporate this logic into the VBScript program itself. The examples below show the implementation using a CMD script.

' AddSqlStartup.VBS                              option explicit                              dim sqlsrvr                              dim startupparam                              dim parameterscount                              dim currentparams                              dim paramfound                              dim scriptargs                              set scriptargs = WScript.Arguments                              if scriptargs.count  2 then                                  WScript.StdOut.WriteLine "Usage: AddSqlStartup "                                  WScript.Quit                              end if                              set sqlsrvr = CreateObject("SQLDMO.SQLServer")                              sqlsrvr.LoginSecure = True                              sqlsrvr.Name = scriptargs(0)                              sqlsrvr.Connect                              paramfound = false                              startupparam = scriptargs(1)                              set currentparams = sqlsrvr.Configuration.Parameters                              for parameterscount = 1 to currentparams.count                                  if currentparams(parameterscount) = startupparam then                                      paramfound = true                                  end if                              next                              ' Add new startup parameter if it doesn't exist.                              if not paramfound then                                  currentparams.add startupparam                              end if                              sqlsrvr.Disconnect                              set sqlsrvr = Nothing                              :: AddSqlStartup.CMD                              @echo off                              setlocal                              set PARAM=%1                              if "%1" EQU "" (                                  echo Please specify the startup parameter.                                  goto :eof                              )                              for /F %%S in (servers.txt) do (                                  cscript //NoLogo AddSqlStartup.vbs %%S %PARAM%                              )                              Endlocal                              File: Servers.Txt                              srvr1                              srvr2\yukon                              srvr3                              

Finally, Charlie executes the CMD script (which reads the list of servers from the Servers.txt file and adds the startup parameter -T1204 to each server) by issuing the following command:

AddSqlStartup.CMD –T1204

Alternatively, Charlie can use the OLE automation system stored procedures to use DMO from within T-SQL to add the startup parameter. A sample stored procedure that shows how to add a new parameter is shown below:

CREATE PROCEDURE SetStartupParameter(@SqlServer varchar(255), @user varchar(255) =                               NULL, @password varchar(255) = NULL, @value varchar(255) = '-T1204')                              AS                                DECLARE @dmoSqlServer int, @parameters int, @hr int                                DECLARE @src varchar(255), @desc varchar(255)                                IF @value IS NULL BEGIN                                  RAISERROR('The @value can not be null', 16, 1)                                  RETURN                                END                                EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @dmoSqlServer OUT                                IF @hr  0 GOTO ExitProc                                -- Using windows authentication. The SQL Service account must be sysadmin                                -- on remote SQL Server (@SqlServer).                                IF @user IS NULL                                  EXEC sp_OASetProperty @dmoSqlServer, 'LoginSecure', 1                                EXEC @hr = sp_OAMethod @dmoSqlServer, 'Connect', null, @SqlServer, @user, @password                                IF @hr  0 GOTO ExitProc                                EXEC @hr = sp_OAGetProperty @dmoSqlServer, 'Configuration.Parameters', @parameters OUT                                IF @hr  0 GOTO ExitProc                                -- Checking to see whether the startup parameter already exists.                                DECLARE @count int , @i int, @v varchar(255), @exists bit                                SELECT @exists = 0, @i = 1                                EXEC sp_OAMethod @parameters, 'Count', @count OUT                                WHILE @i  0 GOTO ExitProc                                END                                GOTO ExitProc                              ExitProc:                                IF @hr  0 BEGIN                                  EXEC sp_OAGetErrorInfo @dmoSqlServer, @src OUT, @desc OUT                                   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc                                END                                EXEC sp_OADestroy @dmoSqlServer                                RETURN @hr                              GO                              


Now, test your SQL Server savvy in the February Reader Challenge, "Imposing Data Restrictions" (below). Submit your solution in an email message to by January 19. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.


Arun is a database architect who designs database schemas for products that use SQL Server 2000 and 7.0. Arun is currently working on a schema for a product that lets end users configure field names. The product’s schema table contains a column that stores a field name, and this field name is displayed in the product’s UI. The table can be installed under any case-insensitive database collation supported by SQL Server. The schema of the table looks like this:

create table meta_FieldNames ( fieldid int not null primary key, fieldname nvarchar(50) not null )

As part of the schema design, Arun wants to allow only a mix of upper or lowercase alphabetical characters with no numbers, international alphabet characters (e.g., accent marks, tildes) or special characters in the field names. How can he impose this restriction on the "fieldname" column of the table?