Can I create Database at runtime

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

Can I create Database at runtime

Author

Topic

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-07 : 23:43:31

Hi,

I want to create Database at runtime..How can I create that.

Thanks in advance.

malay

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-08 : 00:21:05

Use the CREATE DATABASE statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-08 : 00:57:10

We can create with the Create command, but I want to pass database name in a variable..

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 01:15:20

use dynamic sql. pass the database name as a parameter and use it to buld sql string as


DECLARE @Yourvariable varchar(100)
SET @Yourvariable = 'your passed db name here'
SET @Sql='CREATE DATABASE [' + @Yourvariable+'] ON PRIMARY...'
EXEC (@sql)

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-08 : 01:25:47

I have written the Dynamic Query....
--------
declare @DBnames varchar(4000),
@mdflocation nvarchar(4000),
@ldflocation nvarchar(4000),
@mdfSize varchar(100),
@ldfSize varchar(100)

set @DBnames='malay'
set @mdflocation=N'c:\'
set @ldflocation=N'd:\'
set @mdfSize='2000KB'
set @ldfSize='1000KB'

declare @MDF nvarchar(4000)
declare @LDF nvarchar(4000)
--mdf Filename
set @MDF=@mdflocation+@DBnames+'_Data.mdf'
set @LDF=@ldflocation+@DBnames+'_Log.ldf'

--write dynamic query
declare @sp nvarchar(4000)
declare @param nvarchar(4000)

select @sp=N'CREATE DATABASE '+@DBnames+
N' ON PRIMARY '+
N'(' +
N'NAME ='+@DBnames+' , '+
N'FILENAME ='+@MDF+' , '+
N'SIZE ='+@mdfSize+' , '+
N'FILEGROWTH = 10%'+
N') '+
N'LOG ON'+
N'('+
N'NAME ='+@DBnames+'_log'+' , '+
N'FILENAME ='+@LDF+' , '+
N'SIZE ='+@ldfSize+' , '+
N'FILEGROWTH = 10%'+
N')'

exec sp_executesql @sp
print @@error
---------------
I m getting this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c'.
102

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 04:54:29

whats the pupose of N' inside string variable @Sql? if you want to use ' inside string escape it by using '' instead of '

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-08 : 05:44:24

Is it a problem with N'

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 05:47:43

nope. its problem with '. for using ' inside dynamic string you need to escape it with additional ' so that it becomes ''. see below for reason

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-08 : 12:34:25

My problem is solved....

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 12:46:33

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources