Problem altering inline table function with SCHEMABINDING

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Problem altering inline table function with SCHEMABINDING

Postby ddesc » Thu Sep 27, 2012 10:23 pm

Hi,

I use SQL Packager 6.4.0.8, and SQL Server 2008 R2.

I'm having a few issues creating an update package for a database we deploy. It seems that there are some issues with the syntax of the ATLTER FUNCTION statement in the generated script file under certain circumstances.

When upgrading the table function the first thing your script does is remove SCHEMABINDING from the existing function definition. It uses the following statement to do this.

Code: Select all
PRINT N'Removing schema binding from [dbo].[ftrdsh_getParameterRecord]'
GO

ALTER FUNCTION [dbo].[myTableFunction](@pParameter NVARCHAR(MAX))
RETURNS TABLE
WITH
RETURN (
    -- Implementation of function
); -- myTableFunction
GO


Unfortunately this statement is incorrect. It uses WITH instead of AS which is incorrect syntax. Why this is happening had me scratching my head for a little while but it seems this is caused by the way the function was originally defined.

When the function was first written it was defined like this.

Code: Select all
CREATE FUNCTION [dbo].[myTableFunction](@pParameter NVARCHAR(MAX))
RETURNS TABLE
WITH SCHEMABINDING
RETURN (
    -- Implementation of function
);
GO


You will notice the AS has been omitted from the function definition. It seems that SQL Server regards the AS as optional if you use WITH SCHEMABINDING in the definition. Therefore the syntax of the statement above is accepted by the database.

If I change the original definition of the table function to include the AS after the WITH SCHEMABINDING then SQL Packager generates the scripts for the upgrade without syntax errors.

I suspect the omission of AS in the original function definition may not be best practise, but as SQL Server accepts its omission it is therefore valid so I feel your product should support it.

Hope this makes sense,

Cheers

Sam.
ddesc
 
Posts: 2
Joined: Thu Sep 27, 2012 4:54 am
Location: Melbourne, Australia

Postby Brian Donahue » Tue Oct 02, 2012 10:05 am

Hi Sam,

Thanks for pointing that out. SQL Packager, when creating an upgrade package, leaves the WITH behind and doesn't add an AS clause, and that seems to cause the problem. The bug reference number is SPA-594.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby ddesc » Thu Oct 04, 2012 10:57 pm

Hi Brian,

Thanks for this.

Cheers

Sam.
ddesc
 
Posts: 2
Joined: Thu Sep 27, 2012 4:54 am
Location: Melbourne, Australia


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 1 guest