Getting SQLState = 37000, NativeError = 5335 Errors in Export and Import query for table with BCP

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are unable to perform the subset using the build MS SQL Server export import option in the GT Subset tool for one table dbo.[Table]. 

We are getting the below error for the table in the export log.

Starting copy...
SQLState = 37000, NativeError = 5335
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

BCP copy out failed

Below is the query generated in the Export.bat file for the table.

SELECT * from [N_NPR].[dbo].[Table1] L5 where exists 
(SELECT * FROM [N_NPR].[dbo].[Table2] L4 WHERE L4.[FileAttachmentId] = L5.[FileAttachmentId] 
and exists (SELECT * FROM [N_NPR].[dbo].Table3] L0 WHERE L0.[RiskId] = L4.[RiskId] AND riskid  in  
 ( SELECT riskid  FROM [N_GOLDCOPY].[dbo].[temp_Table3]))) UNION SELECT * FROM [N_NPR].[dbo].[Table1] L19 WHERE EXISTS 
 (SELECT * FROM [N_NPR].[dbo].[Table3] L0 WHERE L0.[RiskId] = L19.[RiskId] AND riskid  in   ( SELECT riskid  FROM [N_GOLDCOPY].[dbo].[temp_Table3]))

When we run this query in the SQL Server Management Studio we are getting the below error as well.

Msg 5335, Level 16, State 1, Line 1
The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

When we use the same subset extract to Build MS SQL Server Direct Insert SQL we don't get error for this table subset.

Subset query we are using in our extract is as below.

SELECT *
FROM [N_NPR].[dbo].[Table3]
where riskid
in    (
SELECT riskid
FROM [N_GOLDCOPY].[dbo].[temp_trn_risk])

 

Environment

TDM 4.9.x 4.10.x
GT Subset Component

 

Resolution

This is working as designed

We do not currently support complex queries as the client is trying to use them.
The supported format is SELECT * FROM DRIVINGTABLE WHERE (complex expression)

The GT Subset Application was not designed to work where the driving table uses a complex query (join with other tables).
Any relationship between the driving table and other tables needs to be built using the subset UI