For questions about Azure Synapse (formerly Azure SQL Data Warehouse). It is an analytics service that brings together enterprise data warehousing and Big Data analytics.
Questions tagged [azure-synapse-analytics]
32 questions
5
votes
1 answer
User can drop table from a schema, but cannot create one
In an Azure Synapse dedicated SQL pool I have the following setup:
-- a custom DB role to manage privileges
CREATE ROLE [owner];
-- there is a schema owned by this role
CREATE SCHEMA [myschema] AUTHORIZATION [owner];
-- an Azure AD group to allow…
András Váczi
- 31,778
- 13
- 102
- 151
3
votes
1 answer
How are deadlocks detected by Synapse
I'm trying to produce a deadlock in Azure Synapse. I would have expected it to work much as on-prem SQL Server does given the documentation says it "applies to" Synapse. I've waited an inordinately long time (over 4 hours) yet deadlock detection has…
Michael Green
- 25,255
- 13
- 54
- 100
2
votes
1 answer
Azure Synapse SQL Pool: Issue in MERGE INSERT UPDATE statement
I have a table in Azure Synapse Dedicated SQL POOL.
CREATE TABLE table_A
(
ID int IDENTITY(1,1),
ClientID varchar(10),
fitrstname varchar(20),
lastname varchar(30),
phone varchar(20),
address varchar(100),
milageRun …
pythondumb
- 129
- 3
2
votes
0 answers
Exactly-once FIFO queue in Synapse
Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically
no table hints (READPAST etc.)
no OUTPUT clause
sp_getapplock is…
Michael Green
- 25,255
- 13
- 54
- 100
2
votes
3 answers
How to run legacy SSIS packages with Azure Synapse
I would like to know how to run an SSIS package from Azure Synapse Studio or whether it's at all possible. Apparently there is no support for SSIS runtime in Synapse but I suppose there should be a way to run legacy packages since there was in Azure…
Jayvee
- 121
- 3
1
vote
1 answer
Increment a field in runtime for Synapse SQL dedicated SQL Pool
I have a task to increment the expected flag column by looking in to existing values.
I solved this using while loop and I can get the expected value updated using while loop in Azure synapse-Sql Data warehouse.
But I would like to know a direct…
SOUser
- 31
- 6
1
vote
0 answers
Schema Drift & Delta Table update - cannot resolve `target.new_column` in UPDATE clause given columns [list of columns]
Using Azure Synapse Analytics, I have configured a pipeline and a Mapping Data Flow that were working great until I recently added a Derived Column to my Mapping Data Flow, which created a column that was not originally present when I created the…
leleusi
- 11
- 2
1
vote
1 answer
Azure Dedicated SQL Pool - How to checksum a table?
We're moving all our on premise tables into the azure dedicated sql pool. We're using synapse workspace pipelines to import the data into the dedicated pool.
Is there a way to checksum a table to compare it against on premise table to make sure…
xmlapi
- 11
- 1
1
vote
0 answers
How do I change the database owner in Synapse Analytics Serverless SQL Pool?
I am working with Azure Synapse Serverless SQL Pool. A former employee created the databases in the pool, and is listed as their owner. Consequently, I cannot drop the login.
I would like to change the owner of the databases to [sa]; however, all…
kbrimington
- 111
- 3
1
vote
0 answers
Synapse Dedicated SQL Pool: temporary CREATE TABLE failed despite number of columns are well below the maximum limit
I am performing an UPSERT operation in a Synapse Dedicated SQL Pool.
The TARGET table has 394 column and I am trying to update the TARGET with 5 Unique Keys.
MERGE table_A AS TARGET using
(SELECT ,... FROM table_B) AS SOURCE…
pythondumb
- 129
- 3
1
vote
3 answers
What are the limitations in Synapse Analytics compared to normal SQL Server?
I'm keen to get a list of the feature limitations or suprises when using Synapse Analytics (a.k.a. Dedicated SQL Pool, used to be known as SQL DW / SQL Data Warehouse) compared to using normal SQL Server. Basically the things that you would expect…
Mike
- 692
- 4
- 15
1
vote
1 answer
How to retain same FLOAT value after converting it to a VARCHAR in Azure Synapse
Consider the following example:
DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
AMOUNT FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
SELECT AMOUNT, CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR
FROM…
Lauren_G
- 69
- 1
- 9
1
vote
3 answers
How to get queries executed in Synapse SQL?
I need to find all the query executed on my DB in last 1Hr in Synapse workspace with SQL pool. Will apply my logic on top of the result set.
What's the right table/view to look…
Santosh Hegde
- 131
- 1
- 4
1
vote
0 answers
Getting insight into partitions of a table in Azure Synapse (similar to SQL Server)
I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure:
While the syntax of partitioning may be slightly different from SQL Server, the…
Kashyap
- 145
- 6
1
vote
0 answers
Resource overlap between Synapse databases
I have an Azure Synapse dedicated pool with a single database. It is scaled to DW500c which the documentation says means it has a single compute node.
If I add a second database how does work against it affect the first DB's resources? Will the…
Michael Green
- 25,255
- 13
- 54
- 100