[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Error on sql statement

Hello,

When I try :

declare @sql2 varchar(max)
set @sql2 = 'IF exists (select *  from  ['+@SrvAbonne + '].['+ @DbCible + '].dbo.sysobjects where name = ''MSreplication_subscriptions'')
BEGIN
delete from ['+@SrvAbonne + '].['+ @Dbcible + '].dbo.MSreplication_subscriptions
where [publication] like ''' + @publication + ''' end'
execute (@sql2)
print(@sql2)

I have this error:
The OLE DB provider "SQLNCLI" for linked server "SRVTEST" does not contain the table ""TEST"."dbo"."MSreplication_subscriptions"". The table either does not exist or the current user does not have permissions on that table. #7314

I don't understand why because I check 'IF exists (select *  from  ['+@SrvAbonne + '].['+ @DbCible + '].dbo.sysobjects where name = ''MSreplication_subscriptions'')

Thanks

Regards

bibi
0
bibi92
Asked:
bibi92
  • 2
1 Solution
 
Nico BontenbalCommented:
'Exists' doesn't check if the table exists. It checks if the sql returns more than 0 rows.
Try to run:
select * from TEST.dbo.MSreplication_subscriptions
to see if that returns any records.
Also I think there is something with dynamic SQL and permissions. You might want to google on that.
I think it was that dynamic SQL always runs with the permissions of the current user, or something.
0
 
bibi92Author Commented:
I want to execute but I have another error :
'IF (select *  from  ['+@SrvAbonne + '].['+ @DbCible + '].dbo.sysobjects where name = ''MSsubscription_agents'')
then
delete from ['+@SrvAbonne + '].['+ @Dbcible + '].dbo.MSreplication_subscriptions
where [publication] like ''' + @publication + ''' end if' :

An expression of non-boolean type specified in a context where a condition is expected, near 'then'. #4145

Thanks
Regards
bibi
0
 
bibi92Author Commented:
Thanks bibi
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now