I have a SQL Server 2017 database with a particular UDF that is giving me an error when trying to export a dacpac either via SSMS or sqlpackage. VisualStudio also throws the error when working in ssdt. The function works, and no issues there. included here
is a dumbed down version of the function that has the same issues. You can create an empty database, add the function, and dacpac extract will fail. I understand there are poor design choices here, but this is just an example of the script, that still reproduces
the error.
Script:
CREATE FUNCTION [dbo].[testFunction]
(
@string varchar(max)
)
RETURNS bit
AS
BEGIN
DECLARE @Valid BIT = CASE WHEN EXISTS (
SELECT *
FROM STRING_SPLIT(@string, ',') s
OUTER APPLY (select LEN(s.value) as l) as l
)
then 0 else 1 end
return @Valid
end
It literally just takes a comma separated string, splits it, does an outer apply to generate a length(I know, stupid IRL, but demo).
However, when attempting to extract a dacpac it gives error "Object reference not set to an instance of an object."
If I change the select * to select s.value, or select 1, then voila, dacpac generates and visual studio doesn't crash.
Anyone have any ideas as to why the select * in the above code would cause the dacpac to fail? We're evaluating using ssdt for source control, but random stuff like the above doesn't give me good feelings about using it, when it apparently is doing more code
validation than the sql engine itself.
Edit: Environments
SQL Server 2017 Standard
VS 2019 community with SSDT installed
SQLPackage Build 15.0.4826.1
