Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: creating databases on the fly

    hello,

    i am trying to create a database by using a store procedure. This stored procedure takes two input parameters. i want to assign these parameters to the 'Filename' attributes when i'm creating the database both for the .mdf and .ldf files. However i keep getting an error.

    These work -------
    FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.mdf',

    FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',

    These do'nt work--------
    FILENAME = @Databasepath,
    FILENAME = @Databaselogpath,

    Here is my code:
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    CREATE PROCEDURE rico_dbasescript
    @Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
    AS
    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
    DROP DATABASE [Sardonyxrioctestdb]



    CREATE DATABASE Sardonyxrioctestdb
    ON
    ( NAME = 'Sardonyxrioctestdb_dat',
    FILENAME = @Databasepath,
    --FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
    LOG ON
    ( NAME = 'Sardonyxrioctestdb_log',
    FILENAME = @Databaselogpath,
    --FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )
    GO



    I am still researching my problem but i would appreciate any help. Thanks guys.

    JamaicanGuy
    **I am a newbie .net developer.
    Jah Bless!!!!!!!!!!!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use dynamic sql:
    Code:
    CREATE PROCEDURE rico_dbasescript 
    @Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
    AS
    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
    DROP DATABASE [Sardonyxrioctestdb]
    
    declare @SQLString varchar(8000)
    
    set	@SQLString = 'CREATE DATABASE Sardonyxrioctestdb ON
    	(NAME = ''Sardonyxrioctestdb_dat'',
    	FILENAME = ' + @Databasepath + ',
    	SIZE = 10,
    	MAXSIZE = 50,
    	FILEGROWTH = 5 )
    LOG ON
    	(NAME = ''Sardonyxrioctestdb_log'',
    	FILENAME = ' + @Databaselogpath + ',
    	SIZE = 5MB,
    	MAXSIZE = 25MB,
    	FILEGROWTH = 5MB )'
    
    execute (@SQLString)
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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