Loop through DB list setting DB to single user Mode

Question

I'm trying to create a dynamic SQL statement to set a database into Single-user mode so that later I can do a restore onto it. I've placed an execute SQL task inside a loop where a parameter is used to provide the name of the next database to set into single user mode. However, I'm not sure if Execute SQL can run DML or just SQL and I'm not sure if it can all be done dynamically in a loop. Here is my code in side the SQL Execute task.

DECALRE @SQLString nvarchar(200)

SET @SQLString = 'ALTER DATABASE [' + ? + '] ' + 'SET SINGLE USER WITH ROLLBACK IMMEDIATE'

EXECUTE( @SQLString )

The question mark is the place holder for OLEDB for the name of the database I am setting to single user mode.

This is on SQL2012

Answer 1

Yes an Execute SQL Task will allow you to execute ALTER DATABASE commands as well as DML, DDL, and other such statements. It sounds like you want to use a Foreach Loop for this, and that will work fine using the syntax from your question for an OLE DB connection. As @Ven mentioned in the comment, doing this via a stored procedure with the database name as a variable would be the preferable method.