CODEWITHSUNDEEP
sql-serverdatabaset-sqlalter
Pete
Pete

Reputation: 58412

Alter database name error

I have the following script that I am trying to run through a winforms program that just executes all the scripts in a directory:

USE [master]
GO

EXEC('ALTER DATABASE [AT100Reporting] MODIFY Name = [IngeniumDynamics];')
GO

However, when I try to run this I get the following error:

ALTER DATABASE statement not allowed within multi-statement transaction.

I googled this and the main thing I could find about it was that alter db statements should run in autocommit mode. A further search made me change my script to

USE [master]
GO

SET IMPLICIT_TRANSACTIONS OFF

EXEC('ALTER DATABASE [AT100Reporting] MODIFY Name = [IngeniumDynamics];')
GO

SET IMPLICIT_TRANSACTIONS ON

Yet I still have the same error. Does anyone know what I need to do to make this script run properly

I have also tried this without the EXEC

Upvotes: 0

Views: 2630

Answers (3)

Pete
Pete

Reputation: 58412

As Joachim Isaksson has pointed out, the problem was not with the script but with the winforms program that was running the scripts - I had my TransactionScope options set to TransactionScopeOption.Required but if I changed this to TransactionScopeOption.Suppress the query worked fine

Upvotes: 1

NKR
NKR

Reputation: 2943

Then It should work :

First Set an Single User Mode : and Rename it.

declare @OldName varchar(50)='stackoverflow'
declare @NewName varchar(50)='a'

EXEC('ALTER DATABASE '+@OLDNAME + ' SET SINGLE_USER')

exec('sp_renamedb '+@OldName+','+@NewName)

//And again put it in Multi-User Mode ::
EXEC('ALTER DATABASE '+@NewName+ ' SET MULTI_USER')

Upvotes: 0

NKR
NKR

Reputation: 2943

You can use, sp_renamedb OldDbName,NewDbName to rename the database :

exec ('sp_renamedb questionoverflow, stackoverflow')

Dynamic Way :

declare @OldName varchar(50)='questionoverflow'
declare @NewName varchar(50)='stackoverflow'

exec('sp_renamedb '+@OldName+','+@NewName)

Upvotes: 0

Related Questions