Jonathan Sayce Jonathan Sayce - 1 month ago 39
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

master
database is still Compatibility Level 120).

The problem seems to be caused by a new table-valued function, which uses the built in
STRING_SPLIT
function. There were already stored procedures using
STRING_SPLIT
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)
)
RETURNS TABLE
AS
RETURN

SELECT c.ClientId,
c.FullName

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

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 (https://blogs.msdn.microsoft.com/ssdt/2016/09/20/sql-server-data-tools-16-4-release/). Before we fix this issue in the service, you can download the DacFX 16.4 and use sqlpackage to work around.

Regards, Xiaochen

Comments