3

I am getting this error from AssertEqualsTable "The data types text and text are incompatible in the equal to operator."

then

"The 'TableCompare' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead."

select   *
    into #Actual
    from [dbo].[InvoiceOut];

--make expected table an empty table of #actual's structure because we truncate so it should be empty.
    SELECT TOP(0) *
    INTO #Expected
    FROM #Actual;

EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';

--part of the relevant table info

CREATE TABLE [dbo].[InvoiceOut](
...
    [InsertField] [text] NULL,
    [DeductibleText] [text] NULL,
    [BarcodeText] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
JDPeckham
  • 2,414
  • 2
  • 23
  • 26
  • 1
    You should not still be using text datatype, it is deprecated. This is a problem with your data structure that is CRITICAL to fix. – HLGEM Sep 05 '12 at 21:44
  • 2
    can you explain why it is CRITICAL? – JDPeckham Sep 22 '12 at 21:51
  • Possible duplicate of [WHERE clause on SQL Server "Text" data type](https://stackoverflow.com/questions/4350060/where-clause-on-sql-server-text-data-type) – mm201 Jun 08 '17 at 14:11
  • See also https://stackoverflow.com/questions/27888243/the-data-types-text-and-varchar-are-incompatible-in-the-equal-to-operator-in-c-s – Michael Freidgeim Mar 10 '23 at 02:13

1 Answers1

5

I don't think you can compare text field values, which would explain the error.

Also, the text data type is deprecated in favor of varchar(MAX).

See this

Community
  • 1
  • 1
Beth
  • 9,531
  • 1
  • 24
  • 43