I am trying to create SQL Server Database Project (SQL 2012, VS 2013 .net v4.6) for our existing database. I created the project, imported the schema, and now ran into this issue. I am getting the error below:
Error SQL71501: Procedure: [dbo].[StoredProc_Name] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[TABLE1].[TABLE_ALIAS]::[COL1], [dbo].[TABLE1].[COL1] or [dbo].[TABLE1].[TABLE_ALIAS]::[COL1]. D:\SPNAME.sql
I know a fix, but that doesn't make sense to me. Instead of using an alias, if I use two part naming convention then the error is resolved.
--errors when referenced using alias
SELECT T.*
FROM [dbo].[TABLE1] AS T (NOLOCK)
WHERE T.COL1 = @COL1
--no errors when using 2 part naming convention
SELECT T.*
FROM [dbo].[TABLE1] AS T (NOLOCK)
WHERE [dbo].[TABLE1].COL1 = @COL1
I would love understand why is this happening? I very much doubt the issue is with using alias, is it? So to get this thing building do I need to replace the use of alias in all of our sps with two part naming convention? That's too difficult to be a solution... I did come across questions talking about similar issue and the resolution offered was to change to 2 part naming convention, which is quite not scalable in my case. Any alternative ideas?
As other members suggested, that is a valid syntax. I just wanted to share why it was erroring out for me. The reason I was getting an error was because of the stale stored proc. When I imported the schema, I got these stored proc, but the column name had been changed in the current schema and these stored proc were not updated. Since my primary goal was to get this project building for now, I commented the 'where' clause and moved on. Thanks for all the help.