The "Invalid object name ..." error appears in one server but no in the other one

Question

I have a wired situation. The code below works perfectly well on one server, but not in the other one. Databases compatibility level is the same on both of the servers. I use sql server 2016.

;USE MyDB;
GO
--exec MyDB.dbo.sp_Cleanup_Bid5YearData
ALTER PROCEDURE dbo.sp_Cleanup_Bid5YearData

AS 

DECLARE @date VARCHAR(10), 
        @cmdIf NVARCHAR(200),
        @cmd NVARCHAR(4000)

SET @date = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '_' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '_' + CAST(DAY(GETDATE()) AS VARCHAR(2)) 

IF OBJECT_ID('tempdb..#Id_ToBeRemoved') IS NOT NULL --SELECT 1

   DROP TABLE #Id_ToBeRemoved;

CREATE TABLE #Id_ToBeRemoved
    (
        Id INT PRIMARY KEY NOT NULL, 
        Code CHAR(3) NOT NULL 
    );

INSERT INTO #Id_ToBeRemoved (Id, Code)
VALUES(12,'TMB'),
      (13,'FGR');

IF EXISTS (SELECT TOP 1 1 FROM #Id_ToBeRemoved) 

SET @cmd = ('

;USE MyDB;

IF EXISTS (SELECT 1 FROM [MyDB].dbo.Table_5YearData_'+@date+') 

    DROP TABLE [MyDB].dbo.Table_5YearData_'+@date+';

;USE MyDB;

SELECT 
    Id, 
    Code
INTO [MyDB].dbo.Table_5YearData_'+@date+'
FROM  #Id_ToBeRemoved; 
         ')

EXEC sp_executesql @cmd;
--EXEC (@cmd);
--select * from  [MyDB].dbo.Table_5YearData_2018_4_25 -- you may need to change the ending of the table "2018_4_25" depending on the date you run this statement 

The error message that I'm getting is:

Msg 208, Level 16, State 1, Line 7
Invalid object name 'bidNotification.dbo.Table_5YearData_2018_4_25'

Answer 1

The table bidNotification.dbo.Table_5YearData_2018_4_25 does not exist already in your 2nd database. Your query checks to see if there are rows in the table NOT if the table actually exists. You query works only if that table already exists.

You could modify and do this instead using OBJECT_ID() to verify the table is there.

SET @cmd = ('

;USE MyDB;

IF OBJECT_ID(N''[MyDB].dbo.Table_5YearData' + @date + ''', N''U'') IS NOT NULL

    DROP TABLE [MyDB].dbo.Table_5YearData_'+@date+';

;USE MyDB;

SELECT 
    Id, 
    Code
INTO [MyDB].dbo.Table_5YearData_'+@date+'
FROM  #Id_ToBeRemoved; 
         ')