Strange Issue with Linked server and using IF exists

Question

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)

Answer 1

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