I need to delete rows in a link server's table, 1000 rows at a time.
My code is the following:
WHILE (SELECT COUNT(*) FROM OPENQUERY (SqlServerAcc, 'SELECT * FROM titles ') > 1000)
BEGIN
DELETE OPENQUERY (SqlServerAcc,
'SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM tbl) t WHERE RowNumber <= 1000');
END
But I do have errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '>'.
at the expression > 1000
.
I don't understand why this does not work.
Bonus: how can we make the name of the server not hard-coded; in PRD, it should be SqlServerPrd, but it seems that OPENQUERY does not accept variables for its arguments...