470,815 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Create and then USE a dynamically-named database?

I have a need to create a database, and then populate it. However, the
code below doesn't work as I hoped it might (it creates the table in
the "master" database, which is Not A Good Thing). I know already
(thanks Tony!) that if you use Dynamic SQL for the USE command, then
the subsequent operations need to be Dynamic SQL as well, which is a
pity since there are over 11,000 lines of it and I don't really fancy
debugging it!

Does anyone have a cunning plan? In a nutshell, I would like to be
able to:

1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.

2. Create and populate tables in this new database.

These operations would ideally be running from a scheduled job.

Any thoughts?

TIA

Edward

====================================

USE MASTER

DECLARE @DBName VARCHAR(123)

SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)

if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)

exec('CREATE DATABASE ' + @DBName)
else

raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName)

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[TestID] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Nov 1 '06 #1
3 5622
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:

1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.

2. Create and populate tables in this new database.

Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.

Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@DBName). Anyway, here's how could've done it. Hope this helps:

USE MASTER
DECLARE @DBName VARCHAR(123)
SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)
exec('CREATE DATABASE ' + @DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +

'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
Regards,
Louis

Nov 2 '06 #2

lo***********@hotmail.com wrote:
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:

1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.

2. Create and populate tables in this new database.


Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.

Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@DBName). Anyway, here's how could've done it. Hope this helps:

USE MASTER
DECLARE @DBName VARCHAR(123)
SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)
exec('CREATE DATABASE ' + @DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +

'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.

Thanks

Edward

Nov 2 '06 #3
te********@hotmail.com wrote:
lo***********@hotmail.com wrote:
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
>
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
>
2. Create and populate tables in this new database.
>

Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.

Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@DBName). Anyway, here's how could've done it. Hope this helps:

USE MASTER
DECLARE @DBName VARCHAR(123)
SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)
exec('CREATE DATABASE ' + @DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +

'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)

Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.

Thanks

Edward
If this is always happening on a particular instance, could you break
your script down into 4 or 5 steps of an SQL job? Then, after creating
your new DB, you'd just sp_update_jobstep the steps to point to the new
database and then start it? (If multiple runs are possible, you'd also
need to have some way of locking until the job has finished. If always
started from the same DB, then an applock would work)

If the above doesn't help, then some more clues about whether we're
talking about 1, or n, or an unlimited number of instances and/or
databases (from which you kick this process off), or whether you're
kicking this process off through some other means - is it a stored
proc, and job, etc?

Damien

Nov 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Ray | last post: by
3 posts views Thread by RSB | last post: by
7 posts views Thread by pmclinn | last post: by
2 posts views Thread by Alexander Vasilevsky | last post: by
8 posts views Thread by =?Utf-8?B?U2hhd24=?= | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.