Microsoft KB Archive/890883

= BUG: Potential data loss can occur when you manually run a change script that is generated by Visual Data Tools in Visual Studio .NET =

Article ID: 890883

Article Last Modified on 9/15/2005

-

APPLIES TO


 * Microsoft Visual Studio .NET 2002 Professional Edition
 * Microsoft Visual Studio .NET 2002 Enterprise Developer
 * Microsoft Visual Studio .NET 2002 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Professional Edition
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Enterprise Architect

-





SYMPTOMS
Potential data loss can occur when you manually run a change script that is generated by Visual Data Tools (VDT). You can potentially experience data loss in the Microsoft SQL Server database. This problem occurs in Microsoft Visual Studio .NET.



CAUSE
The change script that is generated by VDT does not include logic to stop execution when errors occur in the change script.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



RESOLUTION
Modify the change script so that it contains an error checking mechanism. You can use the following code example. Alternatively, you can fix the problem in the change script yourself. 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 BEGIN DECLARE @ErrMsg varchar(1000), @TempTableName sysname BEGIN TRANSACTION

-- Failure ends the batch and rolls back. No error test is required. ALTER TABLE dbo.discounts DROP CONSTRAINT FK__discounts__stor___0F975522

-- Make sure that this table name is unique and is unused. IF OBJECTPROPERTY(OBJECT_ID('dbo.Tmp_Discounts'), 'IsTable') = 1 DROP TABLE dbo.Tmp_Discounts

CREATE TABLE dbo.Tmp_discounts (   discounttype varchar(40) NOT NULL,    stor_id char(4) NOT NULL,    lowqty smallint NULL,    highqty smallint NULL,    discount decimal(4, 2) NOT NULL    )  ON [PRIMARY] -- If there is any data, migrate it. IF EXISTS(SELECT * FROM dbo.discounts) -- Receives range locks and holds on the whole table (serializable). BEGIN DECLARE @ExecError int, @SQLcmd nvarchar(4000) SET @SQLcmd = N'INSERT INTO dbo.Tmp_discounts (discounttype, stor_id, lowqty, highqty, discount) SELECT discounttype, stor_id, lowqty, highqty, discount FROM dbo.discounts WITH (TABLOCKX)' EXEC sp_executesql @SQLcmd IF @@ERROR <> 0 BEGIN SET @ErrMsg = 'Insert into dbo.Tmp_discounts failed.' GOTO QuitWithRollback END IF (SELECT COUNT(*) FROM Tmp_discounts) <> (SELECT COUNT(*) FROM discounts) BEGIN SET @ErrMsg = 'Counts between dbo.Tmp_discounts and dbo.discounts disagree.' GOTO QuitWithRollback END END IF OBJECTPROPERTY(OBJECT_ID('dbo.discounts'), 'IsTable') = 1 DROP TABLE dbo.discounts EXECUTE sp_rename N'dbo.Tmp_discounts', N'discounts', 'OBJECT' IF @@ERROR <> 0 BEGIN SET @ErrMsg = 'Rename of dbo.Tmp_discounts failed.' GOTO QuitWithRollback END -- Failure ends the batch and rolls back the transaction. No error test is required. ALTER TABLE dbo.discounts ADD CONSTRAINT FK__discounts__stor___0F975522 FOREIGN KEY (stor_id) REFERENCES dbo.stores(stor_id)

COMMIT TRANSACTION GOTO EndScript QuitWithRollback: RAISERROR (@ErrMsg, 10, 1) ROLLBACK TRANSACTION EndScript: END



Steps to reproduce the problem
Note The following steps make changes to the Discounts table in the Pubs database. We recommend that you make a backup copy of the Discounts table before you start.  Start Visual Studio. NET 2003. Create a data connection to connect to the Pubs database in Server Explorer. On the Tools menu, click Options. In the Options dialog box, click Database Tools, and then click Database Designer.

By default, the General node is selected. Make sure that Auto generate change scripts is enabled. Expand your database connection node in Server Explorer to view the tables, right-click the discounts node, and then click Design Table. In the Discounts Table Design window, click to clear the Allow Nulls cell in the stor_id column.</li>  On the toolbar, click Generate Change Script.

You can obtain the following change script from the Save Change Script window. BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON 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 ALTER TABLE dbo.discounts DROP CONSTRAINT FK__discounts__stor___0F975522 GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_discounts (   discounttype varchar(40) NOT NULL,    stor_id char(4) NOT NULL,    lowqty smallint NULL,    highqty smallint NULL,    discount numeric(4, 2) NOT NULL    )  ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.discounts) EXEC('INSERT INTO dbo.Tmp_discounts (discounttype, stor_id, lowqty, highqty, discount)       SELECT discounttype, stor_id, lowqty, highqty, discount FROM dbo.discounts (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.discounts GO EXECUTE sp_rename N'dbo.Tmp_discounts', N'discounts', 'OBJECT' GO ALTER TABLE dbo.discounts WITH NOCHECK ADD CONSTRAINT FK__discounts__stor___0F975522 FOREIGN KEY (   stor_id    ) REFERENCES dbo.stores (   stor_id    ) GO COMMIT </li> Start SQL Query Analyzer.</li> Connect to the Pubs database on the computer that is running SQL Server.</li>  Run the following Transact-SQL statement. select * from discounts Notice that the table contains three records. </li> Run the previous Transact-SQL statements in steps 6 in SQL Query Analyzer.

You receive the following error message:

Server: Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'stor_id', table 'pubs.dbo.Tmp_discounts'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Caution: Changing any part of an object name could break scripts and stored procedures.

The OBJECT was renamed to 'discounts'.

However, code execution does not stop.</li>  Run the following Transact-SQL statement. select * from discounts Notice that now the table contains no records. </li></ol>

Keywords: kbtshoot kbbug kbserver kbdatabase kbvs2002sp1sweep KB890883

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.