Error (Incorrect syntax): a SQL query works on Develop but not on Script activity

Question

Hello everybody!

For our project we have to run a CREATE VIEW query, which works if we execute it manually on the Develop section.
However, if we use the very same query on a Script activity in our pipeline (which targets a Serverless SQL -using an Azure SQL Server as a Linked service-), the activity fails and gives this message:

{
"errorCode": "2011",
"message": "Incorrect syntax near 'FORMAT'.",
"failureType": "UserError",
"target": "VIEW_create",
"details": []
}

How come it works when we run it on the Develop section but not on the pipeline?
I'll share the query below:

CREATE VIEW view_test_1 AS SELECT *, res.filepath(1) AS [year], res.filepath(2) AS [month], res.filepath(3) AS [day] FROM OPENROWSET( BULK '01_landing/test/year=*/month=*/day=*/*.parquet', DATA_SOURCE = 'files', FORMAT='PARQUET') AS [res] GO;

Thank you so much in advance!

Answer 1

Hi @Mateo Estrada Bazan ,

Thank you for posting query in Microsoft Q&A Platform.

I tried creating view using Azure SQL Database connector type linked service. Its working fine for me. I used system assigned managed Identity as authentication type in linked service.

Since, my managed identity has storage blob data contributor role on storage its not thrown any error and I was able to create successfully view from script activity.

Please check below screenshots.

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.