Thursday, March 21, 2013

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:
Error 3 SQL70561: Cannot schema bind view '[dbo].[aview]' because name 'int' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. C:\Users\...\Documents\Visual Studio 2010\Projects\...\Views\dbo.aview.sql
This view is perfectly valid in SQL Server, and can be created without a problem. In a Data Tools database solution, it prevents the solution from being built with the above error.


In the above screenshot, "try_convert(int" is underlined in red because SSDT isn't recognizing the syntax correctly.  Again, this only appears when the view is created with schemabinding.

Note that in my bug report, the project's target platform is SQL 2012 and the project's database settings compatibility mode is SQL 2012, using Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1 and SQL Server Data Tools 10.3.21101.1.

This has been marked as "resolved" on Microsoft Connect, and the fix "will appear in an upcoming release of DACFx/SSDT", as of March 2013: https://connect.microsoft.com/SQLServer/feedback/details/772804/try-convert-broken-by-with-schemabinding-in-sql-server-2012-data-tools-build#details

UPDATE: Confirmed that this is now resolved in recent releases of SSDT.

No comments: