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

Published 03-13-2019 06:16 PM 156 Views
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.


%3CLINGO-SUB%20id%3D%22lingo-sub-368827%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2311%3A%20Connect%20from%20Azure%20SQL%20DB%20using%20an%20external%20table%20where%20the%20source%20of%20the%20data%20is%20a%20SQL%20Datawarehouse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368827%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20One%20of%20our%20customer%20tries%20to%20connect%20from%20Azure%20SQL%20DB%20using%20an%20external%20table%20where%20the%20source%20of%20the%20data%20is%20a%20SQL%20Datawarehouse%20database.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EThis%20first%20question%20was%20if%20there%20is%20supported%20or%20not%20and%20I%20received%20the%20confirmation%20from%20%3CSTRONG%3E%20Azure%20Product%20Team%20that%20there%20is%20not%20supported%20and%20they%20are%20working%20on%20it%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EThe%20second%20question%20was%2C%20why%20our%20customer%2C%20after%20configuring%20the%20External%20Table%2C%20%3CSTRONG%3E%20is%20facing%20the%20error%20message%20during%20the%20select%20query%3A%20'Setting%20Language%20to%20N'us_english'%20is%20not%20supported.'%20%3F%20%3C%2FSTRONG%3E%20I%20tried%20to%20reproduce%20the%20issue%20and%20I%20was%20able%20to%20find%20why.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CB%3E%20%3CSTRONG%3E%20I%20created%20a%20table%20in%20my%20SQL%20DW%20database.%20%3C%2FSTRONG%3E%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%5BOrder%5D(%20%5BSourceOrderArticleId%5D%20%5Bint%5D%20NULL%2C%20%5BSourceOrderId%5D%20%5Bint%5D%20NULL%2C%26nbsp%3B%20%5BBrandId%5D%20%5Btinyint%5D%20NULL)%20WITH%20(%26nbsp%3B%20DISTRIBUTION%20%3D%20ROUND_ROBIN%2C%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CLUSTERED%20COLUMNSTORE%20INDEX)%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CB%3E%20%3CSTRONG%3E%20Connected%20to%20my%20Azure%20SQL%20DB%2C%20I%20executed%20the%20following%20steps%3A%20%3C%2FSTRONG%3E%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ECREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%3D'xxxxxxxxxx'%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20AppCredDW%20WITH%20IDENTITY%20%3D%20'UserDW'%2C%26nbsp%3B%20SECRET%20%3D%20'PasswordDW'%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20RemoteReferenceDataDW%20WITH%20(%26nbsp%3B%20TYPE%3DRDBMS%2C%3C%2FP%3E%3CBR%20%2F%3E%20LOCATION%3D'serverdw.database.windows.net'%2C%20%3CBR%20%2F%3E%3CP%3EDATABASE_NAME%3D'dwsource'%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREDENTIAL%3D%20AppCredDW)%3B%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3ECREATE%20EXTERNAL%20TABLE%20%5Bdbo%5D.%5BOrder%5D(%20%5BSourceOrderArticleId%5D%20%5Bint%5D%20NULL%2C%20%5BSourceOrderId%5D%20%5Bint%5D%20NULL%2C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BBrandId%5D%20%5Btinyint%5D%20NULL)%20WITH%20(%26nbsp%3BDATA_SOURCE%20%3D%20RemoteReferenceDatadw)%3B%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEvery%20time%20that%20I%20executed%20the%20query%3A%20%3CSTRONG%3E%20select%20*%20from%20%5Bdbo%5D.%5BOrder%5D%20%3C%2FSTRONG%3E%20%2C%20I%20got%20the%20same%20issue%20that%20our%20customer%2C%20event%20trying%20to%20change%20the%20setting%20in%20the%20context%20I%20got%20the%20same%20problem.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEnabling%20SQL%20Auditing%20for%20the%20SQL%20DataWarehouse%20database%2C%20I%20found%20the%20reason%20that%20our%20customer%20is%20getting%20the%20error%20message%20%3CSTRONG%3E%20'Setting%20Language%20to%20N'us_english'%20is%20not%20supported.'%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEvery%20time%20that%20Azure%20SQL%20DB%20(using%20Elastic%20Database%20component)%20tries%20to%20connect%20to%20Azure%20SQL%20Datawarehouse%2C%20this%20component%20change%20the%20context%20of%20the%20connection%20running%20the%20following%20TSQLs%20statements%3A%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40productVersion%20VARCHAR(20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40productVersion%20%3D%20CAST(SERVERPROPERTY('ProductVersion')%20AS%20VARCHAR(20))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20CONVERT(INT%2C%20LEFT(%40productVersion%2C%20CHARINDEX('.'%2C%20%40productVersion)%20-%201))%20%26gt%3B%3D%2012%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXEC%20sp_executesql%20N'SET%20CONTEXT_INFO%200xDEC7E180F56D3946A2F5081A9D2DAB3600004F8F6CF3AC0205674E2CB44811FA5D45B64057F43BDF17E8'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20ANSI_NULLS%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20ANSI_WARNINGS%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20ANSI_PADDING%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20ARITHABORT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20CONCAT_NULL_YIELDS_NULL%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NUMERIC_ROUNDABORT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20DATEFIRST%207%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20DATEFORMAT%20mdy%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20LANGUAGE%20N'us_english'%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%5BT1_1%5D.%5BSourceOrderArticleId%5D%20AS%20%5BSourceOrderArticleId%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5BT1_1%5D.%5BSourceOrderId%5D%20AS%20%5BSourceOrderId%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5BT1_1%5D.%5BBrandId%5D%20AS%20%5BBrandId%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%26nbsp%3B%26nbsp%3B%20%5Bdbo%5D.%5BOrder%5D%20AS%20T1_1%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EThe%20statement%20%3CSTRONG%3E%20SET%20LANGUAGE%20N'us_english'%20%3C%2FSTRONG%3E%20is%20not%20supported%20in%20SQL%20Datawarehouse%20as%20it%2C%20but%20if%20you%20change%2C%20to%20%3CSTRONG%3E%20SET%20LANGUAGE%20us_english%20%3C%2FSTRONG%3E%20there%20is%20possible.%20If%20you%20executed%20the%20command%20%3CSTRONG%3E%20SET%20LANGUAGE%20N'us_english'%20%3C%2FSTRONG%3E%20is%20supported%20in%20Azure%20SQL%20DB.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMost%20probably%2C%20this%20could%20have%20any%20other%20implications%2C%20but%2C%20if%20the%20Elastic%20Database%20component%20use%20%3CSTRONG%3E%20SET%20LANGUAGE%20us_english%20%3C%2FSTRONG%3E%20instead%20of%20%3CSTRONG%3E%20SET%20LANGUAGE%20N'us_english'%20%3C%2FSTRONG%3E%20we%20may%20able%20to%20use%20a%20SQL%20Datawarehouse%20as%20external%20table%20from%20Azure%20SQL%20DB.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FUL%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368827%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202016%20One%20of%20our%20customer%20tries%20to%20connect%20from%20Azure%20SQL%20DB%20using%20an%20external%20table%20where%20the%20source%20of%20the%20data%20is%20a%20SQL%20Datawarehouse%20database.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Enus_english%20is%20not%20supported%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eset%20language%20nus_english%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%20datawarehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%20dw%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 06:16 PM
Updated by: