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.


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

Description

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
	AS 
SELECT 
	id = try_convert(int, id)
 FROM 
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