Getting Error in Export and Import query for table with BCP

book

Article ID: 242545

calendar_today

Updated On:

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.[trn_Subj]. 

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].[trn_Subjectivity] L5 where exists 
(SELECT * FROM [N_NPR].[dbo].[trn_FileAttachment] L4 WHERE L4.[FileAttachmentId] = L5.[FileAttachmentId] 
and exists (SELECT * FROM [N_NPR].[dbo].[trn_Risk] L0 WHERE L0.[RiskId] = L4.[RiskId] AND riskid  in  
 ( SELECT riskid  FROM [N_GOLDCOPY].[dbo].[temp_trn_risk]))) UNION SELECT * FROM [N_NPR].[dbo].[trn_Subjectivity] L19 WHERE EXISTS 
 (SELECT * FROM [N_NPR].[dbo].[trn_Risk] L0 WHERE L0.[RiskId] = L19.[RiskId] AND riskid  in   ( SELECT riskid  FROM [N_GOLDCOPY].[dbo].[temp_trn_risk]))

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 for Build MS SQL Server Direct Insert SQL  we donot get error for this table subset.

All the screenshots are attached with the Export Import Files/logs and Direct Insert Files also attached for your comparision.

DDL of the [trn_Subjectivity] table, trn_risk, Temp_trn_risk etc all also attached.

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

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

 

Cause

This is working as designed

Environment

TDM 4.9.x 4.10.x
Test data manager

 

Resolution

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