Error when I use synapse serverless sql pool OPENROWSET to read from a data lake structured stream with similar column name but only different casing

Hi @Salander He

Thank you for contacting us about the error you encountered while using Synapse Serverless SQL Pool's OPENROWSET function to read data from your Azure Data Lake structured stream.

We understand the issue arises because the data in the stream has columns with names that differ only in casing (uppercase vs lowercase). Synapse Serverless SQL Pool treats these names as distinct columns, causing a mismatch with your query.

Here are two ways to resolve this issue:

Adjust Column Names in the WITH Clause:

• Carefully examine the column names in your data lake structured stream.
• Modify the WITH clause within your OPENROWSET statement to explicitly list all the columns you want to read, ensuring they match the exact casing used in the data lake (including uppercase and lowercase letters).

Use T-SQL FORCE CASE INSENSITIVE NAMES ON (Limited Applicability):

• If modifying the data lake schema isn't feasible, you can potentially use the T-SQL FORCE CASE INSENSITIVE NAMES ON statement within your Synapse SQL Pool query.
• However, it's important to note that this approach has limitations and might not be supported in all scenarios.

I hope this helps. Please let us know if you have any further questions.