Search

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

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)
1
User(s) can reproduce this bug
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 (expand)

Product Language

English

Version

SQL Server 2012 - Enterprise Edition

Category

Developer Tools (SSDT, BIDS, etc.)

Operating System

Windows 7 Ultimate (SP1)

Operating System Language

US English

Steps to Reproduce

In a SSDT database solution,

CREATE VIEW [dbo].[aview] with schemabinding
    AS
SELECT
    id = try_convert(int, id)
FROM
    dbo.arealtable

Actual Results

Database project builds with 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    4    19    

Expected Results

No build error.

Platform

X64

Virtualization

 
File Attachments
File Name Submitted By Submitted On File Size  
try_convert error in data tools.PNG William Assaf 11/29/2012 4 KB
try_convert error in data tools.PNG William Assaf 11/29/2012 4 KB
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.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement