0

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

ErickTreetops
  • 3,189
  • 4
  • 27
  • 37
  • Create a stored proc with variable @databasename , exec sp and pass variables dynamically from parameter sniffing – Ven Oct 12 '18 at 12:23

1 Answers1

0

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.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • Thank you for that . I would rather NOT need to create a stored procedure on the master database if i can avoid it. I will try and see why my DML isn't working. – ErickTreetops Oct 17 '18 at 22:46
  • @user1413844 thanks, I believe you'll have to click on the check mark next to my answer to mark it as accepted. – userfl89 Oct 18 '18 at 13:30