Bug when generating DACPAC with particular Scalar UDF. RRS feed

  • Question

  • 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. 


    CREATE FUNCTION [dbo].[testFunction]
    	@string varchar(max)
    RETURNS bit
    		SELECT *
    		FROM STRING_SPLIT(@string, ',')	s
    		OUTER APPLY (select LEN(s.value) as l) as l
    	then 0 else 1 end
    	return @Valid

    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

    • Edited by OhThatITGuy Tuesday, August 11, 2020 6:40 PM
    Tuesday, August 11, 2020 6:29 PM

All replies

  • Do you have a chance to test with SQL 2019 (can try developer's version - free) and also is your SQL Server version 2017 up to date (e.g. latest CU installed)?

    I didn't attempt to reproduce - I do believe you, but if it's reproducible consistently it sounds like a bug and you may want to open a case with MS.

    BTW, my personal preference is to use EXISTS (select 1 ...) - just a habit.

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    • Edited by Naomi N Tuesday, August 11, 2020 6:38 PM
    Tuesday, August 11, 2020 6:37 PM
  • Hi,

    I used the latest SSMS (18.6) to test the creation of the DACPAC file  on a server with the latest CU6 SQL Server 2019 instance installed, and it did reproduce the problem you mentioned.The creation will succeed if change the SELECT * to SELECT 1.

    I am not sure this is a bug. But for SQL server bug, we can submit issues to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server .

    Best regards,

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Proposed as answer by Naomi N Wednesday, August 12, 2020 12:54 PM
    Wednesday, August 12, 2020 9:56 AM
  • Creating DACPAC using Visual Studio
    This option is the most robust. Although a final file (DACPAC) will be the same at the end – Visual Studio offers more options than the predecessor. One of the more important one is an opportunity to turn off “Verify extraction”, which enabled (and hidden) in SSMS leads to failure in action. By disabling that option you will avoid the issue and it doesn’t matter whether the database contains errors or not – the DAC package file will be generated.
    Open extract window by using menu: View -> SQL Server Object Explorer, select required database and right-click on it and choice Extract Data-tier Application…

    Visual Studio: Extract Data-tier Application

    Then select all option as shown below:

    More options with extract DAC in Visual Studio – SQL Server Data Tools

    Here, you have got much more options.

    Extract schema only
    Like previously – it’s generating a model only.

    Extract schema and data
    This option allows you to generate the data. Be careful! Unexpectedly your DACPAC file might grow to huge size and process can takes long time. Use this option wisely.
    What a minute… data? The data, you said? Yes! But hold your horses. Personally, I have never used this option and apparently it has got limitations. One of them:

    Extract failed

    Extract failed. One or more unsupported elements were found in the schema used as part of a data package.
    Error SQL71564: Foreign key Foreign Key: [Production].[FK_ProductProductPhoto_ProductPhoto_ProductPhotoID] cannot reference table Table: [Production].[ProductPhoto] when used as part of a data package.

    DACPAC file contains the data in BCP format

    Once the extract success – the DAC package file contains extra folders and BCP files as shown below:

    Select tables…
    Basically, not only tables but other objects like views, stored procedures, etc. This option is only available when “Extract schema and data” is chosen.
    All objects are grouped by schema:

    Visual Studio: Choose the tables to extract data

    Verify extraction
    The option validates objects in extracted DAC package and you could end up with the same errors like in SSMS which, as a result, would not generate the file for you. It’s not what we expect. Especially, when planning to use the extract for import to Visual Studio as a database project – I do recommend verifying all objects over there.
    Wednesday, August 12, 2020 10:00 AM
  • Hi,

    Is there any update on this case?
    Please feel free to drop us a note if there is any update.

    Best regards,

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 1:20 AM