Red Gate forums :: View topic - Problem altering inline table function with SCHEMABINDING
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Packager 6
SQL Packager 6 forum

Problem altering inline table function with SCHEMABINDING

 Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ddesc



Joined: 27 Sep 2012
Posts: 2
Location: Melbourne, Australia
PostPosted: Thu Sep 27, 2012 10:23 pm    Post subject: Problem altering inline table function with SCHEMABINDING Reply with quote
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:
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:
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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6344
Location: Red Gate Software
PostPosted: Tue Oct 02, 2012 10:05 am    Post subject: Reply with quote
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521
Back to top
View user's profile Send private message
ddesc



Joined: 27 Sep 2012
Posts: 2
Location: Melbourne, Australia
PostPosted: Thu Oct 04, 2012 10:57 pm    Post subject: Reply with quote
Hi Brian,

Thanks for this.

Cheers

Sam.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group