Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlPackage generates deploy script with invalid syntax when altering in-memory DB table if there is a schema-bound function dependent on it #308

Closed
IVNSTN opened this issue Jul 31, 2023 · 0 comments
Assignees
Labels
bug Something isn't working fixed-pending-release Fix in upcoming release
Milestone

Comments

@IVNSTN
Copy link

IVNSTN commented Jul 31, 2023

  • SqlPackage.exe version: 162.0.52
  • SqlServer version: Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64)
  • Target database compatibility level: 150

Steps to Reproduce:

  1. Create an in-memory sqlproj, add a table with a couple of columns
  2. Create function with options WITH NATIVE_COMPILATION, SCHEMABINDING which selects from this table
  3. Deploy this project
  4. Add a column to the table
  5. Try to deploy modified project and get invalid syntax error

SqlPackage tries to behave like this is not memory optimized project and to be able to alter the table SqlPackage temporarily removes SCHEMABINDING option from dependent function. But natively-compiled functions must have SCHEMABINDING option, otherwise syntax is invalid.

Example of generated deployment script part (obfuscated):

GO
PRINT N'Removing schema binding from [my].[fn]...';

GO
ALTER FUNCTION my.fn
(@bar INT NULL)
RETURNS TABLE 
WITH NATIVE_COMPILATION  ---<<< this is illegal, it MUST have SCHEMABINDING option
AS
RETURN 
   SELECT t.id
   FROM dbo.altered_table AS t
   WHERE t.foo = @bar
GO
PRINT N'Altering Table dbo.altered_table...';

GO
ALTER TABLE dbo.altered_table
   ADD new_col INT NULL;

GO
PRINT N'Adding schema binding to my.fn...';

GO
ALTER FUNCTION my.fn
(@bar INT NULL)
RETURNS TABLE 
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
RETURN 
   SELECT t.id
   FROM dbo.altered_table AS t
   WHERE t.foo = @bar
GO

So there are two things

  • to add a column you don't need to unbind dependent objects, you can just do ALTER TABLE ADD
  • removing SCHEMABINDING from NATIVE_COMPILATION objects is illegal, you need some other approach; like regenerating with fake body or something

Did this occur in prior versions? If not - which version(s) did it work in?
No version found where it works.

How we workaround this issue: we add column with ALTER TABLE ... ADD ... before deployment via sqlpackage.

ps
I don't really understand where I should report this issue. Here is a feedback on azure.com , here is a thread on stackoverflow, both are dead ends.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

@IVNSTN IVNSTN added the bug Something isn't working label Jul 31, 2023
@llali llali self-assigned this Oct 6, 2023
@llali llali added the fixed-pending-release Fix in upcoming release label Oct 6, 2023
@llali llali added this to the 162.2 milestone Oct 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working fixed-pending-release Fix in upcoming release
Projects
None yet
Development

No branches or pull requests

3 participants