asked
Invalid Column error is appearing when running the stored procedure on SSMS but not with SQLCMD
Hi Friends, I have a SP mentioned as below. I m using sql 2008 server R2. When I run this SP from SQLCMD its working and SP is created but when i execute the same from SSMS its failing saying below error. > "Msg 207, Level 16, State 1, Procedure > TestProc, Line 5 > Invalid column name 'col1'. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROC [dbo].[TestProc] AS BEGIN SELECT t.col1 FROM table t /* But note that col1 is not in table t*/ END RETURN Any Thoughts???
WilliamD is probably on the right track there. Deferred Name Resolution will allow you to reference nonexistent tables, but not nonexistent columns. So running this SQL in a database where `table` exist will give an error. Running it in a database where `table` doesn't exist is fine. You may have a different database context between your SSMS session and the SQLCMD session
Are you creating the stored procedure in the same DB in the sqlcmd as when creating it from SSMS? As you can reed from MSDN: [Deferred Name Resolution and Compilation][1]. When using deferred name resolution, then if the object doesn't exists then the procedure will be created, but when you do a select from a table, but only a field doesn't exists then execution will fail. Try to execute SELECT DB_NAME() in SQLCMD to ensure you are creating the proc in the same DB. [1]:
http://msdn.microsoft.com/en-us/library/ms190686.aspx
@kev and pavel- thank you for providing information which was very useful..to clarify your queries I am running the SP on the same DB...but the table is also from the same DB.
Interesting.. Tested the same scenario and fails in both cases when the table exists but column not. If the table doesn't exists, then it succeed in both scenarios. Of course.. using and appropriate table name or enclosing the table in brackets `[table] t`.
