want to create multiple databases by query

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

want to create multiple databases by query

Author

Topic

CNeelam
Starting Member

4 Posts

Posted - 2009-08-25 : 06:04:44

Hi

With below stored procedure I am facing below Issue:
1. It is properly printing the database name as DB1,DB2,DB3 and also the path, then why it is not taking the database name in the create database query at the place of @dbnm?

Expected Result: The SP should create 3 database named DB1, DB2, DB3 each with it respective .mdf and a log file.

Please help me with below script

declare @cnt INT
declare @sql varchar(3000)
declare @dbnm varchar(30)
declare @path varchar(300)
SET @cnt = 1
set @path ='D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\'

WHILE (@cnt <=3)
BEGIN
Set @dbnm = 'DB'+CAST(@cnt as varchar)

print @dbnm /*Prints database name*/
print @path /* Prints path*/

set @sql = 'CREATE DATABASE ' + @dbnm + ' ON PRIMARY (NAME='+ @dbnm + ',FILENAME='+@path+@dbnm+'.mdf' +',' +
' SIZE = 3072KB , FILEGROWTH = 1024KB' +' ),' +

'LOG ON'+'( NAME =' +@dbnm+'_log'+','+'FILENAME ='+ @path+@dbnm+'_log.ldf'+ ','+
' SIZE = 1024KB , FILEGROWTH = 10%'+' )'

execute(@sql)

SET @cnt = @cnt + 1
END
GO

Error in Output after execution

DB1
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATAMsg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D'.
Msg 132, Level 15, State 1, Line 3
The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
DB2
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATAMsg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D'.
Msg 132, Level 15, State 1, Line 3
The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
DB3
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATAMsg 102, Level 15, State 1, Line 3
Incorrect syntax near 'D'.
Msg 132, Level 15, State 1, Line 3
The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.

Thanks,
Neelam

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-25 : 06:08:06

Can you run this and post the result?

declare @cnt INT
declare @sql varchar(3000)
declare @dbnm varchar(30)
declare @path varchar(300)
SET @cnt = 1
set @path ='D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\'

WHILE (@cnt <=3)
BEGIN
Set @dbnm = 'DB'+CAST(@cnt as varchar)

print @dbnm /*Prints database name*/
print @path /* Prints path*/

set @sql = 'CREATE DATABASE ' + @dbnm + ' ON PRIMARY (NAME='+ @dbnm + ',FILENAME='+@path+@dbnm+'.mdf' +',' +
' SIZE = 3072KB , FILEGROWTH = 1024KB' +' ),' +

'LOG ON'+'( NAME =' +@dbnm+'_log'+','+'FILENAME ='+ @path+@dbnm+'_log.ldf'+ ','+
' SIZE = 1024KB , FILEGROWTH = 10%'+' )'
select @sql
--execute(@sql)

SET @cnt = @cnt + 1
END
GO

CNeelam
Starting Member

4 Posts

Posted - 2009-08-25 : 06:15:44

yes, it shows query executed successfully with the below message:
DB1
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA
(1 row(s) affected)
DB2
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA
(1 row(s) affected)
DB3
D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA
(1 row(s) affected)

But I could not see the DB1, DB2, DB3 under databases in the Management Studio,
Please help me.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-25 : 06:22:06

Theres something funny going on there, as it should have shown:

CREATE DATABASE DB01 ON PRIMARY (NAME=DB01,FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\DB01.mdf',SIZE = 3072KB , FILEGROWTH = 1024KB),
LOG ON( NAME =DB01_log,FILENAME ='D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\DB01_log.ldf',SIZE = 1024KB , FILEGROWTH = 10%)

Could you look in D:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\ and see if any of the files exist?

Also, you will need to put certain things in double quotes, such as the paths, so in your code you will need 3 quotes each side of the path.

CNeelam
Starting Member

4 Posts

Posted - 2009-08-25 : 06:39:59

Hi,
Thanks for ur help.
See below i had made some changes to the script, but stiil i cannot see the databases created in the management studio.
I am totally new to sql server, please help me.

declare @cnt INT
declare @sql varchar(3000)
declare @dbnm varchar(30)
declare @path varchar(300)
SET @cnt = 1
set @path ='''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'

WHILE (@cnt <=3)
BEGIN
Set @dbnm = 'DB'+CAST(@cnt as varchar)

print @dbnm /*Prints database name*/
print @path /* Prints path*/

set @sql = 'CREATE DATABASE ' + @dbnm + ' ON PRIMARY (NAME='+ @dbnm + ',FILENAME='+@path+@dbnm+'.mdf'+'''' +',' +
' SIZE = 3072KB , FILEGROWTH = 1024KB' +' ),' +

'LOG ON'+'( NAME =' +@dbnm+'_log'+','+'FILENAME ='+ @path+@dbnm+'_log.ldf'+''''+ ','+
' SIZE = 1024KB , FILEGROWTH = 10%'+' )'
select @sql
--execute(@sql)

SET @cnt = @cnt + 1
END
GO

CNeelam
Starting Member

4 Posts

Posted - 2009-08-25 : 07:23:44

Hey i got the soln and achived what i really wanted to:-)

To create Multiple databases, each with .mdf and .ldf files and 4 filegroups each having 1 .ndf file

declare @cnt INT
declare @sql varchar(3000)
declare @dbnm varchar(30)
declare @path varchar(300)
SET @cnt = 1
set @path ='''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'

WHILE (@cnt <=3)
BEGIN
Set @dbnm = 'DB'+CAST(@cnt as varchar)

print @dbnm /*Prints database name*/
print @path /* Prints path*/

set @sql = 'CREATE DATABASE ' + @dbnm + ' ON PRIMARY (NAME='+ @dbnm + ',FILENAME='+@path+@dbnm+'.mdf'+'''' +','+
' SIZE = 3072KB , FILEGROWTH = 1024KB' +' ),' +

'FILEGROUP GRP1
( NAME = '+@dbnm+'F1,
FILENAME = '+@path+@dbnm+'F1.ndf'+'''' +',
SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP GRP2
( NAME = '+@dbnm+'F2,
FILENAME = '+@path+@dbnm+'F2.ndf'+'''' +' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP GRP3
( NAME = '+@dbnm+'F3,
FILENAME = '+@path+@dbnm+'F3.ndf'+'''' +' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP GRP4
( NAME = '+@dbnm+'F4,
FILENAME = '+@path+@dbnm+'F4.ndf'+''''+' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )'+

'LOG ON'+'( NAME =' +@dbnm+'_log'+','+'FILENAME ='+ @path+@dbnm+'_log.ldf'+''''+ ','+
' SIZE = 1024KB , FILEGROWTH = 10%'+' )'
select @sql
execute(@sql)

SET @cnt = @cnt + 1
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources