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

[Robin Tucker]

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

[Simon Hayes]

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:bk*******************@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

[Robin Tucker]

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

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:bk*******************@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