SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 want to create multiple databases by query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CNeelam
Starting Member

4 Posts

Posted - 08/25/2009 :  06:04:44  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 08/25/2009 :  06:08:06  Show Profile  Reply with Quote
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
Go to Top of Page

CNeelam
Starting Member

4 Posts

Posted - 08/25/2009 :  06:15:44  Show Profile  Reply with Quote
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.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 08/25/2009 :  06:22:06  Show Profile  Reply with Quote
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.

Edited by - RickD on 08/25/2009 06:24:52
Go to Top of Page

CNeelam
Starting Member

4 Posts

Posted - 08/25/2009 :  06:39:59  Show Profile  Reply with Quote
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
Go to Top of Page

CNeelam
Starting Member

4 Posts

Posted - 08/25/2009 :  07:23:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000