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


Wednesday, April 18, 2018

When was the last transaction log backup taken?

Good question.  I was reviewing a customer's backup status today, and I needed to answer that question -- when was the last tranlog backup run?  It is a fast statement, and I have included the recovery model in with each database, so that you can see which ones are FULL recovery, and where the backups may be missing.

     SELECT
          d.name [Database],
          d.recovery_model_desc [RecoveryModel],
          MAX(b.backup_finish_date) [BackupDate]
     FROM
          master.sys.databases d LEFT OUTER JOIN msdb..backupset b
             ON b.database_name = d.name
             AND b.type = 'L'
     GROUP BY 
          d.name
          d.recovery_model_desc
     ORDER BY 
          d.name;

Your resultset will be a little something like this:
















If the database recovery models are correct, then you may need to get some backups into place on that Orders database.  

See this for more details on SQL Server's transaction log backups: