Lesson Learned #11: Connect from Azure SQL DB using an external table where the source of the data is a SQL Datawarehouse
One of our customer tries to connect from Azure SQL DB using an external table where the source of the data is a SQL Datawarehouse database.
- This first question was if there is supported or not and I received the confirmation from Azure Product Team that there is not supported and they are working on it.
- The second question was, why our customer, after configuring the External Table, is facing the error message during the select query: 'Setting Language to N'us_english' is not supported.' ? I tried to reproduce the issue and I was able to find why.
- I created a table in my SQL DW database.
CREATE TABLE [Order]( [SourceOrderArticleId] [int] NULL, [SourceOrderId] [int] NULL, [BrandId] [tinyint] NULL) WITH ( DISTRIBUTION = ROUND_ROBIN, CLUSTERED COLUMNSTORE INDEX)
- Connected to my Azure SQL DB, I executed the following steps:
CREATE MASTER KEY ENCRYPTION BY PASSWORD='xxxxxxxxxx';
CREATE DATABASE SCOPED CREDENTIAL AppCredDW WITH IDENTITY = 'UserDW', SECRET = 'PasswordDW';
CREATE EXTERNAL DATA SOURCE RemoteReferenceDataDW WITH ( TYPE=RDBMS,
CREATE EXTERNAL TABLE [dbo].[Order]( [SourceOrderArticleId] [int] NULL, [SourceOrderId] [int] NULL, [BrandId] [tinyint] NULL) WITH ( DATA_SOURCE = RemoteReferenceDatadw);
- Every time that I executed the query: select * from [dbo].[Order] , I got the same issue that our customer, event trying to change the setting in the context I got the same problem.
- Enabling SQL Auditing for the SQL DataWarehouse database, I found the reason that our customer is getting the error message 'Setting Language to N'us_english' is not supported.'
- Every time that Azure SQL DB (using Elastic Database component) tries to connect to Azure SQL Datawarehouse, this component change the context of the connection running the following TSQLs statements:
DECLARE @productVersion VARCHAR(20)
SELECT @productVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20))
IF CONVERT(INT, LEFT(@productVersion, CHARINDEX('.', @productVersion) - 1)) >= 12
EXEC sp_executesql N'SET CONTEXT_INFO 0xDEC7E180F56D3946A2F5081A9D2DAB3600004F8F6CF3AC0205674E2CB44811FA5D45B64057F43BDF17E8'
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT ON;
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE N'us_english';
SELECT [T1_1].[SourceOrderArticleId] AS [SourceOrderArticleId],
[T1_1].[SourceOrderId] AS [SourceOrderId],
[T1_1].[BrandId] AS [BrandId]
FROM [dbo].[Order] AS T1_1
- The statement SET LANGUAGE N'us_english' is not supported in SQL Datawarehouse as it, but if you change, to SET LANGUAGE us_english there is possible. If you executed the command SET LANGUAGE N'us_english' is supported in Azure SQL DB.
- Most probably, this could have any other implications, but, if the Elastic Database component use SET LANGUAGE us_english instead of SET LANGUAGE N'us_english' we may able to use a SQL Datawarehouse as external table from Azure SQL DB.