Forum Discussion

Element115's avatar
Element115
Memorable Member
2 years ago

QUESTION::COPY DATA::UPSERT FAIL

The source is an on-prem SQL Server, and the destination is a lakehouse table.  I use a Copy data activity pointing to the lakehouse SQL endpoint in order to get access to the Upsert option.   Ca...
data engineering
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Element115 ,

Based on the information you have provided, you are experiencing an error message displayed:

Global temporary tables and transient user tables in TEMPDB are not supported in this edition of SQL Server.


You can follow the steps below to test and troubleshoot the problem:

  1. First you need to verify the version of SQL Server you are using, as some versions may have limitations on certain features.
  2. If the query is using global temporary tables, consider modifying it to instead use local temporary tables (prefixed with #), which are supported by all versions of SQL Server.

If there is still a problem, could you please provide some information such as what you did before the error occurred and has the problem occurred before?

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Element115's avatar
    Element115
    Memorable Member
    2 years ago

    1. Where is the documentation page that mentions which SQL Server version is not compatible with the pipeline Copy data Upsert? 

     

    This is the version of the on-prem server:


    Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64)
    Jan 25 2023 08:42:43
    Copyright (C) 2017 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
    EngineEditon: 2

     

    2. The query in the Source tab of the Copy data activity DOES NOT explicitly use any temp tables, global or otherwise; here is the query:

    SELECT *
FROM 
	vIVDS_TX_10CM_HEIGHT_BINS
WHERE 
	[DATE] >= '@{variables('latest_date_ivds_tx_10cm_height_bins')}'

     

    So in your opinion, what causes this issue?

  • Element115's avatar
    Element115
    Memorable Member
    2 years ago

    Ticket #2407230040010740