1

So I have a query I've written that I thought was working, but just found out today that it isn't. The goal is to sync a user database on one SQL server with another on a MYSQL server so our web server can query it quicker than if it were trying to retrieve results from the SQL Server. The query in question is supposed to delete any user off of the MYSQL server if they are not found in the SQL server. I can take all the individual pieces of this query (deleting from the linked server, selecting the proper records from each table, verifying the non-existence of certain records, etc.), run them by themselves, and they work fine; however as soon as I put them together in this query, somehow the records I expect to get deleted are not.

DELETE WebADUsers
FROM    OPENQUERY(ENET, 'SELECT * FROM ActiveDirectory.Users') AS WebADUsers
WHERE WebADUsers.EmployeeID NOT IN
(
    SELECT  u.[employeeID]
    FROM    [SGDB].[IT_ActiveDirectory].[dbo].[Users] u
)

So essentially a user with the ID '02681' exists on the MYSQL (ENET) server, but that EmployeeID does not exist in the SQL (SGDB) server. He is not getting deleted from the ENET server. I have tried accomplishing the same task by deleting myself out of the SGDB server, but the ENET record will persist after running this query with a result of "0 rows affected." I could have swore this query was working, but I guess not. Does anyone see what I'm doing wrong, or at least have a couple ideas for me to try? I'd really appreciate it.

Things I've tried:

Creating a subset of the OPENQUERY() and using that instead. It had no effect unsurprisingly. Example -

DELETE WebADUsers
FROM    (SELECT * FROM OPENQUERY(ENET, 'SELECT * FROM ActiveDirectory.Users')) AS WebADUsers
WHERE WebADUsers.EmployeeID NOT IN
(
    SELECT  u.[employeeID]
    FROM    [SGDB].[IT_ActiveDirectory].[dbo].[Users] u
)

In this example I instead try to put everything into a temp table and then see if I could delete from the temp table with the same WHERE conditions. It successfully imports all the correct data, and the same user record we've been discussing is not in the SGDB server's user database, so it should work correctly and delete the user from the #tempad table... It doesn't though. Deletes 0 rows. Example 2 -

IF OBJECT_ID('tempdb..#tempad') IS NOT NULL
    DROP TABLE #tempad
SELECT      *
INTO        #tempad
FROM        OPENQUERY(ENET, 'SELECT * FROM ActiveDirectory.Users')

DELETE w
FROM    #tempad w--OPENQUERY(ENET, 'SELECT * FROM ActiveDirectory.Users') AS WebADUsers
WHERE w.EmployeeID NOT IN
(
    SELECT  u.[employeeID]
    FROM    [SGDB].[IT_ActiveDirectory].[dbo].[Users] u
)
TheEggSample
  • 333
  • 4
  • 5
  • 18
  • The query is correct. Maybe you have a transaction that is not being commited? Did you try doing the delete over a linked server? – EzLo Mar 20 '18 at 15:56
  • Yes. Breaking the query down into all its individual parts works (Deleting from the linked server, selecting the right employeeIDs, etc.). Its just when I put it all together that it doesn't work as intended. – TheEggSample Mar 20 '18 at 16:00
  • Another way to (re)write this is `WITH WebADUsers AS (SELECT * FROM OPENQUERY ... WHERE .. NOT IN ...) DELETE WebADUsers`, although I don't expect that to yield a different plan. (But when you're throwing in `OPENQUERY` and another linked server, who knows -- I prefer chopping such things up explicitly with temp tables inbetween so I know exactly what data flows where.) – Jeroen Mostert Mar 20 '18 at 16:12
  • Curious: if you replace the **DELETE WebADUsers** with SELECT *, does the query return any results. Just wondering if it's the DELETE operation it doesn't like, or is it the WHERE clause integration with the OPENQUERY. – Nik Shenoy Mar 20 '18 at 16:18
  • @NikShenoy Yeah, when I SELECT * from the Openquery, I get no results. The only time I can get results is when I specify the the employeeID I'm looking for in the WHERE clause, despite the record meeting the conditions of being in ENET server but not in SGDB server. – TheEggSample Mar 20 '18 at 16:23
  • @JeroenMostert I tried what you recommend with the temp tables. See example 2 in my original post. It somehow won't delete from the temp table. This at least narrows it down so we know its not an OpenQuery issue. – TheEggSample Mar 20 '18 at 19:34
  • Try changing the `NOT IN` for a `NOT EXISTS`, also make sure there isn't an implicit conversion between both employeeID's data types from each table. There might be an `employeeID` that is `NULL` on your SGDB table (which will force a `NOT IN` to yield 0 results, always!) – EzLo Mar 20 '18 at 19:54
  • @EzequielLópezPetrucci It doesn't like NOT EXISTS. Says it should be NOT IN, NOT LIKE or NOT BETWEEN. But I think you were right about the NULL value forcing they 0 results. I'm not sure how these values got entered in as NULL, but there are some. I'm working on fixing it now and will report back. Thanks – TheEggSample Mar 20 '18 at 20:34
  • Fixed everyone! The issue was in fact that I had some unintentional NULL values being selected by the NOT IN. I actually didn't know that was a functionality of NOT IN in the first place, so thank you for pointing that out @EzequielLópezPetrucci. Appreciate all the help everyone! – TheEggSample Mar 20 '18 at 23:09

0 Answers0