Parse error on dedicated SQL pool

Question

When running the script below on Azure Synapse Dedicated SQL Pool, I encounter the following error:
“Parse error at line: 11, column: 1: Incorrect syntax near ‘FOR’.”
However, if I execute the same code on Azure Synapse Serverless Pool, it functions correctly.

-- The provided script
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

-- Initialization of variables
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 insights on this issue?

Answer 1

Hi @Thomaskyle

Thank you for reaching out to us with your query.  

As per your query the error message "Incorrect syntax near 'FOR'" indicates that the FOR clause is not supported in the Dedicated SQL Pool of Azure Synapse Analytics.

However, the FOR clause is supported in other SQL Server-based platforms, including Azure SQL Database and Azure SQL Managed Instance.

Reference: SELECT - FOR Clause (Transact-SQL)

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:

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Welcome, and thanks for posting your question in the Microsoft Q&A forum.

Transact-SQL language is used in a serverless SQL pool, and a dedicated model can reference different objects and has some differences in the set of supported features. On this page, you can find high-level Transact-SQL language differences between consumption models of Synapse SQL.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **