TRY_CONVERT broken by with schemabinding in SQL Server 2012 Data Tools build - by William Assaf

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 772804 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 11/29/2012 1:39:46 PM
Access Restriction Public


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 a nonsensical error. 
CREATE VIEW [dbo].[aview] with schemabinding
	id = try_convert(int, id)
from [dbo].atable d

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	4	19	

It thinks the data type is an object.  See attached screenshot, where "try_convert(int" is underlined in red.

Note that the project target platform is SQL 2012 and the project's database settings compatibility mode is SQL 2012.

Version of Data Tools I'm using:

In SQL Server 2012 Data Tools, Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.50709 SP1Rel
Installed Version: SSDS Standard
SQL Server Data Tools   10.3.21101.1
Visual Studio 2010 Shell (Integrated) - ENU Service Pack 1 (KB983509)   

Also, the SQL Server Data Tools are up to date.
SQL Server Data Tools 10.3.21101.1
SQL Server Data Tools - Build Utilities 10.3.21101.1
Microsoft SQL Server 2012 Data-Tier App Framework 11.1.2820.0
Sign in to post a comment.
Posted by Microsoft on 3/18/2013 at 8:24 AM
Hey William,

Wanted to let you know that the team has taken a fix for this issue. The fix will appear in an upcoming release of DACFx/SSDT.

Thanks for your feedback in reporting this issue and let us know if you have any additional questions/feedback.
Adam Mahood
Program Manager
SQL Server Database Systems
Posted by Zefir on 2/14/2013 at 7:30 AM
I have the same problem with geography:

geography::STPointFromText('POINT(' + CONVERT(nvarchar(15), dbo.Audit.Longitude) + ' ' + CONVERT(nvarchar(15),
                         dbo.Audit.Latitude) + ' ' + CONVERT(nvarchar(10), dbo.Audit.Altitude) + ')', 4326) AS Location

In managemnet studio everything is working and view is created without single issue, however in VS 2012 I have error:
SQL70561: Cannot schema bind view '[dbo].[vAudit]' because name 'geography' is invalid for schema binding