Monday, April 30, 2018

The data types ntext and varchar are incompatible in the equal to operator.

Today I loaded a trace file into a table so that I could analyze some long running queries.  This is the statement that I used to upload the trc file to a table:

    --read trace file using fn_trace_gettable
    SELECT *
    INTO DBA..HREmployees_Trace
    FROM fn_trace_gettable('G:\HREmployees_Trace_31.trc', default) Trace


And then this is the statement that I used to find the records for the problem query that we were troubleshooting:

    SELECT StartTime,EndTime,TextData,*
    FROM dbo.HREmployees_Trace
    WHERE TextData = 'select * from HREmployees where Status = "A"'


But, it immediately failed w/this error:

    Msg 402, Level 16, State 1, Line 1
    The data types ntext and varchar are incompatible in the equal to operator.

Why am I getting that error?  Well, the answer is very simple.  The next and varchar are not compatible when compared with each other using equal sign.  If you look at that table I created when loading the trace file (.trc), you can see that TextData is created as NTEXT.

Fastest workaround?   Use LIKE instead of EQUAL (=), like this:


    SELECT StartTime,EndTime,TextData,*
    FROM dbo.HREmployees_Trace

    WHERE TextData LIKE '%select * from HREmployees where Status = %'


And you see there... it's no longer failing to collect from the NTEXT column.





















Take a look at this for a little more detail on sys.fn_trace_gettable:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql?view=sql-server-2017


No comments:

Post a Comment