Create Databse using Stored Procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Create Databse using Stored Procedure?

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-12 : 11:01:19

Is it possible to create a databse inside a stored procedure? I'm trying to do this now and it doesn't seem to be working. I get the error: CREATE DATABASE statement not allowed within multi-statement transaction.

Even though its not in a transaction and implicit transactions are set to off.

My code:

ALTER PROCEDURE [dbo].[sp_CreateDatabase] 
	@database_server varchar(100), -- Server name
	@database_name varchar(200), -- Database name
	@data_spec_id int, -- data_spec_id for the tables to create
	@database_type_id int, -- The spec type, Source, Standard, or Derived 
	@comment varchar(max) = NULL, --Comment, defaults to null
	@User varchar(200) = NULL, --User executing the SP. If left null it will default to the system user.
	@database_id int OUTPUT
WITH RECOMPILE
AS
--===================================================================================================
--Date			Author				Comment
--2010-03-08	Geoff Price			This creates a new database, sets up the users, logs the event,
--									and inserts and new record in the Databases table.
--===================================================================================================

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE -- Error handling variables
	@Error int, 
	@Msg VarChar(255),
	@Procedure varchar(250),
	@ProcessID int,
	@Severity int;

DECLARE -- Execution variables
	@SQL varchar(max);

SET @Procedure = 'sp_CreateDatabase';
SET @User = ISNULL(@User, SYSTEM_USER);

BEGIN TRY
	SET @SQL = 	
	'Use [master]
	CREATE DATABASE ['+@database_name+'] ON  PRIMARY 
	( NAME = N'''+@database_name+''', FILENAME = N''g:\databases\data\'+@database_name+'.mdf'' , SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
	 LOG ON 
	( NAME = N'''+@database_name+'_log'', FILENAME = N''D:\databases\logs\'+@database_name+'_log.ldf'' , SIZE = 100000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

	EXEC dbo.sp_dbcmptlevel @dbname=N'''+@database_name+''', @new_cmptlevel=90

	IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
	begin
	EXEC ['+@database_name+'].[dbo].[sp_fulltext_database] @action = ''disable''
	end

	ALTER DATABASE ['+@database_name+'] SET ANSI_NULL_DEFAULT OFF 

	ALTER DATABASE ['+@database_name+'] SET ANSI_NULLS OFF 

	ALTER DATABASE ['+@database_name+'] SET ANSI_PADDING OFF 

	ALTER DATABASE ['+@database_name+'] SET ANSI_WARNINGS OFF 

	ALTER DATABASE ['+@database_name+'] SET ARITHABORT OFF 

	ALTER DATABASE ['+@database_name+'] SET AUTO_CLOSE OFF 

	ALTER DATABASE ['+@database_name+'] SET AUTO_CREATE_STATISTICS ON 

	ALTER DATABASE ['+@database_name+'] SET AUTO_SHRINK OFF 

	ALTER DATABASE ['+@database_name+'] SET AUTO_UPDATE_STATISTICS ON 

	ALTER DATABASE ['+@database_name+'] SET CURSOR_CLOSE_ON_COMMIT OFF 

	ALTER DATABASE ['+@database_name+'] SET CURSOR_DEFAULT  GLOBAL 

	ALTER DATABASE ['+@database_name+'] SET CONCAT_NULL_YIELDS_NULL OFF 

	ALTER DATABASE ['+@database_name+'] SET NUMERIC_ROUNDABORT OFF 

	ALTER DATABASE ['+@database_name+'] SET QUOTED_IDENTIFIER OFF 

	ALTER DATABASE ['+@database_name+'] SET RECURSIVE_TRIGGERS OFF 

	ALTER DATABASE ['+@database_name+'] SET  ENABLE_BROKER 

	ALTER DATABASE ['+@database_name+'] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

	ALTER DATABASE ['+@database_name+'] SET DATE_CORRELATION_OPTIMIZATION OFF 

	ALTER DATABASE ['+@database_name+'] SET TRUSTWORTHY OFF 

	ALTER DATABASE ['+@database_name+'] SET ALLOW_SNAPSHOT_ISOLATION OFF 

	ALTER DATABASE ['+@database_name+'] SET PARAMETERIZATION SIMPLE 

	ALTER DATABASE ['+@database_name+'] SET  READ_WRITE 

	ALTER DATABASE ['+@database_name+'] SET RECOVERY SIMPLE 

	ALTER DATABASE ['+@database_name+'] SET  MULTI_USER 

	ALTER DATABASE ['+@database_name+'] SET PAGE_VERIFY CHECKSUM  

	ALTER DATABASE ['+@database_name+'] SET DB_CHAINING OFF'

	EXEC(@SQL);

IF DB_ID(@database_name) IS NOT NULL
BEGIN
	SET @SQL =
	'Use ['+@database_name+']
	/****** Object:  User [mmApplication]    ******/
	CREATE USER [mmApplication] FOR LOGIN [mmApplication] WITH DEFAULT_SCHEMA=[dbo]
	EXEC sp_addrolemember ''db_datareader'', ''mmApplication''
	EXEC sp_addrolemember ''db_datawriter'', ''mmApplication''
	GRANT EXECUTE TO [mmApplication]

	/****** Object:  User [VALENTECH\SQLR]    ******/
	CREATE USER [VALENTECH\SQLR] FOR LOGIN [VALENTECH\SQLR]
	EXEC sp_addrolemember ''db_datareader'', ''VALENTECH\SQLR''
	EXEC sp_addrolemember ''db_datawriter'', ''VALENTECH\SQLR'''

	EXEC(@SQL);

	BEGIN TRANSACTION

		INSERT INTO Databases
		(data_spec_id, database_type_id, database_server, database_name, comment)
		VALUES
		(@data_spec_id, @database_type_id, @database_server, @database_name, @comment)

		EXEC sp_Log_Event @database_name, 17, NULL, @User; 

		SET @database_id = IDENT_CURRENT('Databases');

	COMMIT
END
END TRY
BEGIN CATCH  --error
	--get the system error number
	SELECT @Error = ERROR_NUMBER();

	--get the system error message
	SELECT @Msg = ERROR_MESSAGE();

	--set the severity level to severe error
	SET @Severity = 4;

	--error handling
	EXEC sp_Handle_Error @Error, @Msg, NULL, @Severity, NULL, @Procedure, 'LOG', @User;

	RETURN @Error;
END CATCH  --error

SET NOCOUNT OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:18:08

whats the purpose of creating a DB inside procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-12 : 11:29:49

quote:
Originally posted by visakh16

whats the purpose of creating a DB inside procedure?

Its to be run from an application. Actually I'm trying to just run it as a script now and that doesn't work either.

DECLARE -- Parameters
	@database_server varchar(100), -- Server name
	@database_name varchar(200), -- Database name
	@data_spec_id int, -- data_spec_id for the tables to create
	@database_type_id int, -- The spec type, Source, Standard, or Derived 
	@comment varchar(max), --Comment
	@User varchar(200), 
	@database_id int

DECLARE -- Execution variables
	@SQL varchar(max);

SET	@database_server = 'SX'
SET	@database_name = 'DBY'
SET	@data_spec_id = 1
SET	@database_type_id = 2
SET	@comment = 'New databse with revised schema'

SET @User = ISNULL(@User, SYSTEM_USER);

SET @SQL = 	
'Use [master]
CREATE DATABASE ['+@database_name+'] ON  PRIMARY 
( NAME = N'''+@database_name+''', FILENAME = N''g:\databases\data\'+@database_name+'.mdf'' , SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'''+@database_name+'_log'', FILENAME = N''D:\databases\logs\'+@database_name+'_log.ldf'' , SIZE = 100000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

EXEC dbo.sp_dbcmptlevel @dbname=N'''+@database_name+''', @new_cmptlevel=90

IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC ['+@database_name+'].[dbo].[sp_fulltext_database] @action = ''disable''
end

ALTER DATABASE ['+@database_name+'] SET ANSI_NULL_DEFAULT OFF 

ALTER DATABASE ['+@database_name+'] SET ANSI_NULLS OFF 

ALTER DATABASE ['+@database_name+'] SET ANSI_PADDING OFF 

ALTER DATABASE ['+@database_name+'] SET ANSI_WARNINGS OFF 

ALTER DATABASE ['+@database_name+'] SET ARITHABORT OFF 

ALTER DATABASE ['+@database_name+'] SET AUTO_CLOSE OFF 

ALTER DATABASE ['+@database_name+'] SET AUTO_CREATE_STATISTICS ON 

ALTER DATABASE ['+@database_name+'] SET AUTO_SHRINK OFF 

ALTER DATABASE ['+@database_name+'] SET AUTO_UPDATE_STATISTICS ON 

ALTER DATABASE ['+@database_name+'] SET CURSOR_CLOSE_ON_COMMIT OFF 

ALTER DATABASE ['+@database_name+'] SET CURSOR_DEFAULT  GLOBAL 

ALTER DATABASE ['+@database_name+'] SET CONCAT_NULL_YIELDS_NULL OFF 

ALTER DATABASE ['+@database_name+'] SET NUMERIC_ROUNDABORT OFF 

ALTER DATABASE ['+@database_name+'] SET QUOTED_IDENTIFIER OFF 

ALTER DATABASE ['+@database_name+'] SET RECURSIVE_TRIGGERS OFF 

ALTER DATABASE ['+@database_name+'] SET  ENABLE_BROKER 

ALTER DATABASE ['+@database_name+'] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

ALTER DATABASE ['+@database_name+'] SET DATE_CORRELATION_OPTIMIZATION OFF 

ALTER DATABASE ['+@database_name+'] SET TRUSTWORTHY OFF 

ALTER DATABASE ['+@database_name+'] SET ALLOW_SNAPSHOT_ISOLATION OFF 

ALTER DATABASE ['+@database_name+'] SET PARAMETERIZATION SIMPLE 

ALTER DATABASE ['+@database_name+'] SET  READ_WRITE 

ALTER DATABASE ['+@database_name+'] SET RECOVERY SIMPLE 

ALTER DATABASE ['+@database_name+'] SET  MULTI_USER 

ALTER DATABASE ['+@database_name+'] SET PAGE_VERIFY CHECKSUM  

ALTER DATABASE ['+@database_name+'] SET DB_CHAINING OFF'

EXEC(@SQL);

IF DB_ID(@database_name) IS NOT NULL
BEGIN
	SET @SQL =
	'Use ['+@database_name+']
	/****** Object:  User [mmApplication]    ******/
	CREATE USER [mmApplication] FOR LOGIN [mmApplication] WITH DEFAULT_SCHEMA=[dbo]
	EXEC sp_addrolemember ''db_datareader'', ''mmApplication''
	EXEC sp_addrolemember ''db_datawriter'', ''mmApplication''
	GRANT EXECUTE TO [mmApplication]

	/****** Object:  User [VALENTECH\SQLR]    ******/
	CREATE USER [VALENTECH\SQLR] FOR LOGIN [VALENTECH\SQLR]
	EXEC sp_addrolemember ''db_datareader'', ''VALENTECH\SQLR''
	EXEC sp_addrolemember ''db_datawriter'', ''VALENTECH\SQLR'''

	EXEC(@SQL);

	BEGIN TRANSACTION

		INSERT INTO DataStudio_Dev..Databases
		(data_spec_id, database_type_id, database_server, database_name, comment)
		VALUES
		(@data_spec_id, @database_type_id, @database_server, @database_name, @comment)

		EXEC DataStudio_Dev..sp_Log_Event @database_name, 17, NULL, @User; 

		SET @database_id = IDENT_CURRENT('Databases');

	COMMIT
END

SELECT @database_id

Essentially I need to programatically create a database, setup some default users, do some logging, and create some other records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:34:18

you can use this if you want

http://www.codeproject.com/KB/tips/CreateSQLDV.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-12 : 11:49:25

quote:
Originally posted by visakh16

you can use this if you want

Thanks for that I may have to do something like that, but the reason I'd prefer an SP is that we want all of the functionality to be able to be performed from either a UI or from the query window using the same code.

So is it a fact that there is no way to crate a DB within a stored proc?

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-12 : 12:05:46

Actually I have it sort of working now, I just can't create the users in the same stored proc. I don't see any way to create users in a different DB, everything I can find on how to create users says to USE the databse you want to create in first, whch I can't do in an SP.

This is what's working now:

BEGIN TRY
IF DB_ID(@database_name) IS NULL
BEGIN
	SET @SQL = 	
	'CREATE DATABASE ['+@database_name+'] ON  PRIMARY 
	( NAME = N'''+@database_name+''', FILENAME = N''g:\databases\data\'+@database_name+'.mdf'' , SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
	 LOG ON 
	( NAME = N'''+@database_name+'_log'', FILENAME = N''D:\databases\logs\'+@database_name+'_log.ldf'' , SIZE = 100000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'

	EXEC(@SQL);
END
IF DB_ID(@database_name) IS NOT NULL
BEGIN
BEGIN TRANSACTION
	INSERT INTO Databases
	(data_spec_id, database_type_id, database_server, database_name, comment)
	VALUES
	(@data_spec_id, @database_type_id, @database_server, @database_name, @comment)

	EXEC sp_Log_Event @database_name, 17, NULL, @User; 

	SET @database_id = IDENT_CURRENT('Databases');
COMMIT
END
END TRY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources