Upgrading Batch-Execution Feature

Congratulations to Ahmad Mohamed, a DBA and developer for Ernst & Young in Cleveland, Ohio, and Daniele Pierasco, a DBA for Brain Force Software Italia in Milan, Italy. Ahmad won first prize of $100 for the best solution to the March Reader Challenge, "Upgrading Batch-Execution Feature." Daniele won second prize of $50. Here’s a recap of the problem and the solution to the March Reader Challenge.

Problem:

Mark is a database developer for a company that sells products that run with SQL Server as the back-end database. The company’s products currently support all SQL Server 2000 editions and different server collations. One of the company's products consists of a workflow application that has a functionality that lets users create their own batch scripts. Customers use the batch scripts to execute application command-line utilities. The scripts also have tokens that they pass as parameters. After replacing the tokens with their appropriate values, the engine that executes the batch commands processes the tokens. These tokens are case-sensitive and look like the following examples:

%SRVR%%LOGFILE%%ERRFILE%

Mark is designing a new version of the feature that simplifies the batch-command execution. As part of the upgrade process, he wants to identify the commands that need to be modified. Help Mark write the query to identify the AppTasks table rows that have the application tokens that must be modified as part of the upgrade process. A sample schema and data for the AppTasks table follows:

USE tempdbGOCREATE TABLE TaskTokens (    TokenName nvarchar(10) COLLATE Latin1_General_CS_AI NOT NULL PRIMARY KEY)GOINSERT INTO TaskTokens (TokenName)VALUES( N'%SRVR%' )INSERT INTO TaskTokens (TokenName)VALUES( N'%LOGFILE%' )INSERT INTO TaskTokens (TokenName)VALUES( N'%ERRFILE%' )GOSELECT TokenNameFROM TaskTokensGOCREATE TABLE AppTasks (    AppTaskId int NOT NULL PRIMARY KEY,    AppCommandBatch nvarchar(2000) COLLATE Latin1_General_CS_AI NOT NULL,)GOINSERT INTO AppTasks (AppTaskId, AppCommandBatch)VALUES( 1, N'REM %SRVR%: Server name to be analyzedREM %LOGPATH%: Log file name with pathapp1 %SRVR%, %LOGPATH%' )INSERT INTO AppTasks (AppTaskId, AppCommandBatch)VALUES( 2, N'REM %srvr%: Not specified. Defaulting to local serverapp1 ".", "C:Temp"' )INSERT INTO AppTasks (AppTaskId, AppCommandBatch)VALUES( 3, N'REM No SRVR or logpath parameter requiredapp2' )GOSELECT AppTaskId, AppCommandBatchFROM AppTasksGO

Solution:

Mark can use the CHARINDEX() function to get the rows he wants from the AppTasks table. The CHARINDEX() function looks for any occurrence of the specified pattern in the search string and returns its position. An example that uses the %SRVR% token follows:

 SELECT a.AppTaskId, a.AppCommandBatch   FROM AppTasks aWHERE CHARINDEX('%SRVR%', a.AppCommandBatch) > 0

This query is different than using a LIKE statement because LIKE will consider the % sign as a wild-card whereas CHARINDEX() treats it as a literal. The search pattern will use the same collation because the column collation is case-sensitive. So the query returns one row irrespective of the database or server collation. Mark then uses this technique to join against the TaskTokens table. The final query that returns the desired rows for the upgrade script follows:

SELECT a.AppTaskId, a.AppCommandBatch   FROM AppTasks a WHERE EXISTS(SELECT *                FROM TaskTokens AS t               WHERE CHARINDEX( t.TokenName, a.AppCommandBatch) > 0)

APRIL READER CHALLENGE:

Now, test your SQL Server savvy in the April Reader Challenge, "OS Characteristics" (below). Submit your solution in an email message to [email protected] by March 17. 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:

Mike, a test architect for a company that develops applications using SQL Server as the back-end database, wants to determine which OS version and Service Pack is installed on the machine running the SQL Server service. Mike wants to use T-SQL code to perform this check so that he can use the logic in his stored procedures. All his servers run Windows Server 2003 and the virtual machines he uses for testing purposes run Windows XP Professional Edition. Help Mike write the T-SQL code to determine the following OS characteristics:1. OS version, such as Windows Server 2003 or XP Pro2. OS edition, such as Enterprise Edition or Standard Edition3. Service Pack version