Recommended way to attach/detach database to sql server
In the past we have used SQLDMO and call a C++ dll through installscript to attach and detach our databases. With Sql 2005 Express SQLDMO is no longer included and Microsoft says you should use SQLSMO.
I tried writing a C# dll using SQLSMO and in the installscript using DotNetCoCreateObject to call the functions I wrote. But I get an error (I am running Vista) saying that the assembly does not allow partially trusted callers. I added the allowpartiallytrusted callers attribute to my C# assembly, but it does not seem to help. I think it is because SQLSMO does not allow partially trusted callers (whatever those are).
So I am wondering what my options are and what is the preferred method?
I can try wrapping my C# dll in C++ and call it in installscript like I was before. But I wonder if I would still get the same exception.
Or I can use osql to attach and detach. In SQLDMO we also called a function to make sure the sql server is started. I don't think osql has that capability.
Are there any other options? Or hints on how to get SQLSMO to work?
Instead of writing the new code, you may want to install the Microsoft SQL Server 2005 Backward Compatibility Components available at Feature Pack for Microsoft SQL Server 2005. The package includes the latest version of SQL Distributed Management Objects (SQL-DMO) that works with Microsoft SQL Server 2005.
