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
bibi92Asked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
'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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.