Sql-server – How to delete rows from a table on a linked server

linked-serversql servert-sql

I need to delete rows from a table in several databases on several servers with the same schema. OPENROWSET() does not seem to be the answer.

If I do this:

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

I get the error 

Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DELETE FROM  . . .

If I add SELECT 1; to the query so a resultset gets returned like i do below, I get one row one column result (the value of which is BTW not '1'). However, there delete operation does not occur.

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''SELECT 1; DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

What do I need to make the delete happen on the linked server?

Best Answer

Why not just:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DELETE [' + @DBName + '].dbo.tblname WHERE foo = ''bar'';';
EXEC linkedserver...sp_executesql @sql;
Related Question