why do generated script begin with empty transactions ?

[gabriel]

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_IDENTIFI ER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDAB ORT OFF
SET CONCAT_NULL_YIE LDS_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_Publ icationLevel FOREIGN KEY
(
PublicationLeve lId
) REFERENCES dbo.Publication Level
(
PublicationLeve lId
) ON UPDATE CASCADE
ON DELETE CASCADE

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

GO
ROLLBACK

[Simon Hayes]

"gabriel" <sp**@yahoo.f r> 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_IDENTIFI ER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDAB ORT OFF
SET CONCAT_NULL_YIE LDS_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_Publ icationLevel FOREIGN KEY
(
PublicationLeve lId
) REFERENCES dbo.Publication Level
(
PublicationLeve lId
) ON UPDATE CASCADE
ON DELETE CASCADE

GO
ALTER TABLE dbo.Article ADD CONSTRAINT
FK_Article_User Info 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

[Erland Sommarskog]

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp