ADF pipeline with upsert throwing error "The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable"

Shantanu Dekapurwar 20

ADF pipeline writing data from AZURE sql to on prem SQL Server with upsert operation.

TEXT fields are part of target system, Even though TEXT fields are not selected in select query and are not mandatory fields still throwing error. I cannot change target system database structure.

Message=The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Erland Sommarskog 101.4K Reputation points MVP

2024-04-27T09:57:47.05+00:00

So what does the query that produces this error look like?
Smaran Thoomu 10,080 Reputation points Microsoft Vendor

2024-04-29T06:52:57.96+00:00

@Shantanu Dekapurwar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Shantanu Dekapurwar 20 Reputation points

2024-04-29T19:44:26.1833333+00:00

query is straightforward select id,name from table1 from source AZure SQL table

Sink is on prem sql server 2016 with table target has following fields (id (int), name(varchar), add(text))

I am trying to do upsert based on id
Erland Sommarskog 101.4K Reputation points MVP

2024-04-29T21:11:51.57+00:00

That's not an "upsert" query. That is the query we need to see. Or are you saying that Azure Data Factory generates it for you? (Sorry, I know SQL Server. I know nothing about Azure Data Factory.)
Smaran Thoomu 10,080 Reputation points Microsoft Vendor

2024-04-30T06:17:48.4633333+00:00
Hi @Shantanu Dekapurwar

Thanks for the question and using MS Q&A platform.

The error message indicates that the text data type is not comparable and cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators. This error is usually caused by the fact that the text data type is not supported in the comparison operations.

To resolve this issue, you can try to cast the text data type to a comparable data type such as nvarchar or varchar. You can do this by modifying your query to cast the text data type to a comparable data type before performing the upsert operation.

For example, you can modify your query as follows:

SELECT id, name, CAST(add AS NVARCHAR(MAX)) AS add FROM table1

This will cast the add column to nvarchar data type, which is comparable and can be used in the upsert operation. Alternatively, you can try to modify the target system database structure to use a comparable data type instead of text.

I hope this helps! Let me know if you have any further questions.
Shantanu Dekapurwar 20 Reputation points

2024-04-30T13:56:42.93+00:00

The error is with "Text" field which are not even selected in select query or in mapping so not sure how cast will work?

I believe copy activity with UPSERT used Union clause in background and "Text" field doesn not supported in UNION clause.

Target is legacy application db hence i cannot change "Text" to varchar, looking for workaround
Smaran Thoomu 10,080 Reputation points Microsoft Vendor

2024-04-30T15:32:43.4433333+00:00
@Shantanu Dekapurwar I see. In that case, you can try to exclude the Text column from the upsert operation. You can do this by modifying the upsert query to exclude the Text column from the SELECT statement.

Here's an example of how you can modify your upsert query to exclude the Text column:

MERGE INTO target AS T USING ( SELECT id, name FROM source ) AS S ON T.id = S.id WHEN MATCHED THEN UPDATE SET T.name = S.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (S.id, S.name);

In this example, the Text column is excluded from the SELECT statement. This should resolve the issue.

Alternatively, you can try to use a staging table to copy the data from the source table to the target table. In the staging table, you can exclude the Text column and then copy the data to the target table. This should also resolve the issue.

I hope this helps. Let me know if you have any further questions.

Accepted answer

Erland Sommarskog 101.4K Reputation points MVP

2024-04-30T21:02:24.31+00:00

The best solution is to change the data type of that column to varchar(MAX). Yes, I hear that you say that you can't change it, but tell your stakeholders that they have had eighteen long years to replace this deprecated and mouldy data type. It should have been done a decade ago.

If they insist on keeping the data type, they will have to keep paying for lame workarounds.

One possible workaround is to create a view on top of the table where you cast the data type. Your ADF pipeline would work against that view. Then you need to have INSTEAD OF triggers on that view to propagate the operations to the underlying table.

As I said, I don't know ADF myself, but I would be very surprised if ADF does not permit you to use a custom statement for the operation. And in this operation you would cast the data type.

But as I said, the best is to fix the data type....
Please sign in to rate this answer.
Shantanu Dekapurwar 20 Reputation points

2024-05-02T11:59:42.78+00:00

Here is the summary, Copy Activity with upsert has limitation to work with "Text" datatype, as we dont have liberty to change/update datatype in legacy system following workarounds we are contemplating

a) Use Azure Data Flow or any other ETL tool to do Upsert logic, they all work fine with all the datatypes

b) As mentioned above by Erland go with view/Temp table and trigger on target side to perfrom upsert logic Or you can also go with SP

c) Go with DataSync services which perform near real time replication between on prem and Azure SQL, might be costlier alternative but it doesn't break because of datatype not supported.
Sign in to comment

ADF pipeline with upsert throwing error "The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable"

0 additional answers