Reading from Linked Server and deleting on local DB

Question

Hi,

I have 2 similar tables on different databases. I already linked the remote DB and set it's data access to 'True'.

However I can't get the following query to work

GO
USE LOCALDB
GO
DELETE from [dbo].[TableA]
 WHERE [dbo].[TableA].[UniqueField] = (SELECT [UniqueField] FROM [REMOTESERVER].[REMOTEDB].[dbo].[TableA])
GO

This query doesn't give me any error but then again it doesn't delete the entry where there is a match on the UniqueField. I'm quite blocked at the moment and this is the only query I've thought of that doesn't prompt an error.

EDIT: The query says 0 rows affected.

Thanks in advance,

EtonB.

Answer 1

Thoughts...

I would consider loading a table variable locally and then deleting using that. This will stop any chance of a distributed txn starting which can happen for local write actions using data from a remote server.

Is UniqueField varchar and "collation compatible"? Or not and needs COLLATE. That is, perhaps the query is working as expected but the match is down to CASE.

What does this return?

SELECT [UniqueField] FROM [REMOTESERVER].[REMOTEDB].[dbo].[TableA]

Does it work for some values but not all? Exact datatype match? etc...