Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create database and schema dynamically Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 12:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
I want to make a Stored Procedure to create a database with a dynamic name.
In that SP I have to create a new schema in that new database as well.

create proc CREATE_LOAD_DB @load_year int, @load_month int
as
declare @dbname sysname
declare @sql nvarchar(max)

set @dbname = 'DB_' + CAST(@load_year as varchar(4)) + CAST(@load_month as varchar(2))
set @sql = 'create database ' + QUOTENAME(@dbname)
exec (@sql)

How to create a schema in this new database within the SP?

thanks,

Robbert



Post #1389532
Posted Wednesday, November 28, 2012 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 13,268, Visits: 10,144
I don't think you can.
You can't use the "USE database" statement in a stored procedure and you cannot qualify the schema with a database name in the CREATE SCHEMA statement.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1389538
Posted Wednesday, November 28, 2012 1:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 194, Visits: 717
It should work if you include the USE-Statement in the dynamic SQL-String for creating the schema.

set @sql = 'use ' + QUOTENAME(@dbname) + ' create schema MyNewSchema';
exec ( @sql )

Post #1389553
Posted Wednesday, November 28, 2012 1:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
I tried that, giving this error:

Msg 111, Level 15, State 1, Line 1
'CREATE SCHEMA' must be the first statement in a query batch.

A 'GO' in between the USE and CREATE is also not working.



Post #1389558
Posted Wednesday, November 28, 2012 1:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 5,230, Visits: 9,456
I don't know if this would work for you, but you could create the schema in the model database. Then it would appear automatically in all new databases.

John
Post #1389565
Posted Wednesday, November 28, 2012 1:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 194, Visits: 717
this one works:
set @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''create schema MyNewSchema'''
exec (@sql)
Post #1389568
Posted Wednesday, November 28, 2012 1:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
weberharter you are great!!, it WORKS.

Thanks.



Post #1389571
Posted Wednesday, November 28, 2012 1:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 13,268, Visits: 10,144
weberharter (11/28/2012)
this one works:
set @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''create schema MyNewSchema'''
exec (@sql)


Congrats for proving me wrong




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1389574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse