Lesson Learned #11: Connect from Azure SQL DB using an external table where the source of the data is a SQL Datawar...

[Jose_Manuel_Jurado]

First published on MSDN on Dec 30, 2016
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,

LOCATION='serverdw.database.windows.net',

DATABASE_NAME='dwsource',

CREDENTIAL= AppCredDW);

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.