Create Multiple Database in SQL Server

Results 1 to 2 of 2

Thread: Create Multiple Database in SQL Server

  1. #1
    Yog Guest

    Default Create Multiple Database in SQL Server

    IS it possible to create an SQL server database from within my ASP scripts. The procedure is i want to create a database once a user pays and registers on the site. If not then how do i automate this process.<BR><BR>Another very important question:<BR><BR>SQL server supports creation of around 32000 databases under one Server copy. Our organization has a customer base of 40000 and we are developing a store manager for each of them storing their database on the SQL server copy on the Web. Is it acceptable that i have a database for all these customers. If not what is the other way or rather what is the most effective way of using SQL server resources.

  2. #2
    Garth Guest

    Default RE: Create Multiple Database in SQL Server

    The code shown below demonstrates how to use a stored procedure to create a database. Execute the DDL in master, replace the login information with valid values and then execute the ASP. Please note that in order to create a useful database you will need to add additional parameters (e.g. SIZE, MAXSIZE). See the Books Online to review all the arguments of the CREATE DATABASE statement.<BR><BR>And for your second question, I am sure you are going to want to have multiple servers to support the number of customers/databases. Just because SQL Server can support 32,767 databases it doesn&#039t mean you would actually want to put this many on one server.<BR><BR>Garth<BR><BR><BR><BR ><BR>&#060;!--DDL<BR>USE master<BR>go<BR>CREATE PROCEDURE sp_UTIL_CreateDatabase<BR>@DataBaseName sysname,<BR>@ReturnValue varchar(7) OUTPUT<BR>AS<BR>SET NOCOUNT ON<BR>DECLARE @SQL varchar(2000)<BR><BR>SET @SQL = &#039CREATE DATABASE &#039+@DataBaseName<BR>EXEC (@SQL)<BR><BR>IF @@ERROR &#060;&#062; 0<BR> SET @ReturnValue = &#039Failure&#039<BR>ELSE<BR> SET @ReturnValue = &#039Success&#039<BR>--&#062;<BR><BR><BR>&#060;!--#include file=""--&#062;<BR>&#060;%<BR> DataBaseName = "TestDataBase"<BR><BR> Set objConn = server.CreateObject("ADODB.Connection")<BR><BR> REM DSN-Less ODBC<BR> REM objConn.Open "Driver={SQL Server};Server=ACE;Database=master;Uid=sa;Pwd="<BR ><BR> REM DSN-Less OLE-DB<BR> objConn.Open "Provider=SQLOLEDB;Server=ACE;Database=master;Uid= sa;Pwd="<BR><BR><BR> Set cmd1 = server.CreateObject("ADODB.Command")<BR> cmd1.ActiveConnection = objConn<BR> cmd1.CommandType = adCmdStoredProc<BR> cmd1.CommandText = "sp_UTIL_CreateDatabase"<BR> cmd1.Parameters.Append cmd1.CreateParameter("DataBaseName",adVarChar,adPa ramInput,128,DataBaseName)<BR> cmd1.Parameters.Append cmd1.CreateParameter("ReturnValue",adVarChar,adPar amOutput,7)<BR> cmd1.Execute<BR><BR> response.write cmd1.Parameters("ReturnValue")<BR> %&#062;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts