Congratulations to Emmanuel Nanchen, an analyst and developer for Manpower in Gland, Switzerland and Erik McKibben, a DBA for the Port Authority of Allegheny Country in Pittsburgh, Pennsylvania. Emmanuel won first prize of $100 for the best solution to the February Reader Challenge, "Avoiding Application Failure." Erik won second prize of $50. Here’s a recap of the problem and the solution to the February Reader Challenge.
David, a developer for a company that sells firewall and ad-blocking applications, develops applications that use Microsoft SQL Server 2000 Desktop Engine (MSDE) for storing various configuration metadata and activity logs. David notices that the application startup can fail if the MSDE service hasn't recovered the application database completely even though the SQL Server service has started successfully. How can David avoid application failure if the database isn't recovered completely? What can David do in the application code to start the application gracefully?
David can avoid the application startup failure by checking the database status before he connects to it or changes the context of the connection to it. He can do this by using the DATABASEPROPERTYEX() function to determine the application database’s status. DATABASEPROPERTYEX() returns the specified property’s setting and database name. For example, to determine the database status, David can run:
SELECT DATABASEPROPERTYEX('northwind', 'Status')
The following T-SQL batch shows how to use DATABASEPROPERTYEX() to determine the database status within a specified interval and return failure if the database isn’t online.
-- Create test database and mark it offline: CREATE DATABASE test_recovery GO ALTER DATABASE test_recovery SET offline GO DECLARE @dbname nvarchar(128), @online bit, @timeout_secs int, @total_wait int SET @dbname = 'test_recovery' SET @total_wait = 30 SET @timeout_secs = 0 SET @online = 0 WHILE(@timeout_secs 1 RAISERROR ('Fatal error. Application database is not online', 16, 2) GO DROP DATABASE test_recovery GO
David can adjust the @total_wait value based on the recovery time, which lets the application wait for the database to be recovered. David can determine if the database is online by executing the batch of statements for the specified application database.
MARCH READER CHALLENGE:
Now, test your SQL Server savvy in the March Reader Challenge, "Upgrading Batch-Execution Feature" (below). Submit your solution in an email message to email@example.com by February 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.
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--the engine that executes the batch commands, after replacing them with their appropriate values, processes these tokens. These tokens are case-sensitive in nature and look like:
%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 rows from the AppTasks table that have the application tokens that must be modified as part of the upgrade process. The following is a sample schema and data for the table:
USE tempdb GO CREATE TABLE TaskTokens ( TokenName nvarchar(10) COLLATE Latin1_General_CS_AI NOT NULL PRIMARY KEY ) GO INSERT INTO TaskTokens (TokenName) VALUES( N'%SRVR%' ) INSERT INTO TaskTokens (TokenName) VALUES( N'%LOGFILE%' ) INSERT INTO TaskTokens (TokenName) VALUES( N'%ERRFILE%' ) GO SELECT TokenName FROM TaskTokens GO CREATE TABLE AppTasks ( AppTaskId int NOT NULL PRIMARY KEY, AppCommandBatch nvarchar(2000) COLLATE Latin1_General_CS_AI NOT NULL, ) GO INSERT INTO AppTasks (AppTaskId, AppCommandBatch) VALUES( 1, N' REM %SRVR%: Server name to be analyzed REM %LOGPATH%: Log file name with path app1 %SRVR%, %LOGPATH% ' ) INSERT INTO AppTasks (AppTaskId, AppCommandBatch) VALUES( 2, N' REM %srvr%: Not specified. Defaulting to local server app1 ".", "C:\Temp"' ) INSERT INTO AppTasks (AppTaskId, AppCommandBatch) VALUES( 3, N' REM No SRVR or logpath parameter required app2' ) GO SELECT AppTaskId, AppCommandBatch FROM AppTasks GO