Question:
We have a requirement from our clients to deploy the database and set the database name in advance when installating the SQL Express or MSDE with the auto-install option.
Answer:
It is possible to create the database dynamically using SP_EXECUTESQL to build the SQL statements during runtime. You can then use EXECUTE to run the defined string, which is the option to create the database dynamically by using name the user inputs. The create database statement is constructed as follows:
declare @db varchar(50)
select @db=’testdb’
select @db=’create database ‘ +@db
Execute (@db)
To accomplish this you could also use SMO (Server Management Objects) in SQL Server 2005 which is installed automatically with the Client Tools option selected in the installation program. The SMO files can be specifically added or removed from the installation by choosing the SDK branch of the Client Components option on the feature selection screen during setup. Similary in SQL Server 2000 you can use DMO (Distributed Management Objects), in SQL Server 2005 the DMO feature is installed as a legacy component. To install the Legacy Components feature, click Advanced on the Components to Install page during SQL Server Setup. Expand the Client Components feature and then select Legacy Components. Refer the following Books Online articles for further information on usage of SMO or DMO:
Database Class
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspxHow to: Create, Alter, and Remove a Database in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162576.aspxCreate database in sql server using asp.net with C#
http://www.codeproject.com/useritems/Create_Database.asp]]>