Jonathan Sayce Jonathan Sayce - 3 years ago 663
SQL Question

Error SQL71501 on exporting Azure SQL Database

I'm getting a strange error when exporting an Azure SQL Database. Exports had been working fine until some recent schema changes, but it's now giving me Error SQL71501.

The database is V12, Compatibility Level 130 (although the

database is still Compatibility Level 120).

The problem seems to be caused by a new table-valued function, which uses the built in
function. There were already stored procedures using
and they don't seem to have affected the export, but the function (which compiles OK, and is working fine) seems to cause a problem with the export.

The function below is a simplified version of the real one, but causes the same problem.

CREATE FUNCTION [dbo].[TestFunction](
@CommaSeparatedValues VARCHAR(MAX)

SELECT c.ClientId,

FROM dbo.Client c

INNER JOIN STRING_SPLIT(@CommaSeparatedValues, ',') csv
ON c.ClientId = csv.value

The complete error message given in the Import/Export history blade is as follows:

Error encountered during the service operation.

One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71501: Error validating element [dbo].[TestFunction]: Function: [dbo].[TestFunction] has an unresolved reference to object [dbo].[STRING_SPLIT].

Error SQL71501: Error validating element [dbo].[TestFunction]: Function: [dbo].[TestFunction] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Client].[csv], [dbo].[STRING_SPLIT].[csv] or [dbo].[STRING_SPLIT].[value].

Answer Source

This is Xiaochen from Microsoft SQL team. We are already working on the fix of this issue. The fix will be deployed to the export service in next few weeks. In the same time, the fix is already available in the latest DacFX 16.4 ( Before we fix this issue in the service, you can download the DacFX 16.4 and use sqlpackage to work around.

Regards, Xiaochen

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download