8 Replies Latest reply on Feb 9, 2010 11:55 AM by TiGGi

    Creating an MSSQL Database

    DIDMedia

      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

        • 1. Re: Creating an MSSQL Database
          TiGGi Level 1

          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

          1 person found this helpful
          • 2. Re: Creating an MSSQL Database
            ilssac Level 5

            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.

            • 3. Re: Creating an MSSQL Database
              Adam Cameron. Level 5
              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

              • 4. Re: Creating an MSSQL Database
                DIDMedia Level 1

                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>

                • 5. Re: Creating an MSSQL Database
                  DIDMedia Level 1

                  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>

                  • 6. Re: Creating an MSSQL Database
                    Dan Bracuk Level 5

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

                    • 7. Re: Creating an MSSQL Database
                      DIDMedia Level 1

                      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>

                      • 8. Re: Creating an MSSQL Database
                        TiGGi Level 1

                        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