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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment