locked
Reference to non existent column inside an IF statement RRS feed

  • Question

  • Hi all,

    I have a script that tests for a columns existence and if exists uses it i.e. refers to it. When I run the script on my machine it runs fine, on another it returns an invalid column error. What server setting would do this?

    Note, my script must run on SQL versions 2000, 2005, 2008, 2008R2

    Msg 207, Level 16, State 1, Line 14

    The code is:

    DECLARE @Cols Int
    
    SELECT @Cols = COUNT(*)
        FROM syscolumns
        WHERE name = 'R040_ORDER_EXPORT_DRIVER'
        AND id = OBJECT_ID(N'[dbo].F040_USER')
    
    
    IF @Cols > 0 
    BEGIN
          CREATE TABLE SystemSalesOrderSetting(DriverExportOrder Char(8))
    
          INSERT INTO SystemSalesOrderSetting(DriverExportOrder)
          SELECT R040_ORDER_EXPORT_DRIVER
          FROM F040_USER
    END
    GO
    
    
    
    
    

    Thanks for any assistance.

    Clay

     

    Invalid column name 'R040_ORDER_EXPORT_DRIVER'

    Wednesday, November 30, 2011 6:22 AM

Answers

  • SQL Server has a feature known as deferred name resolution - well, I call it a misfeature. This means that if you create a stored procedure or submit a script that refers to a non-existing table, you don't not get an error at compile time. Only if the table is missing when the statement is executed, you get an error.

    However, this does not extend do columns. So if you have a script or a stored procedure that refers to a non-existing column in an existing table, then you get a compile-time error, and execution never commences.

    Now, to make things even more complicated, if you have:

    INSERT INTO SystemSalesOrderSetting(DriverExportOrder)
                SELECT R040_ORDER_EXPORT_DRIVER
                FROM F040_USER

    And SystemSalesOrderSetting does not exist, SQL Server abandons the compilation of the entire statement, and will thus not report the missing column. And to make it even more confusing, I believe this case works a little differently on different SQL Server versions, so some may report an error and other versions may not.

    What is the purpose of this script? Is it part of an upgrade script to take the database schema from one version to another? In such scripts, you typically have to use a lot of dynamic SQL to resolve the scoping issues:

    IF EXISTS (SELECT *
                         FROM     syscolumns
                         WHERE    name = name = 'R040_ORDER_EXPORT_DRIVER'
                             AND    id = OBJECT_ID(N'[dbo].F040_USER'))
    BEGIN
         EXEC('CREATE TABLE SystemSalesOrderSetting(DriverExportOrder Char(8))
    
                    INSERT INTO SystemSalesOrderSetting(DriverExportOrder)
                    SELECT R040_ORDER_EXPORT_DRIVER
                    FROM F040_USER')
    END

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by thierry schmit Wednesday, November 30, 2011 11:03 AM
    • Marked as answer by clay123123123 Wednesday, November 30, 2011 10:00 PM
    Wednesday, November 30, 2011 9:00 AM

All replies

  • Perhaps permission issue:

    GRANT SELECT ON sys.syscolumns TO user

    Shatrughna.
    Wednesday, November 30, 2011 6:27 AM
  • Do you have table F040_user on another machine?
    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, November 30, 2011 6:29 AM
  • You can use 

    SELECT COLUMNPROPERTY( OBJECT_ID('Person.Person'),'LastName',

    'PRECISION')AS 'Column Length';
    BTW, despite syscolumns system table  is available on all editions you are described I would not rely on this because thing could be change even after applying SP

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 30, 2011 6:55 AM
    Answerer
  • You can use 

    SELECT COLUMNPROPERTY( OBJECT_ID('Person.Person'),'LastName',

    'PRECISION')AS 'Column Length';
    BTW, despite syscolumns system table  is available on all editions you are described I would not rely on this because thing could be change even after applying SP

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 30, 2011 6:55 AM
    Answerer
  • SQL Server has a feature known as deferred name resolution - well, I call it a misfeature. This means that if you create a stored procedure or submit a script that refers to a non-existing table, you don't not get an error at compile time. Only if the table is missing when the statement is executed, you get an error.

    However, this does not extend do columns. So if you have a script or a stored procedure that refers to a non-existing column in an existing table, then you get a compile-time error, and execution never commences.

    Now, to make things even more complicated, if you have:

    INSERT INTO SystemSalesOrderSetting(DriverExportOrder)
                SELECT R040_ORDER_EXPORT_DRIVER
                FROM F040_USER

    And SystemSalesOrderSetting does not exist, SQL Server abandons the compilation of the entire statement, and will thus not report the missing column. And to make it even more confusing, I believe this case works a little differently on different SQL Server versions, so some may report an error and other versions may not.

    What is the purpose of this script? Is it part of an upgrade script to take the database schema from one version to another? In such scripts, you typically have to use a lot of dynamic SQL to resolve the scoping issues:

    IF EXISTS (SELECT *
                         FROM     syscolumns
                         WHERE    name = name = 'R040_ORDER_EXPORT_DRIVER'
                             AND    id = OBJECT_ID(N'[dbo].F040_USER'))
    BEGIN
         EXEC('CREATE TABLE SystemSalesOrderSetting(DriverExportOrder Char(8))
    
                    INSERT INTO SystemSalesOrderSetting(DriverExportOrder)
                    SELECT R040_ORDER_EXPORT_DRIVER
                    FROM F040_USER')
    END

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by thierry schmit Wednesday, November 30, 2011 11:03 AM
    • Marked as answer by clay123123123 Wednesday, November 30, 2011 10:00 PM
    Wednesday, November 30, 2011 9:00 AM
  • Thanks to everyone for your input.

    Yes Erland, it is an upgrade script & the dynamic sql is working as expected.

    Cheers.

     

     

     

    Wednesday, November 30, 2011 10:00 PM