In Visual Studio 2022, SSDT, SQL Project External Tables - Build Error!

Question

Title: External Table Build Error in SSDT – SQL46010 near ')' but works in SSMS

Hi,

I’m working with Visual Studio 2022, SSDT, and SQL Server Projects, and I’m trying to create external tables in an Azure SQL Database.

The external table creation works fine when run directly in SSMS. However, when I include the same CREATE EXTERNAL TABLE definition in my SQL project, I get a build error:

SQL46010: Incorrect syntax near ')'.

Here’s a simplified version of the syntax being used:

sql

CREATE EXTERNAL TABLE [ExternalTable].[XT_Booking_Person_Moniker]
(
    -- Column definitions here
    [LastUpdated_DT] DATETIME2(7) NULL
)
WITH
(
    DATA_SOURCE = [Apps_DB_ExternalDataSource],
    Schema_Name = '...',
    Object_Name = '...'
);

The external table references another database on the same Azure SQL logical server. It builds fine in SSMS, but SSDT fails during project build. I’ve verified the EXTERNAL DATA SOURCE and necessary credentials exist and work.

Has anyone encountered this? Is this a limitation of SSDT or SQL Server Projects? Any workaround or guidance would be appreciated!

Thanks in advance.

Answer 1

In project properties, which "Target platform" have you selected? It should be Azure SQL, see

https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/target-platform?view=sql-server-ver17&pivots=sq1-visual-studio