Can Alter (current) Database?

I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.

I know I need to do:

ALTER DATABASE dbname SET ENABLE_BROKER

But I must avoid including the name of the database in the script. I did wonder if this would work:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

ALTER DATABASE @.DB SET ENABLE_BROKER

But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)

The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.

Any thoughts?

Ruth

Hi,

I guess you have to create a dynamic statement to make it work, something like:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

DECLARE @.SQLString VARCHAR(200)
SET @.SQLString = ' ALTER DATABASE ' + @.DB + 'SET ENABLE_BROKER'
EXEC(@.SqlString)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that looks good. You can of course replace @.DB with DB_NAME() in the SET.

Ruth

|||

Dynamic SQL is the only solution I know of. Make sure you protect yourself against SQL injection problems (e.g. use QUOTENAME on the database name). Also, ALTER DATABASE requires exclusive lock on the database, see http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||Remus,

I assumed that the value returned from DB_NAME() would be acceptable in SQL. Probably a bad assumption, really!

So, I did have:
SET @.AlterStmt = 'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'

but I should really have this?
SET @.AlterStmt = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER'

Thinking about injection, what is the best method to use when creating SQL on the fly. I don't want to use SqlParameters for everything as they obfuscate the code significantly. Is there an System.Data.SqlClient equivalent of QUOTENAME() ?

Ruth
|||

DB_NAME() is fine, it doesn't need to be passed into QUOTENAME. When I formulated the reply, Jens' post simply wasn't there and I didn't see it and I assumed the database name comes in as an argument.

You should worry about SQL Injection if the database name comes from an external (potentialy untrusted) source like a web form text field.

There is no equivalent to QUOTENAME, I usually use the simplest String.Replace method, like this:

string quotedDbName = "[" + dbnameVariable.Replace("]","]]") + "]";

HTH,
~ Remus