Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  Aaron Bertrand Apr 2 '12 at 17:51
    
Alternative to using a linke server: Use PowerShell to loop through a server & database list calling invoke-sqlcmd. –  Robert L Davis Apr 2 '12 at 18:11
add comment

1 Answer

up vote 3 down vote accepted

Why not just:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DELETE [' + @DBName + '].dbo.tblname WHERE foo = ''bar'';';
EXEC linkedserver...sp_executesql @sql;
share|improve this answer
    
Because I didn't know that was possible and didn't think that EXEC could do that. THANKS! –  Justin Dearing Apr 2 '12 at 17:58
    
Or just do: DELETE T FROM Server.Database.Schema.Table AS T WHERE ...... –  Robert L Davis Apr 2 '12 at 18:05
    
But @RobertLDavis did you notice that @DBName was a parameter? Also what value does the alias T provide? –  Aaron Bertrand Apr 2 '12 at 18:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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