2

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?

2
  • I don't understand SELECT TOP 0 * or why you want OPENROWSET for this. Much simpler to just use the linked server directly, instead of trying to turn a non-query into a query, no? Apr 2, 2012 at 17:51
  • Alternative to using a linke server: Use PowerShell to loop through a server & database list calling invoke-sqlcmd. Apr 2, 2012 at 18:11

1 Answer 1

5

Why not just:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DELETE [' + @DBName + '].dbo.tblname WHERE foo = ''bar'';';
EXEC linkedserver...sp_executesql @sql;
3
  • Because I didn't know that was possible and didn't think that EXEC could do that. THANKS! Apr 2, 2012 at 17:58
  • Or just do: DELETE T FROM Server.Database.Schema.Table AS T WHERE ...... Apr 2, 2012 at 18:05
  • But @RobertLDavis did you notice that @DBName was a parameter? Also what value does the alias T provide? Apr 2, 2012 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.