I am able to use the Data Export Service (DES) to export data to an Azure SQL Database. When I try to create a new profile in DES that connects to a Data Warehouse (aka Azure Synapse Analytics) on the same Azure SQL Server using the same user (db_owner member), I keep getting the message 'Error validating profile registration'. Note that the Key Vault Secrets used for each connection are virtually the same except the 'Initial Catalog' and the DES wizard is able to validate the connection to the Key Vault.
Can the Data Export Service in Microsoft Dynamics export data to an Azure Synapse Data Warehouse
azure-sql-data-warehousemicrosoft-dynamics
Related Solutions
Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):
function_body
Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.
Could you double check that function is created in DW?
Add a label to your query using the OPTION ( LABEL ... ) syntax which is supported in Azure SQL Data Warehouse. You can then monitor it either via the portal or using the DMV sys.dm_pdw_exec_requests, for example
SELECT *
FROM dbo.yourBillionRowTable
OPTION ( LABEL = 'Your Unique Query Label 042' )
-- In a separate window...
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] = 'Your Unique Query Label 042'
Results:
Or monitor it in the portal:
Related Question
- Azure SQL Data Warehouse – How to Add a Check Constraint
- Azure SQL Data Warehouse – Transaction Limits for CTAS Operations
- Azure SQL Data Warehouse – How to See True Query Plan
- Revision Tracking & Source Control for Azure SQL Data Warehouse
- How to Achieve a Many-to-One Merge Join in Azure Data Warehouse
- SQL Server – Difference Between Clustered Columnstore Index Table in Azure SQL and Azure Data Warehouse
- Azure SQL – How to Copy Real-Time Data to Azure Data Warehouse
Best Answer
The answer is no. The reason is that an Azure Synapse Data Warehouse does not allow you to create user data types and DES creates data types to use in it's stored procedures that help merge the incremental data into the SQL tables.