Does anyone know a fix for Data loss in visual studio ?

[sock1992]

I'm attempting to publish my database but I'm getting the errors below.

Ive unchecked the "block incremental deployment if data loss might occur" option which usually works but for some reason this time it isn't. Does anyone know a fix for this??

Rename refactoring operation with key e843b923-a383-40ef-bd7e-4db4cce209dc is skipped, element [dbo].[bankCard].[Id] (SqlSimpleColumn) will not be renamed to bankCardId
Rename refactoring operation with key 228788af-5c77-4f95-ae9a-7382c3c179b8 is skipped, element [dbo].[bankCard].[Id] (SqlSimpleColumn) will not be renamed to bankCardId
Starting rebuilding table [dbo].[Bookings]...
(85,1): SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 21 Conversion failed when converting the nvarchar value ' A2 ' to data type int.
(65,0): SQL72045: Script execution error.  The executed script:
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Bookings] (
    [bookingId]     INT           NOT NULL,
    [departingFrom] NVARCHAR (50) NULL,
    [destination]   NVARCHAR (50) NULL,
    [dateOfJourney] DATE          NULL,
    [seatNo]        INT           NULL,
    [ticketType]    NVARCHAR (50) NULL,
    [price]         INT           NULL,
    PRIMARY KEY CLUSTERED ([bookingId] ASC)
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[Bookings])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_Bookings] ([bookingId], [departingFrom], [destination], [dateOfJourney], [seatNo], [ticketType], [price])
        SELECT   [bookingId],
                 [departingFrom],
                 [destination],
                 [dateOfJourney],
                 [seatNo],
                 [ticketType],
                 [price]
        FROM     [dbo].[Bookings]
        ORDER BY [bookingId] ASC;
    END

DROP TABLE [dbo].[Bookings
An error occurred while the batch was being executed.

** Highlights
     Tables that will be rebuilt
       [dbo].[Bookings]
       [dbo].[Payment]
     Clustered indexes that will be dropped
       None
     Clustered indexes that will be created
       None
     Possible data issues
       The column [dbo].[Bookings].[customerId] is being dropped, data loss could occur.
       The column [dbo].[Bookings].[scheduleId] is being dropped, data loss could occur.
       The type for column price in table [dbo].[Bookings] is currently  FLOAT (53) NULL but is being changed to  INT NULL.
         Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.
       The type for column seatNo in table [dbo].[Bookings] is currently  NVARCHAR (50) NULL but is being changed to  INT NULL.
         Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.
       The column [dbo].[Payment].[bankCardId] is being dropped, data loss could occur.
       The type for column Amount in table [dbo].[Payment] is currently  FLOAT (53) NULL but is being changed to  INT NULL.
         Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.

** User actions
     Table rebuild
       [dbo].[Bookings] (Table)
       [dbo].[Payment] (Table)
     Create
       [dbo].[bankCard] (Table)
       [dbo].[FK_bankCard_Customer] (Foreign Key)

** Supporting actions

The column [dbo].[Bookings].[customerId] is being dropped, data loss could occur.
The column [dbo].[Bookings].[scheduleId] is being dropped, data loss could occur.
The type for column price in table [dbo].[Bookings] is currently  FLOAT (53) NULL but is being changed to  INT NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.
The type for column seatNo in table [dbo].[Bookings] is currently  NVARCHAR (50) NULL but is being changed to  INT NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.
The column [dbo].[Payment].[bankCardId] is being dropped, data loss could occur.
The type for column Amount in table [dbo].[Payment] is currently  FLOAT (53) NULL but is being changed to  INT NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type  INT NULL.

 

[Skydiver]

I don't do SQL, but my gut feel is that NVARCHAR(50) to INT conversion is something that could fail spectacularly. What if the field contains alphanumeric values? How will the conversion work? What if the field contains all alphabetic characters? What if the field contains all digits ... for all 50 characters? I don't think it's possible to store a 50 digit number into a 32-bit integer.

 

[Skydiver]

And now that I'm not reading this message on a small phone, line 4 tells the story:

(85,1): SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 21 Conversion failed when converting the nvarchar value ' A2 ' to data type int.

 

[sock1992]

And now that I'm not reading this message on a small phone, line 4 tells the story:

(85,1): SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 21 Conversion failed when converting the nvarchar value ' A2 ' to data type int.

I saw that, and I changed the data type to varchar, char and nchar but it made no difference. Now, I've just changed the seat data type to int and altered A2 to a number and it works.