How It Works: Conversion of a Varchar, RPC Parameter to Text from a Trace (.TRC) Capture

  • Article

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog.

This was an interesting issue I ran into this week.   The reported problem was that the data shown as TEXT for an RPC event did not match the data submitted by the client.  

SQL Server 2005 introduced an optimization for capturing RPC events.  During a SQL Server 2005 capture you can exclude the TEXT column from RPC events and the BINARY column contains the necessary data to construct the TEXT column.  By capturing the BINARY column and allowing the TEXT to be formatted when the trace is viewed (Profiler or fn_trace_gettable) and the tracing impact is lowered.

The interesting aspect comes when the BINARY data is converted to the UNICODE TEXT column.   Take the following procedure definition as an example, taking special care to notice the multi-byte character and UNICODE character definitions.

create procedure spTest  @sVarchar varchar(10), @sNVarchar nvarchar(10) ...

Note: The trace TEXT column is always UNICODE column.      

The BINARY column contains the portion holding the raw parameter data values.  So the process that reads the trace must convert the varchar to UNICODE in order to build the TEXT column.

  • When using fn_trace_gettable the process is the SQL Server instance.
  • When using Profiler the process is the Profiler process.

The SQL Server products use MultiByteToWideChar to convert the varchar data to the UNICODE representation using the CP_ACP code page which tells the conversion to use the SYSTEM DEFAULT LANGUAGE (locale).

Assume a client is Russian (Cyrillic).1251 based and submitted the following query.

{call spText('ГУРМ', N'ГУРМ')}

My machine is a US English.1252 based.  When I open the trace file I see the following because the character to UNICODE is using my system default language to perform the conversion.

exec spTest 'ÃÓÐÌ34 ', N ' ГУРМ '

If I change my system default locale to the Russian I see the following.

exec spTest 'ГУРМ ', N ' ГУРМ '

As a general rule it is unwise to store language specific data in single byte character columns, nvarchar should be used instead to avoid unexpected conversions.

The solution for this issue was to make sure the trace was being processed using a computer with the system language set to the expected data. 

To check your system default language (Vista example) use the Control Panel | Regional and Language Options.

The system language is located under the Administrative table | Change System Locale

image

Don't get confused with the Formats tab.  This is the default USER locale and is not used by the CP_ACP designation in MultiByteToWideChar.

image

LANGUAGE EVENTS: What about the Language (adhoc query) event TEXT?   These are UNICODE and built during the trace capture so the process that reads the trace file already has the UNICODE representation.

REPLAY: It is important to understand the possible translation issue because this could have an affect on replay attempts.

RML UTILITIES: This issue exposed an problem with ReadTrace's (9.00.0023) processing of the Varchar, RPC data.  Contact DSDBTOOL@MICROSOFT.COM if you encounter this issue.

Bob Dorr
SQL Server Senior Escalation Engineer