Posted by teddysnips on 11/02/06 10:48

louisyoung187@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

[Back to original message]