0

Based on this answer I am trying to make a DELETE from a JOIN. The query works if both of the tables are on the same server, but now I have two server:

  • TableA is on DatabaseA that is on ServerA SQL Server 9.0.4060
  • TableB is on DatabaseB that is on ServerB SQL Server 12.0.2000

ServerA is a linked server on ServerB.

I have the following TableA (on ServerA)

+---------+-----------+------+
|  Name   |  Surname  | Side |
+---------+-----------+------+
| Anakyn  | Skywalker | Bad  |
| Luke    | Skywaler  | Good |
| Obi Wan | Kenobi    | Good |
| Anakyn  | Skywalker | Good |
| Qui gon | Jinn      | Good |
| Darth   | Maul      | Bad  |
+---------+-----------+------+

and the following TableB (on ServerB)

+----------+------+
| Surname  | Side |
+----------+------+
| Skywaler | Good |
| Maul     | Bad  |
+----------+------+

If I execute this query

DELETE A
FROM [ServerA].[DatabaseA].[dbo].[TableA] A
INNER JOIN [dbo].[TableB] B
ON A.[Surname]=B.[Surname] COLLATE Latin1_General_CI_AS
AND A.[Side]=B.[Side] COLLATE Latin1_General_CI_AS

the system gives me an error

OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 7202, Level 11, State 2, Line 16 Could not find server 'ServerB' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Additional consideration:

  • The query select * from sys.servers where is_linked = 1 executed on ServerB.DatabaseB show me that ServerA is a linked server;
  • If replace DELETE A with SELECT A.* the query show me the correct results that I would like to delete;
  • I have checked the INFORMATION_SCHEMA.COLUMNS and every columns of the join have SQL_Latin1_General_CP1_CI_AS collation;
  • From [ServerB] I can delete rows of [ServerA].[DatabaseA].[TableA] if I use a DELETE without the JOIN.

Anybody have any help for me?

Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • Does this work `;with cte as (select A .* FROM [ServerA].[DatabaseA].[dbo].[TableA] A INNER JOIN [dbo].[TableB] B ON A.[Surname]=B.[Surname] COLLATE Latin1_General_CI_AS AND A.[Surname]=B.[Surname] COLLATE Latin1_General_CI_AS)select * from cte ` – TheGameiswar Mar 27 '18 at 15:29
  • I think you have simplified to much it is hard to tell what is going on. The error message says ServerB but there is in ServerB in your code. And not sure why you are checking Surname twice. :) – Sean Lange Mar 27 '18 at 15:30
  • @TheGameiswar Yes, the query works (and shows me the rows I want to delete) – Nicolaesse Mar 27 '18 at 15:38
  • @SeanLange You are right, I corrected the query witk the JOIN on [Surname] and [Side]. I've tryed to simplifier as much as I can to make it easily to understand. I could give a more complicated example if needed. – Nicolaesse Mar 27 '18 at 15:40
  • @paparazzo I have tryed and it works. – Nicolaesse Mar 27 '18 at 15:49
  • No need to make it more complicated. :) Since you say you have to run this from ServerB maybe you can use IN instead of a join? – Sean Lange Mar 27 '18 at 16:08

0 Answers0