Creating an MSSQL Database

[DIDMedia-6vmW65]

Is it possible to create a new MS SQL database from a coldfusion script or query?

We're trying to create databases and their respective datasources on the fly.  We've conquered creating a new datasource successfully and coldfusion has a variety of support for creating tables, altering them etc,

but can you create a new Database itself with coldfusion on the fly?

Thanks in advance,

Brian

For example would it be possible to run this script where #FORM.datasource# is a form variable from a web form...

CREATE DATABASE [#FORM.datasource#] ON  PRIMARY
( NAME = N'#FORM.datasource#', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\#FORM.datasource#.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'#FORM.datasource#_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\#FORM.datasource#_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'#FORM.datasource#', @NeW_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [#FORM.datasource#].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [#FORM.datasource#] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [#FORM.datasource#] SET ANSI_NULLS OFF
GO
ALTER DATABASE [#FORM.datasource#] SET ANSI_PADDING OFF
GO
ALTER DATABASE [#FORM.datasource#] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [#FORM.datasource#] SET ARITHABORT OFF
GO
ALTER DATABASE [#FORM.datasource#] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [#FORM.datasource#] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [#FORM.datasource#] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [#FORM.datasource#] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [#FORM.datasource#] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [#FORM.datasource#] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [#FORM.datasource#] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [#FORM.datasource#] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [#FORM.datasource#] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [#FORM.datasource#] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [#FORM.datasource#] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [#FORM.datasource#] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [#FORM.datasource#] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [#FORM.datasource#] SET  READ_WRITE
GO
ALTER DATABASE [#FORM.datasource#] SET RECOVERY FULL
GO
ALTER DATABASE [#FORM.datasource#] SET  MULTI_USER
GO
ALTER DATABASE [#FORM.datasource#] SET PAGE_VERIFY CHECKSUM 
GO
USE [#FORM.datasource#]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [#FORM.datasource#] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

[TiGGi]

Yes there is a way, what I did I created a CF datasource to master database and then used this connection to create/alter/drop tables.

<cfquery datasource="master">
USE [#dbname#]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Users](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [nvarchar](50) NOT NULL,
    [Password] [nvarchar](15) NOT NULL,.....etc
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

</cfquery>

You mentioned that you are able to create datasource, this is something in my to-do list and I would apprechiate some help.  All of this sounds very familiar to me and you might be working on somehing that I've already did a lot.  One thing that might be helpfull to you that I've done is to generate scripts to  create site in IIS and virtual folders for the sites.  Let me know if you need some more info. thanks

[ilssac · Accepted solution]

Technically no, because ColdFusion doesn't do anything.  All it does is bundle up the SQL string and pass it to the database driver.

But yes, you can provide any valid SQL inside a <cfquery...> block and it will be passeed to the database and the database will do whatever the SQL statement is trying to tell it to do.

This assumes, of course, that security has not been put in place to prevent this type of use in the database.  I.E.  It is usually considered a very bad idea to have the database account that ColdFusion connects with from having create, alter and drop permissions.  The ColdFusion administrator also offers a security feature to try and deny these command strings from the SQL statments being processed.

But if you are willing to live with that risk, you can have your CFML code do all kinds of powerful things with your database, just be very aware that if you can get your CFML to do this, hackers maybe able to do so as well.

[Adam Cameron.]

coldfusion has a variety of support for creating tables, altering them etc,

CF doesn't support any SQL in the sense you are thinking.  All CF does is pass the SQL string to the DB driver, which passes it to the DB.  CF doesn't execute any of the SQL itself.

For example would it be possible to run this script where #FORM.datasource# is a form variable from a web form...

Well... the best way to find out is to try it, I guess.

You'll need to be connecting with a DB user that has DB-create privileges...

--

Adam

[DIDMedia-6vmW65]

The answer to using the #FORM.datasource# is yes.  It works just fine, just as you can build a query variable and run it like so:

<cfset mydatasource = "newData">

<cfset query = "SELECT * FROM my_table">

<cfquery name="name" datasource="#mydatasource#">

     #query#

</cfquery>

This is the same thing in CF as:

<cfquery name="name" datasource="newData">

     SELECT * FROM my_table

</cfquery>

[DIDMedia-6vmW65]

It turns out that it was really quite simple.

All it took was a simple:

<cfquery name="NAME" datasource="EXISTING DATASOURCE">

     CREATE DATABASE database_name

</cfquery>

[Dan_Bracuk]

If you are going to do this, you need something to prevent duplicate db names on your db server and duplicate datasources in cf.

[DIDMedia-6vmW65]

CF and/or MS SQL will throw an error if you try to create a duplicate database, but a simple check would be easy to place in.

As for the commend above on how to remotely create a CF datasource with <cfscript> you'll find the code below.  For more information, search coldfusion API on the CF eDocs.

<cfscript>
                    // Login is always required. This example uses two lines of code.
                    adminObj = createObject("component","cfide.adminapi.administrator");
                    adminObj.login("coldfusion_admin_password");
               
                    // Instantiate the data source object.
                    myObj = createObject("component","cfide.adminapi.datasource");
               
                    // Create a DSN.
                    myObj.setMSSQL(driver="MSSQLServer",
                        name="datasource_name",
                        host = "12.34.54.34", // sql server host
                        port = "1433",
                        database = "database_name",
                        username = "your_sql_username",
                        password = "your_sql_password", // if neccessary
                        login_timeout = "29",
                        timeout = "23",
                        interval = 6,
                        buffer = "64000",
                        blob_buffer = "64000",
                        setStringParameterAsUnicode = "false",
                        description = "datasource_description",
                        pooling = true,
                        maxpooledstatements = 999,
                        enableMaxConnections = "true",
                        maxConnections = "299",
                        enable_clob = true,
                        enable_blob = true,
                        disable = false,
                        storedProc = true,
                        alter = false,
                        grant = true,
                        select = true,
                        update = true,
                        create = true,
                        delete = true,
                        drop = false,
                        revoke = false );
                </cfscript>

[TiGGi]

I just realized that I posted example on how to create table, sorry.  Here is how you can create db and check if it exists first.

<Cfquery datasource="master">
if not exists(select * from sys.databases where name = '#dbname#')
CREATE DATABASE #dbname#
ON
( NAME = #dbname#_dat,
  FILENAME = 'C:\SQL\#dbname#.mdf' )
LOG ON
( NAME = '#dbname#_log',
  FILENAME = 'C:\SQL\#dbname#.ldf')
</cfquery>

You can use the rest of the db options you need, just plug them into the query