Create DB in Stored Procedure - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Create DB in Stored Procedure

Author

Topic

lab
Starting Member

14 Posts

Posted - 2005-04-08 : 13:19:55

Hi all!

I'm creating a C inteface that manage DB's and I need to create new databases... To do so I wanted to use stored procedures to have smaller C code (because of the large numbers of table of the template DB). So I create stored procedures:
The first one creates a temporary DB
The second one Adds tables to the DB (not important)
The Third one execute the first two and rename the temporary db to the desired name ..

PROCEDURE 1:
CREATE PROCEDURE [dbo].[CreateTMPDB] AS

CREATE DATABASE [DB_TMP] ON (NAME = N'DB_TMP_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$DB\data\DB_TMP_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'DB_TMP_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$DB\data\DB_TMP_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CS_AS
GO

PROCEDURE 3:
CREATE PROCEDURE [dbo].[CreateDB] @DBNAME varchar(50) AS
exec CreateTMPDB
exec AddTableToNewDB
exec sp_renamedb 'TATDB_TMP', @DBNAME
GO

The Problem here is that when I rename the DB using sp_renamdb, it doesn't rename the data file and the log file. So I can't add 2 db cause they end up with the same file ans I get errors...

I used that method because I was unable to create a DB using a variable as the DB Name but its probably just because I'm not familiar enough with SQL Server Stored Procedures... I tried using this before but it didnt work:

CREATE PROCEDURE [dbo].[CreateTMPDB] @DBNAME varchar(50) AS

CREATE DATABASE @DBNAME ON (NAME = @DBNAME+'_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$DB\data\'+@DBNAME+'_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = @DBNAME+'_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$DB\data\'+@DBNAME+'_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CS_AS
GO

Using this I get lots of error in the concatenation for the paths and filename and It does not even work when I just use CREATE DATABASE @DBNAME ...

Does Anyone have an idea on how I can create my database using a name specified by a variable ?

Thx,
Steve

lab
Starting Member

14 Posts

Posted - 2005-04-08 : 15:10:29

I've been trying some other stuff and I think I'm almost there if someone could give me a little hand... using this procedure

CREATE PROCEDURE [dbo].[CreateDB] @DATA varchar(255) AS
declare @sql varchar(4000)
set @sql = 'CREATE DATABASE [' + @DATA + ']'
exec @sql

With this I actually get the correct sintax but it doesnt execute...

Server: Msg 203, Level 16, State 2, Procedure CreateDB, Line 4
The name 'CREATE DATABASE [data]' is not a valid identifier.

Any Ideas ?

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-08 : 15:19:37

It should be

EXEC (@sql)

lab
Starting Member

14 Posts

Posted - 2005-04-08 : 15:32:17

ah !! thats it !

Thx a lot ... works great!

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources