1

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)
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.