Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint-CodePudding

I created linked server to Azure synapse SQL pool (dedicated pool) at my on-prem SQL Server named (SynapseSql - linked server name)

When I try to run this query:

INSERT INTO [SynapseSQL].[DW].[dbo].[t1] ([col1]) VALUES (1)

It is throwing me an error:

Msg 46706, Level 16, State 1, Line 1
Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

Is that mean we cannot insert/update/delete data using parallel query in linked server to synapse?

Could anybody help me to use parallel queries in my SProc`s for dedicated SQL pool?

Thanks!

CodePudding user response：

The functionalities of a normal SQL Server or SQL Azure database are not supported by Microsoft Azure Data Warehouse. Server-side cursors, for example, are not supported by Azure Data Warehouse. When connecting to Azure Data Warehouse using a driver like the Microsoft SQL Server JDBC driver or the jTDS JDBC driver, the driver must be set to not utilise cursors, otherwise problems will occur on certain queries.

As the Linked Servers, they allow the instance in which they are created to read data from external data sources as well as execute commands against remote database servers.

As a workaround this could be used:

EXEC ('INSERT INTO dbo.t1 VALUES(1)') at yourLinkedServer;

WITH t1 as ( select * FROM schema.table ) select * FROM t1

This is the Original Post describes the workaround and the limitation.