Parse error on dedicated SQL pool

HideyoshiAkaio 60 Reputation points
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
    @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 = (
    AND TABLE_NAME NOT IN (''OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'')
    AND TABLE_NAME LIKE ''%' + @BaseTableSuffix + '''
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?
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,524 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 2,835 Reputation points Microsoft Vendor

    Hi @HideyoshiAkaio

    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.

    User's image

    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:

    enter image description here

    Query to convert the table data in Json format:

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

    Result: enter image description here

    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.

1 additional answer

Sort by: Most helpful
  1. Vahid Ghafarpour 8,610 Reputation points

    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.

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

    0 comments No comments