469,925 Members | 1,491 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,925 developers. It's quick & easy.

why do generated script begin with empty transactions ?

Greetings,

I am adding foreign keys to a database and saving the generated scripts.

What I do not understand is that all script begin with empty
transactions. Why ?

Example follows :
/*

vendredi 29 juillet 2005 10:54:36

User:

Server: (LOCAL)

Database: NewsPaper

Application: MS SQLEM - Data Tools

*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Article ADD CONSTRAINT
FK_Article_PublicationLevel FOREIGN KEY
(
PublicationLevelId
) REFERENCES dbo.PublicationLevel
(
PublicationLevelId
) ON UPDATE CASCADE
ON DELETE CASCADE

GO
ALTER TABLE dbo.Article ADD CONSTRAINT
FK_Article_UserInfo FOREIGN KEY
(
CreatorId
) REFERENCES dbo.UserInfo
(
UserId
) ON UPDATE CASCADE
ON DELETE CASCADE

GO
ROLLBACK
Jul 29 '05 #1
2 2392

"gabriel" <sp**@yahoo.fr> wrote in message
news:42**********************@news.free.fr...
Greetings,

I am adding foreign keys to a database and saving the generated scripts.

What I do not understand is that all script begin with empty transactions.
Why ?

Example follows :
/*

vendredi 29 juillet 2005 10:54:36

User:

Server: (LOCAL)

Database: NewsPaper

Application: MS SQLEM - Data Tools

*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Article ADD CONSTRAINT
FK_Article_PublicationLevel FOREIGN KEY
(
PublicationLevelId
) REFERENCES dbo.PublicationLevel
(
PublicationLevelId
) ON UPDATE CASCADE
ON DELETE CASCADE

GO
ALTER TABLE dbo.Article ADD CONSTRAINT
FK_Article_UserInfo FOREIGN KEY
(
CreatorId
) REFERENCES dbo.UserInfo
(
UserId
) ON UPDATE CASCADE
ON DELETE CASCADE

GO
ROLLBACK


It looks like you're using the Table Designer in EM? I have no idea why the
script includes those statements, but in general it's much better to make
DDL changes using a script in Query Analyzer - EM is useful for admin tasks,
but it's not very suitable for design and programming tasks (in fact, some
tasks cannot be done at all in EM). This article gives more details:

http://www.aspfaq.com/show.asp?id=2455

Simon
Jul 29 '05 #2
gabriel (sp**@yahoo.fr) writes:
I am adding foreign keys to a database and saving the generated scripts.

What I do not understand is that all script begin with empty
transactions. Why ?


Because the Table Designer is seriously buggy, and has number of
severe design flaws, and the empty transactions you see are a token
of these.

These empty transactions are harmless, however, say that you change a column
to a table that both references other tables, and are referred by other
tables. Now these transactions will no longer be empty, but will
comprise different parts of the table-update when it all should have
been one transaction.

Don't use the Table Designer to modify tables, use T-SQL instead. OK,
so you could generate scripts from the table designer, if you review
them *carefully*.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 29 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by David M Loraine | last post: by
3 posts views Thread by muzamil | last post: by
7 posts views Thread by | last post: by
2 posts views Thread by DVH | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.