Hello,
I created several external tables in Synapse On-Demand/Serverless pool. Most of them work as expected, however i do have an issue with one external table that points to a CSV file under specific circumstances.
The table i created is: dbo.customer:
create external table [dbo].[customer]
(
[sales_org_id] nvarchar,
[debitor_id] nvarchar,
[group_id] nvarchar,
[customer_name1] nvarchar,
[customer_name2] nvarchar,
[street] nvarchar,
[zip_code] nvarchar,
[city] nvarchar,
[country] nvarchar,
[customer_group_id] nvarchar,
[customer_Group_name] nvarchar,
[branch_id] nvarchar,
[branch_text] nvarchar,
[kl] nvarchar,
[sales_group_id] nvarchar,
[role_id] nvarchar,
[customer_id] nvarchar,
[lvm] nvarchar,
[lvm2] nvarchar,
[hier_1] nvarchar,
[hier_1_name] nvarchar,
[hier_2] nvarchar,
[hier_2_name] nvarchar,
[hier_3] nvarchar,
[hier_3_name] nvarchar,
[hier_4] nvarchar,
[hier_4_name] nvarchar,
[hier_5] nvarchar,
[hier_5_name] nvarchar,
[hier_6] nvarchar,
[hier_6_name] nvarchar,
[hier_7] nvarchar,
[hier_8_name] nvarchar
)
with (data_source = [bbc_sas],LOCATION = N'customer.csv',FILE_FORMAT = [csv_comma_fr2])
When i select the data in Synapse Studio it works (apparently), even with full resultset:
When i do the same in SSMS i get an error:
Query execution failed with error code 15833
If i execute the same statement but with top 1000 (or less) it works in SSMS too. Any number above 1000 will lead to the error.
In the SQL-Request monitoring of Synapse i sometimes see an error regarding "cardinality estimation" when selecting the whole resultset.
So i tried to create statistics on that external table and doing that i also get an error:
Internal error number 3602 encountered while creating statistics.
Azure Data Studio behaves the same as the SQL query in Synapse studio, meaning the full resultset query works, however the error message when trying to create statistics remains.
I get the same error message when trying to create statistics on openrowset (on the same file).
