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

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

MORE INFORMATION

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.

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

Start SQL Query Analyzer.
Connect to the Pubs database on the computer that is running SQL Server.
Run the following Transact-SQL statement.
```
select * from discounts
```
Notice that the table contains three records.
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.
Run the following Transact-SQL statement.
```
select * from discounts
```
Notice that now the table contains no records.

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

SYMPTOMS

CAUSE

STATUS

RESOLUTION

MORE INFORMATION

Steps to reproduce the problem