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: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
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 @ 6:20 AM
Points: 13,293, Visits: 11,082
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, October 3, 2014 1:55 PM
Points: 199, Visits: 735
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: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
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 @ 4:27 AM
Points: 5,430, Visits: 10,097
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, October 3, 2014 1:55 PM
Points: 199, Visits: 735
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: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
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 @ 6:20 AM
Points: 13,293, Visits: 11,082
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