Synpase views error Duplicate column ordinal cannot be provided in WITH schema clause

Question

I am new to synapse and I am trying to create a synapse view from a delta table using openrow set.

Getting the error Duplicate column ordinal cannot be provided in WITH schema clause Not sure why I should see this error, I am not using any with clause and I guess with clause if not specified picks all cols.

create or ALTER view testView
 SELECT CAST(abc AS nvarchar(255)) AS abc,
   CAST(def AS nvarchar(255)) AS def,
   CAST(Date1 AS datetime) AS DateJoined  --this is a timestamp col in delta table
FROM
  OPENROWSET( BULK 'tablename', DATA_SOURCE = 'datasource', FORMAT='DELTA') AS m 

Answer 1

Duplicate column ordinal cannot be provided in WITH schema clause

I tried above scenario and it's working fine for me. For the delta format table, it uses the parquet file to get the data and, In parquet file it binds schema with the column name.

• Make sure your data is in correct format.
• Chech column ordinal of you view below this query.

SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =  'tablename'

• If there is any inconsistency you can delete that and alter the column ordinal using below query.

ALTER  TABLE tablename ALTER  COLUMN columnname datatype AFTER columntoplaceyourcolumnafter

I have delta table stored in ADLS as below:

Then for reading that I used below code:

CREATE  OR  ALTER  VIEW testView2 AS
SELECT  CAST(id AS  nvarchar(255)) AS id,
CAST(name  AS  nvarchar(255)) AS  name,
CAST(date  AS  datetime) AS DateJoined --this is a timestamp col in delta table
FROM
OPENROWSET( BULK  'tablename', DATA_SOURCE = 'datasourcename', FORMAT='DELTA') AS m;

OUTPUT: