Tuesday, February 14, 2012

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!!!!!!!!!!!!Use dynamic sql:
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

No comments:

Post a Comment