By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.