Azure Synapse Dedicated SQL Pool Parse Error

Question

When execute the script below on Azure Synapse Dedicated SQL Pool I get the error:

Parse error at line: 11, column: 1: Incorrect syntax near 'FOR'.

However, I if execute the same code on Azure Synapse Serverless Pool it works fine.

DECLARE
    @EnrichedViewDatabase sysname, 
    @EnrichedViewSchema sysname, 
    @EnrichedColumnSuffix varchar(50),  
    @LanguageCode varchar(10), 
    @BaseTableSuffix varchar(50),
    @PreviewOnly bit, --Indicate whether to preview the SQL Script (without creating the views) = 1 ; Create views = 0;
    @CurrentDatabase sysname,
    @CurrentDatabaseSchema sysname

SET @EnrichedViewDatabase = 'DedicatedSQLPool'
SET @EnrichedViewSchema = 'dbo'
SET @EnrichedColumnSuffix = 'code'
SET @LanguageCode = 1033
SET @BaseTableSuffix = ''
SET @PreviewOnly = 0 
SET @CurrentDatabase = 'dataverse_montaguqa1_unq5a4857c03d0545c6b9ad616ca59a7'
SET @CurrentDatabaseSchema = 'dbo'

DECLARE @ColumnMetadata nvarchar(MAX), @ColumnMetadataSQL nvarchar(MAX)

--Define the SQL statement to retrieve column metadata from the Lake Database managed by Synapse Link for Dataverse
--Results will be stored as a JSON document in a variable
SET @ColumnMetadataSQL = 'SET @ColumnMetadataOUT = (
SELECT TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    ORDINAL_POSITION, 
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = ''dbo''
    AND TABLE_NAME NOT IN (''OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'')
    AND TABLE_NAME LIKE ''%' + @BaseTableSuffix + '''
FOR JSON AUTO)'
DECLARE @ParmDefinition NVARCHAR(MAX);  
SET @ParmDefinition = N'@ColumnMetadataOUT NVARCHAR(MAX) OUTPUT';  
EXECUTE sp_executesql @ColumnMetadataSQL, @ParmDefinition, @ColumnMetadataOUT=@ColumnMetadata OUTPUT;  

--Declare a variable to store a SQL statement for creating enriched views
DECLARE @SQL nvarchar(MAX) = ''

Any thoughts

Answer 1

For Clause is available in SQL server, Azure SQL Database, Azure SQL MI.

• It is not supported in Azure Synapse Analytics.

Image Reference: FOR Clause (Transact-SQL) - SQL Server | Microsoft Learn

In synapse dedicated SQL pool, you can use concat and string_agg functions and create Json data manually instead of using For JSON auto. I tried this with sample data.

Sample source data:

Query to convert the table data in Json format:

select concat('{',string_agg(concat('{id:"',id,'",name:"',name,'"}'),','),'}')
as JSON_data from tab1

Result:

You can log a feature request here.