Unable to create external data source using Azure SQL Database Emulator

Question

I am trying to use the Azure SQL Database Emulator described here: https://learn.microsoft.com/en-us/azure/azure-sql/database/local-dev-experience-sql-database-emulator?view=azuresql

I am trying to emulate our production environment locally. So far, I have created two databases, a "Corp" database and a "Data" database.

Now, I am trying to link the two by creating an external data source on the "Data" database, like so:

/****** Object:  ExternalDataSource [Corp]    Script Date: 7/11/2024 10:58:08 AM ******/
CREATE EXTERNAL DATA SOURCE [Corp] WITH (TYPE = RDBMS, LOCATION = N'localhost', CREDENTIAL = [SQL_Credential], DATABASE_NAME = N'Corp')
GO

This SQL is generated from SSMS; I only replaced the LOCATION argument.

However, I get an error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RDBMS'.

External data sources are not listed under Limitations in the linked article, so I should be good to go. However, it does appear to be in a (preview) state (despite being introduced in 2022).

Has anyone else experienced this? If so, how did you work around it?

please check the solution and let me know if I can help you more. — Pratik Lad
– Pratik Lad, Commented Jul 22, 2024 at 11:54
@PratikLad based on "the solution", it sounds like this is a limitation of the Azure SQL Database Emulator (in which case, it should be noted under the limitations section). Let me know if that is correct. We have worked around this for now. — aorphan
– aorphan, Commented Jul 22, 2024 at 23:29

Pratik Lad · Accepted Answer · 2024-07-12 10:18:43Z

0

The error you are getting is because External Data Source type RDBMS is only supported in Azure SQL not in local SQL server. while publishing or building the project if you select the Publish to new SQL server local development container it will deploy it on local SQL server imaged docker and it will produce an error.

While publishing the project select the Publish to existing SQL server

enter image description here

After this option beside server select the Azure SQL Server connection and it will run without error.

Also see this MS QnA thread solution where @Alberto Morillo tested the same and it failed if both the databases are emulated Azure SQL Databases.

answered Jul 12, 2024 at 10:18

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to create external data source using Azure SQL Database Emulator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related