May 24, 2012 at 9:05 am
Hello Everyone
I would like to know why a DDL cannot be a part of a transaction.
Regards,
Nawaz.
May 24, 2012 at 9:12 am
Not sure what you mean.
DDL can be part of a transaction:
BEGIN TRAN
CREATE TABLE test (someColumn int)
ROLLBACK
SELECT *
FROM test
Msg 208, Level 16, State 1, Line 7
Invalid object name 'test'.
-- Gianluca Sartori
May 24, 2012 at 9:19 am
Oh yes It can be. I am sorry.
But I would like to know why a 'CREATE DATABASE' Statement cannot be a part of Transaction. At the same time what are other statement that cannot be a part of Tran
Regards,
Nawaz.
May 24, 2012 at 12:51 pm
na1774 (5/24/2012)
Oh yes It can be. I am sorry.But I would like to know why a 'CREATE DATABASE' Statement cannot be a part of Transaction. At the same time what are other statement that cannot be a part of Tran
Regards,
Nawaz.
From the MSDN page. http://msdn.microsoft.com/en-us/library/ms176061.aspx
The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.
Makes sense if you think about it. This is more than just changing some data or a table. There are a lot of things that happen when you create a database (sys entries, file system objects created, etc).
And to be honest, I can' think of anything from a practical side where creating a database in a transaction would be useful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply