EDIT 20210601, as an update:
This has been confirmed to be a bug, few weeks ago we've got an email from a support engineer saying they have implemented new logic for Elastic Query (still in Public Preview) which had this side effect. The workaround is to specify conversions (ex. nvarchar for text etc). For our case - the logic has been reverted so our solutions work on the previously affected servers, as I understand there will be a new release which will not have this bug.
Original question:
I have 3 Azure SQL databases: A, B and C
Database C is the source of data, databases A and B have external tables to that database. The definition of external tables is identical.
In the last couple of days something has changed - queries used to load data do not work in database A anymore. However, they still work in database B.
To start with, this query works in both databases:
SELECT * FROM < external table to database C >
But this query works only in database B but not database A:
SELECT 'test' FROM < external table to database C >
The error message:
Msg 46836, Level 16, State 2, Line 1
External table schema does not match actual schema from remote table: Mismatch between actual and expected type of column 1 in the remote query result. Expected: VARCHAR, Actual: NVARCHAR
Same error when trying other datatypes like datetime - it wants datetime2.
So, I've tried to convert the data type first and then it works in both databases:
SELECT CAST('test' AS nvarchar(10)) FROM < external table to database C >
This still does not work (same error which is weird because it says expecting varchar):
SELECT CAST('test' AS varchar(10)) FROM < external table to database C >
Any ideas what causes this?
Specifically why the difference between databases for the same query? And how can this change happen, I'm 100% sure there were no changes in configuration of the server or databases from my side.
