SQLTeam.com | Weblogs | Forums

Delete Where Not In Table B

Hi experts,

When I run this as a Select, it returns the correct rows.

But when I run as below, it deletes some rows where the database name exists in sys.databses.
Note DBbackups is on Server A local and sys.databases is on an external server accessed via a linked server. I have tried LEFT OUTER JOIN as well but get the same results.

DELETE DBBackups FROM DBBackups

LEFT JOIN MyServer.master.sys.databases as B ON DBBackups.DBName = B.name

WHERE B.name IS NULL;

Does the id being used on the MyServer instance have access to all db names on that instance? If that id doesn't have permission to view certain dbs, they won't be returned, even if the db name actually does exist.

2 Likes

Thanks ScottPletcher. I had a design error, corrected now.