Can "CREATE DATABASE" or "CREATE TABLE" be wrapped in transactions?

I have some code that dynamically creates a database (name is @.FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one of the following fails, the
database "creation" is rolledback. Otherwise, I would try deleting on error
detection, but it could get messy.

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE DATABASE ' + @.FullName
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
[int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]
NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
[PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> I have some code that dynamically creates a database (name is @.FullName)
and
> then creates a table within that database. Is it possible to wrap these
> things into a transaction such that if any one of the following fails, the
> database "creation" is rolledback. Otherwise, I would try deleting on
error
> detection, but it could get messy.
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
> [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
[datetime]
> NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END

CREATE DATABASE can't be inside a transaction; CREATE TABLE can be. Instead
of rolling back on an error, you could just drop the database and start
again, although if you have a lot of objects, that could be time-consuming.
One other possibility is to restore an empty database with all the objects
but no data, which would be a lot faster, but may not be suitable depending
on what exactly you're doing.

Simon|||That is exactly what I want to do I guess. So I need to look up "restore"?

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f6f48f2$1_2@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> > I have some code that dynamically creates a database (name is @.FullName)
> and
> > then creates a table within that database. Is it possible to wrap these
> > things into a transaction such that if any one of the following fails,
the
> > database "creation" is rolledback. Otherwise, I would try deleting on
> error
> > detection, but it could get messy.
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] (
[ID]
> > [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
> [datetime]
> > NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> > NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> > [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> CREATE DATABASE can't be inside a transaction; CREATE TABLE can be.
Instead
> of rolling back on an error, you could just drop the database and start
> again, although if you have a lot of objects, that could be
time-consuming.
> One other possibility is to restore an empty database with all the objects
> but no data, which would be a lot faster, but may not be suitable
depending
> on what exactly you're doing.
> Simon