CREATE DATABASE statement not allowed within multi-statement transaction. Dynamic database creation using stored procedure + EF

Question

I am trying to create a database using a stored procedure as shown here:

CREATE PROCEDURE [dbo].[usp_CreateDatabase]
    @dbName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @createDbQuery nvarchar(max) = 'CREATE DATABASE '+ Quotename(@dbName);
    EXEC (@createDbQuery)
END

This stored procedure will be in the Master database.

When I execute this in Management Studio, it works as expected and the database is created.

C# code:

using (Data.MasterDB db = new Data.MasterDB())
{
     db.usp_CreateDatabase(databaseName);
}

dbContext has the connection string of the Master database.

But when I try to execute the same from my application using Entity Framework, it throws

CREATE DATABASE statement not allowed within multi-statement transaction.
Database 'TestDb' does not exist. Make sure that the name is entered correctly

Answer 1

Is your application creating a Transaction before executing the procedure? If so, that would cause this problem since the Procedure is also creating a transaction.

You will see this same behavior if you were to execute the procedure in management studio (or any IDE) with "Auto-Commit" turned off, or if you wrapped it in a transaction like so:

BEGIN TRANSACTION
    EXECUTE dbo.usp_CreateDatabase TmpData
COMMIT;