Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 772804
Opened: 11/29/2012 1:39:46 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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
Details
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
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
try_convert error in data tools.PNG 11/29/2012 4 KB
try_convert error in data tools.PNG 11/29/2012 4 KB