Strange Issue with Linked server and using IF exists We are trying to insert data from remote server using below query

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
begin
Insert into [ServerB].[DB_B].dbo.Monthlydata
select  *,getdate() from  
[ServerA].[DB_A].dbo.monthlydata
end


Above query fails with below error


  The OLE DB provider "SQLNCLI11" for linked server "ServerA" does not contain the table ""DB_A"."dbo"."MonthlyReportData"". The table either does not exist or the current user does not have permissions on that table.


Table Doesn't exist,but whats strange is  query checking   `begin' block ,even after if exists fails

If we modify the query to below,it works with out any error and print 1 doesn't gets printed

  if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    print 1
    end


if we modify further the query to, query a table which exists it work,i mean it doesn't go further after begin block

 if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from  
    [ServerA].[DB_A].dbo.validtable
    end


In the above example,in begin block,observe exists works correctly and doesn't throw any error only when you have a valid table in begin and end block''

Hope i explained this clearly.

Below are few things checked

1.Issue Exists even with information_schema.tables
2.Tried this as well,but no help  :https://stackoverflow.com/questions/22182437/check-if-table-exists-in-external-linked-database
3.Account used to connect is Sysadmin

Below answer works when i use a single statement and use exec.I should have made myself clear,above query is generated dynamically and passed to exec..see below example

set @Sql='

Insert into [server_A].[db_A].dbo.Monthlydata
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(cast(@dbname as varchar(20)))+'.dbo.MonthlyReportData
end'


exec(@sql)


Please let me know if i am unclear or you need any further info

Below is my complete code

DEclare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open GetBMSdata
fetch next from Getdata into @dblocation,@dbname



while @@FETCH_STATUS=0
begin

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData)
begin
Insert into [servera].[dba].dbo.Monthlydata
select  * from ' +quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData
end'

exec(@sql)






fetch next from Getdata into @dblocation,@dbname
end


close Getdata
deallocate Getdata


I ended up modifying my code like below

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.sys.tables where name='MonthlyReportData'')
begin
exec(
''
Insert into [serverb].dba_b.dbo.MonthlyReportData
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.dbo.MonthlyReportData
''
)
end
'
print @sql
exec(@sql)
The whole query is validated by the engine before it runs, so the IF statement cannot prevent that message for a table that does not exist.

You could use dynamic sql inside the IF.

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
  exec('Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from  
    [ServerA].[DB_A].dbo.monthlydata')
end


After clarification and full sample

Declare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open Getdata
fetch next from Getdata into @dblocation,@dbname



while @@FETCH_STATUS=0
begin
if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
 set @Sql='

 Insert into [serverb].[db_a].dbo.data

 select  *,getdate() from ' 
 +quotename(@dblocation)+'.'+quotename(cast(@dbname as 
 varchar(20)))+'.dbo.MonthlyReportData
 end'

 exec(@sql)
end

fetch next from Getdata into @dblocation,@dbname
end


close Getdata
deallocate Getdata