SP querying another database - No error when column name is incorrect

  • I have a SP which queries a table in another database. There is an abnormal behavior when creating the SP.

    1. When I get the column name of a local table wrong

    AND

    if table in the other database is NOT present - there is no error about the incorrect column name.

    2. When I get the column name of a local table wrong

    AND

    if table in the other database IS present - there is an error about the incorrect column name.

    This is not a critical issue as such, but I'm intrigued to know why this happens. Any input please.

    SELECT @@VERSION

    /*Microsoft SQL Server 2012 - 11.0.2218.0 (X64)

    Jun 12 2012 13:05:25

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)*/

    CREATE DATABASE Third_Party ON PRIMARY

    (

    NAME = N'Third_Party', FILENAME=N'C:\MSSQL_DATA\DATA\Third_Party.mdf',

    SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB

    )

    LOG ON

    (

    NAME = N'Third_Party_Log', FILENAME=N'C:\MSSQL_DATA\DATA\Third_Party.ldf',

    SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB

    )

    GO

    CREATE DATABASE Dealer ON PRIMARY

    (

    NAME = N'Dealer', FILENAME=N'C:\MSSQL_DATA\DATA\Dealer.mdf',

    SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB

    )

    LOG ON

    (

    NAME = N'Dealer_Log', FILENAME=N'C:\MSSQL_DATA\DATA\Dealer.ldf',

    SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB

    )

    GO

    ----------------------------------------------------

    USE Third_Party

    GO

    IF OBJECT_ID('dbo.Location_Dummy') IS NOT NULL DROP TABLE dbo.Location_Dummy

    CREATE TABLE dbo.Location_Dummy

    (

    Location_ID INT,

    Location NVARCHAR(50)

    )

    go

    USE Dealer

    GO

    IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.Store

    CREATE TABLE dbo.Store

    (

    Store_Name NVARCHAR(100),

    Store_Location NVARCHAR(50)

    )

    GO

    USE Dealer

    GO

    --This Create SP script does not throw an error, even though Store.Store_Code is not present

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_Present

    GO

    CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present

    AS

    BEGIN

    --Column Store_Code is not present

    SELECT s.Store_Name, s.Store_Location, s.Store_Code

    FROM dbo.Store s

    WHERE s.Store_Location IN

    (

    --Table Third_Party.dbo.Location is not present

    SELECT l.Location FROM Third_Party.dbo.Location l

    )

    END

    GO

    --This Create SP script throws an error, as expected

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Present

    GO

    CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Present

    AS

    BEGIN

    --Column Store_Code is not present

    SELECT s.Store_Name, s.Store_Location, s.Store_Code

    FROM dbo.Store s

    WHERE s.Store_Location IN

    (

    --Table Third_Party.dbo.Location_Dummy is present

    SELECT ld.Location FROM Third_Party.dbo.Location_Dummy ld

    )

    END

    GO

    https://sqlroadie.wordpress.com/

  • If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table. That is, it waits until run time, then checks to see if the table exists and checks the column names. This is intentional, to allow you to create code before the object exists.

    But, if the table does exist, SQL can verify all the column names from the start, and thus won't allow an incorrect name.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher (11/16/2015)


    If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table.

    <snip/>

    Thanks for the reply Scott. If a table does not exist, is name resolution for all tables of that query deferred? It looks so.

    In my example, table Store is present and table in the other DB is not present. I'm selecting a non-existent column in an existing table (--no error)

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_Present

    GO

    CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present

    AS

    BEGIN

    --Column Store_Code is not present

    SELECT s.Store_Name, s.Store_Location, s.Store_Code

    FROM dbo.Store s

    WHERE s.Store_Location IN

    (

    --Table Third_Party.dbo.Location is not present

    SELECT l.Location FROM Third_Party.dbo.Location l

    )

    END

    GO

    https://sqlroadie.wordpress.com/

  • Arjun Sivadasan (11/16/2015)


    ScottPletcher (11/16/2015)


    If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table.

    <snip/>

    Thanks for the reply Scott. If a table does not exist, is name resolution for all tables of that query deferred? It looks so.

    In my example, table Store is present and table in the other DB is not present. I'm selecting a non-existent column in an existing table (--no error)

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_Present

    GO

    CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present

    AS

    BEGIN

    --Column Store_Code is not present

    SELECT s.Store_Name, s.Store_Location, s.Store_Code

    FROM dbo.Store s

    WHERE s.Store_Location IN

    (

    --Table Third_Party.dbo.Location is not present

    SELECT l.Location FROM Third_Party.dbo.Location l

    )

    END

    GO

    Hmm, interesting. I guess the entire statement is deferred if a table within it has to be deferred. That makes sense when you think about it: SQL can't validate data types for comparisons between tables if one of the table's schema's isn't even known!

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Yup, I agree. Thanks for your time.

    https://sqlroadie.wordpress.com/

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply