CODEWITHSUNDEEP
azureodbcazure-sql-databaseazure-synapse
Mister K
Mister K

Reputation: 13

Connecting to Azure Synapse SQL On-Demand leads to "syscharsets is not supported" error

I run an Azure Synapse Analytics using the SQL Pool and SQL On-Demand Pool. Both are running in an Azure managed virtual private network and for the SQL Pool as well as for the SQL On-Demand component so-called private endpoints have been set-up.

First I tried to connect to both private endpoints using ADO.NET which works perfectly.

Connection Test via ADO.NET -> success

Afterwards, I connected a BI tool which just supports ODBC.

Connection Test via ODBC -> failed

Anyway, the ODBC connection to the SQL Pool works fine.

The ODBC connection to the SQL On-Demand component resulted in a connection error "syscharsets is not supported".

Tried already with various ODBC settings without success, any ideas?

Thanks.

Upvotes: 1

Views: 1840

Answers (1)

Mike Ubezzi
Mike Ubezzi

Reputation: 1027

I want to provided a response to your inquiry as it applies to the difference between Azure Synapse Analytics SQL Pool versus SQL On-Demand Workspace:

SQL pool represents a collection of analytic resources that are being provisioned when using Synapse SQL. The size of SQL pool is determined by Data Warehousing Units (DWU).

SQL Pool has an instance of the SQL Engine running where SQL on-demand workspace is an instance of Azure Data Lake with limited T-SQL support:

SQL on-demand offers T-SQL querying surface area, which is slightly enhanced/extended in some aspects to accommodate for experiences around querying semi-structured and unstructured data. Furthermore, some aspects of the T-SQL language are not supported due to the design of SQL on-demand, as an example, DML functionality is currently not supported.

Please run the following in SQL Pool, as an example:

USE master;
SELECT  db.name [Database]
,       ds.edition [Edition]
,       ds.service_objective [Service Objective]
  FROM    sys.database_service_objectives   AS ds
  JOIN    sys.databases                     AS db ON ds.database_id = db.database_id;

And the following in the on-demand workspace:

/* Script to get full version information */
/* */
SELECT  SERVERPROPERTY('ProductVersion') AS "Product Version", 
    SERVERPROPERTY('ProductLevel') AS "Product Level", 
    SERVERPROPERTY('EngineEdition') AS "Engine Edition", 
    SERVERPROPERTY('Edition') AS "Edition",
    @@Version AS "Version";

With sys.syscharsets (Transact-SQL) being a function of the SQL Engine and the on-demand workspace only offering a T-SQL like interface to query structured and unstructured data, the following emphasizes what is not supported:

SQL on-demand has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts is not supported:

  • Tables
  • Triggers
  • Materialized views
  • DDL statements other than ones related to views and security
  • DML statements

"In the case of an on-demand workspace, for executing SQL on-demand queries, recommended tools are Azure Data Studio and Azure Synapse Studio."

Connecting client applications to the on-demand workspace is not unique but it is not going to provide the same experience as SQL pools, since SQP pools is a running SQL Engine instance as defined by SERVERPROPERTY.

Look for Edition:

SERVERPROPERTY:Edition

And for EngineEdition:

SERVERPROPERTY:EngineEdition

Additional information: Supported drivers and connection strings (Link).

There is not a lot of information regarding the use case of connecting client applications to the on-demand workspace but if there is additional information you have to share, please detail this as appropriate.

Upvotes: 1

Related Questions