Can I Attach a database with SMO?

I just developed a solution locally, I would like my installer to attach a database on the CD and then modify the web.config, I am interested in learning how to attach it programatically with SMO?

ThankHere is a very simple example:

using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

Server svr;

StringCollection sc;

svr = new Server();

sc = new StringCollection();

sc.Add(@."c:\test.mdf");

svr.AttachDatabase("test", sc);

|||Thank you very much. I love ya.. just kidding.

Can I add a link in a users "My Reports" to a report on a different report server?

We are in the process of commissioning a new reporting server. In the interim, I want to be able to update/modify reports and publish to one location only, and publish links to the users "My Reports" on the old server. Any ideas folks?

Report Manager only talks to a single server, so you can't mix reports from one server with another.

A quick workaround would be to upload an HTML file as a resource into the user's My Reports folder which redirects them to the new server.

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