Questions tagged [azure-synapse-analytics]

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.

Filter by
Sorted by
Tagged with
1 vote
1 answer
51 views

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 ...
SOUser's user avatar
  • 13
0 votes
0 answers
15 views

Distributions when only one physical node

Working on Azure Synapse, we have for now around 30 tables on a dev environment. I want to optimize the tables before replicating them in qal and prod env. As far as I understand, we only have one ...
Gregoire_M's user avatar
0 votes
1 answer
26 views

SQL DB Role to get View Definition permission

In Azure Synapse Serverless SQL Pool looking for an inbuilt DB Role that can be assigned to an SPN which can then read the definition of all views. I know that we do have the "GRANT VIEW ...
Prakash's user avatar
  • 101
0 votes
0 answers
42 views

Azure Synapse query to multiple JSON files in data lake, parse data into table format

I want to query RAW telemetry data stored in JSON format in my data lake. I am using Azure Synapse to query the files. I can successfully pull the data I want into a result table, but I struggle to ...
Damo's user avatar
  • 193
1 vote
0 answers
51 views

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 ...
leleusi's user avatar
  • 11
0 votes
1 answer
362 views

Generating MD5 Hash for NVARCHAR and VARCHAR columns across different databases

I am building a utility that generates a single MD5 hash across all CONCATENATED column values for any table row. To make this work, I have to eliminate NULL values using COALESCE and must CAST NUMBER ...
Lauren_G's user avatar
0 votes
1 answer
268 views

Database Scoped Credential with User-Assigned Managed Identity possible?

We have a data lake (Azure Data Lake Gen 2) that contains single folders with data for specific use cases in the 'gold' container, e.g. for a financial dashboard (folder 1) or a production dashboard (...
alexsilviu05's user avatar
0 votes
1 answer
460 views

Dedicated SQL Pool In Azure - Cannot use System Views to calculate size for tables

I know that it's a repeated question but I've tried all solution and workaround suggested from every possible question present on stackoverflow.com I have inherited a dedicated SQL Pool with a SQL ...
Fedeco's user avatar
  • 103
0 votes
0 answers
51 views

How to store LineString or Point data type in Azure Synapse?

I have JSON files that I need to store in Azure Synapse table. One record looks like this: { "type": "Feature", "properties": { "id": 12356}, "geometry&...
Zeevik.sha's user avatar
0 votes
1 answer
213 views

Hash, Replication, and Round Robin Distributions - Need more clarification

I will give a few examples of the tables we have: Table1 - Most queries are State Code and Year, has about 1000 rows, will grow by 1000 rows a year, used Round Robin Table2 - Most queries are by ...
xmlapi's user avatar
  • 11
1 vote
1 answer
121 views

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 ...
xmlapi's user avatar
  • 11
0 votes
1 answer
349 views

Azure Dedicated SQL pool - group has db_datareader access but cannot login

We have created Test AD Group and they should have readonly access to the database (schemas and tables, views) in the Azure SQL dedicated pool. Our DBA team did it but the users in the Test AD Group ...
xmlapi's user avatar
  • 11
0 votes
2 answers
696 views

How to rtrim the slash character (/)?

rtrim('txt/','/') What if we cannot use this version of the rtrim function.. How else to remove the trailing '/' from the end of a string (if present)?
simpatico's user avatar
  • 101
0 votes
0 answers
744 views

Accessing Secured Storage account from Synapse Notebook

We have a Firewalled storage account which has a few files that we need to access via Synapse notebook. We do that with below code : abfss_path = f'abfss://[email protected]....
Ramakant Dadhichi's user avatar
1 vote
0 answers
201 views

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 ...
kbrimington's user avatar
5 votes
1 answer
779 views

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] ...
András Váczi's user avatar
0 votes
1 answer
31 views

Synapse column store datetime encoding

I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-...
Michael Green's user avatar
1 vote
0 answers
258 views

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 &...
pythondumb's user avatar
2 votes
1 answer
1k views

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 ...
pythondumb's user avatar
1 vote
3 answers
2k views

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 ...
Mike's user avatar
  • 658
1 vote
1 answer
245 views

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 ...
Lauren_G's user avatar
1 vote
2 answers
5k views

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 for? sys....
Santosh Hegde's user avatar
0 votes
0 answers
788 views

Azure Synapse - Need to have table access to View?

I've been trying to find information on this for quite some time but I couldn't find any confirmed results. I know that we can grant access to view without granting explicit access to the base table ...
zuheir's user avatar
  • 1
3 votes
1 answer
1k views

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 ...
Michael Green's user avatar
2 votes
0 answers
224 views

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 (...
Michael Green's user avatar
2 votes
3 answers
2k views

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 ...
Jayvee's user avatar
  • 121
1 vote
0 answers
19 views

Getting insight into partitions of a table in Azure Synapse (similar to SQL Server) [duplicate]

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 ...
Kashyap's user avatar
  • 149
1 vote
0 answers
15 views

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 ...
Michael Green's user avatar
0 votes
1 answer
3k views

Create an Azure Synapse temp table from a SELECT with ORDER BY

I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly ...
Lauren_G's user avatar
0 votes
3 answers
926 views

Split intervals

I have two tables. Each holds some attributes for a business entity and the date range for which those attributes were valid. I want to combine these tables into one, matching rows on the common ...
Michael Green's user avatar
0 votes
1 answer
3k views

Getting error USE statement is not supported to switch between databases with Azure Synapse formerly SQLDW

Whenever I attempt to submit a query to Azure Synapse SQLDB (or try to do anything with the database from my application called DBForge) I get the following error message: USE statement is not ...
Carltonp's user avatar
  • 101