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)
Best Answer
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.
After clarification and full sample