Capturing Vital Information About Data Transformations

Here's a recap of the problem and the solution to the June Reader Challenge, "Capturing Vital Information About Data Transformations."

Problem:

Harry develops products that help build data warehouses and have SQL Server 2000 back-end databases. He's trying to develop a set of stored procedures that will perform data transformations for the product and run long-running batch jobs. Harry wants to modify his stored procedures so that he can easily capture vital data-transformation or batch-job information and statistics, which will let him monitor a batch job's progress or provide counters about certain events the application defines. Help Harry provide a mechanism to raise events with an event description and the associated data without significantly modifying the application code. Also, monitor the events outside the database using tools or scripts as needed. The following is an example of the data Harry wants to return from his stored procedure, which invokes a series of jobs:

Counter Name Counter Data
------------ ------------
Job Status   Percent value like 10, 20, 30
Step Status  Percent value like 10, 20, 30
App Xfrm 1   Number of Executions 1025

Solution:

Harry can leverage the ability to generate user-defined trace events in SQL Server and modify the stored procedures to emit events. Harry can call the extended system stored procedure sp_trace_generateevent to generate a trace event with user information that SQL Server Profiler captures. The system stored procedure sp_trace_generateevent takes three parameters: @eventid, @userinfo and @userdata. The value of @eventid is pre-defined and can range from 82-91 where 82 indicates the user-defined trace event 0, 83 indicates the user-defined trace event 1, and so on. Harry can use the @userinfo parameter to specify a string that will be emitted in the TextData column for the trace event. Harry can use the @userdata parameter to specify some data that will be emitted in the BinaryData column for the trace event. A sample call to generate a sample event with the userinfo and userdata string values will look like:

DECLARE @binarydata varbinary(8000)
SET @binarydata = CAST(1025 AS varbinary)
EXEC master..sp_trace_generateevent 82, N'App Xfrm 1', @binarydata

The above event data can be seen from Profiler after setting up a trace that monitors the trace event User Configurable (0). Harry can thus generate the required events from the stored procedures without making major changes to the application. Using Profiler also lets him monitor the execution environment of the application outside the database server. Additionally, Harry can use Profiler to log the trace events to a table and analyze them later.

JULY READER CHALLENGE:

Now, test your SQL Server savvy in the July Reader Challenge, "Case Sensitive Settings" (below). Submit your solution in an email message to [email protected] by June 16. 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:

Marat, a software developer in a company that develops data-modeling applications, has an application that includes SQL Server 2000 modifications. One of the modifications lets users create databases on the server. As part of the database-creation process, Marat wants to detect if the SQL Server supports the use of database names that are case sensitive. Help Marat determine this SQL Server setting so that the application can handle it appropriately.