changing 'Filename' attri. when creating databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

changing 'Filename' attri. when creating databases

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-13 : 09:24:55

Obadiah writes "hello,

I am a newbie to developing .net applications unfortunately my boss and my colleaques think i am a pro (interview went too well) and i would like to get at least this months paycheck before am fired....so i here's my dumb question:

I want to create a SQL database from my vb.net application using stored procedures, however i want to be able to set the Filename attribute ('Filename = C:\Program Files\Microsoft SQL Server\MSSQL$SARDONYXINSTANCE\Data\testdb.mdf') by means of a input parameter to the stored procedure. like so...
Filename = @databasepath.

HERE is what i have:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctestdb.mdf' , @Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctestdblog.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\Sardonyxrioctestdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctestdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

The above procedure works fine when Filename = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctestdb.mdf'
but bombs out when Filename = @Databaselogpath.

Thank you in advance."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 09:45:49

Use dynamic SQL as below:

exec ('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 )')

or even better..use sp_executesql !

Harsh Athalye
India.
"Nothing is Impossible"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-07-13 : 09:53:59

well, if you REALLY have to do this, you need to do this as a dynamic query:


drop procedure rico_dbasescript
go
CREATE PROCEDURE rico_dbasescript
	@Databasepath varchar(100) = 'c:\Sardonyxrioctestdb.mdf' , 
	@Databaselogpath varchar(100)= 'c:\Sardonyxrioctestdblog.ldf'
AS
BEGIN
	IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
	DROP DATABASE [Sardonyxrioctestdb]
	DECLARE @SQlCmd	VARCHAR(1000)

	SET @SQlCmd = '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 )'
	SELECT @SQlCmd
	EXEC (@SQlCmd)
END
go
DECLARE 
	@Databasepath varchar(100),
	@Databaselogpath varchar(100)
EXEC rico_dbasescript

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources