How to create External table with different collation in synapse
We can’t create external tables with a different collation on the table level, collation is only applicable on the column level in the external tables. See the below example reference. USE [mydbname];GOCREATE EXTERNAL TABLE populationExternalTable([country_code] VARCHAR (5) COLLATE Latin1_General_100_CI_AI_SC_UTF8,[country_name] VARCHAR (100) COLLATE Latin1_General_100_CI_AI_SC_UTF8,[year] smallint,[population] bigint)WITH (LOCATION = ‘csv/population/population.csv’,DATA_SOURCE = sqlondemanddemo,FILE_FORMAT = QuotedCSVWithHeaderFormat); See this microsoft…
Self hosted integration runtime consuming memory and not releasing
Here is a problem statment: The self hosted integration IR that we are using for copy activity is using all of the memory on the machine on which IR is hosted. It is not releasing memory Here are few things to check If your memory and CPU are consistently high then it could be due…
Azure Synapse Analytics: Publishing error Error while publishing: Cannot read properties of undefined (reading ‘constructor’)
This is due to broken resources in collaberation branch which is causing the issue. To resolve this, re-sync Git and Live mode by disconnecting and reconnecting Git.
Dynamic Data sync between Azure synapse analytics to SQL server
We have a requirement to set up a pipeline in ADF that copy all tables of Azure synapse analytics to SQL server dynamically.It should select all tables dynamically from synapse analytics db and copy the same table at the destination SQL server. It should be set up as incremental load. This can be achieved by using…
How synapse handles duplicate records with Primary and Unique constraints
Having primary key and/or unique key allows dedicated SQL pool engine to generate an optimal execution plan for a query. but it does not guarantee uniqueness or perform record uniqueness checks during insert operations. users needs to make sure primary key column or a unique constraint column values are unique. In Synapse, PRIMARY KEY is…
Azure Synapse Error : Could not allocate a new page for database ‘tempdb’ because of insufficient disk space
While creating NonClustered Index in synapse dedicated SQL pool, getting the below error:Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.…
azure sql server vs synapse dedicated sql pool
Azure Synapse Dedicated SQL Pool Azure Synapse Dedicated SQL Pool (Previously Azure SQL Data Warehouse), a massively parallel processing database similar to other columnar-based scale-out database technologies such as Snowflake, Amazon Redshift, and Google BigQuery. To the end-user it is much like a traditional SQL Server, however, behind the scenes it distributes the storage and…
How to Drop a Partition in Azure Synapse Dedicated SQL Pool
Dedicated SQL pool supports partition splitting, merging, and switching. Each of these functions is executed using the ALTER TABLE statement. To delete an existing partition, we need to use “Partition switching” when the table is not empty. If the table is empty, we can merge the empty partitioning using the Merge command. Demo: How to…
Synapse workspace deployment Error- connect ENETUNREACH
Issue: We have installed and added the synapse workspace deployment in our DevOps environment and created a release pipeline to push the ARM template (Development) to the Production environment. But during deployment, we are getting the below error. [error]Encountered with exception:Error: Get workspace location error: Error: connect ENETUNREACH 169.254.169.254:80 Solution: Follow the below steps to…
Synapse – unable to create datalake storage account Gen2(error: There was an error trying to validate storage account name)
Go to Subscription–> on the search, look for “Resource providers” Select ‘Microsoft.Storage’ and on the top, click on “Register” Once we register “Microsoft.Storage”, it should resolve the storage error.