Imposing Data Restrictions

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.

Problem:

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?

Solution:

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

FEBRUARY READER CHALLENGE:

Now, test your SQL Server savvy in the February Reader Challenge, "Imposing Data Restrictions" (below). Submit your solution in an email message to [email protected] 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.

Problem:

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?

Comments

Plain text