1

I am in a strange situation where I am not able to update rows in my View via SSMS. It gives me following error.

Error Source: sqlserver management datatools Error Message: the row value updated or deleted either do not make the row unique or they alter multiple rows

However, I am able to update them in in Visual Studio and also via TSQL Update statement. This looks like a bug in SQL Server Management Studio.

Any help/suggestions are appreciated.

View

    CREATE VIEW [ELog].[FileDroppingArea_2]
AS
       SELECT
                ea.SK_EmailAttachementsId,
                e.SK_EmailId,
                e.ServiceTypeCode,
                e.serviceprovidercode,
                pa.OrginalNameOfAttachment AS ParentAttachmentOriginalName,
                pa.NewNameOfAttachment AS ParentAttachmentName,
                e.FileNameModifier,
                ea.OrginalNameOfAttachment,
                --ea.NewFileName,
       (
              SELECT
                       ServiceTypeSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceTypeSplit
              WHERE ServiceTypeSplit.RowId = '1'
       ) AS ServiceTypeFromFile,
       (
              SELECT
                       ServiceProviderSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceProviderSplit
              WHERE ServiceProviderSplit.RowId = '2'
       ) AS ServiceProviderFromFile,
       (
              SELECT
                       SubmissionYearSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionYearSplit
              WHERE SubmissionYearSplit.RowId = '3'
       ) AS SubmissionYearFromFile,
       (
              SELECT
                       SubmissionMonthSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionMonthSplit
              WHERE SubmissionMonthSplit.RowId = '4'
       ) AS SubmissionMonthFromFile,
       (
              SELECT
                       CommissionerCodeSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') CommissionerCodeSplit
              WHERE CommissionerCodeSplit.RowId = '5'
       ) AS CommissionerCodeFromFile,
       (
              SELECT
                       FlexOrFreezeeSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') FlexOrFreezeeSplit
              WHERE FlexOrFreezeeSplit.RowId = '6'
       ) AS FlexOrFreezeeFromFile,
       (
              SELECT
                       VersionSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') VersionSplit
              WHERE VersionSplit.RowId = '7'
       ) AS VersionFromFile,
       (
              SELECT
                       ServiceSubTypeSplit.FieldValue
              FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceSubTypeSplit
              WHERE ServiceSubTypeSplit.RowId = '8'
       ) AS ServiceSubTypeFromFile,
                ea.CopyToServiceType
       FROM ELog.EmailAttachements ea
                INNER JOIN
                ELog.Emails e ON e.SK_EmailId = ea.SK_EmailId
                LEFT JOIN
                ELog.EmailAttachements pa ON ea.SK_ParentEmailAttachements = pa.SK_EmailAttachementsId
       WHERE ea.IsReadyForProcessing = 1
                 AND ea.IsMovedToRaw = 0

Instead of Update Trigger

CREATE TRIGGER [ELog].[TRG_upd_FileDropAreaByColumn] ON [ELog].[FileDroppingArea_2]
INSTEAD OF UPDATE
AS

       BEGIN


              DECLARE @SKEmailAttachmentId INT
              DECLARE @ServiceTypeFromFile VARCHAR(MAX)
              DECLARE @ServiceProviderFromFile VARCHAR(MAX)
              DECLARE @SubmissionYearFromFile VARCHAR(MAX)
              DECLARE @SubmissionMonthFromFile VARCHAR(MAX)
              DECLARE @CommissionerCodeFromFile VARCHAR(MAX)
              DECLARE @FlexOrFreezeeFromFile VARCHAR(MAX)
              DECLARE @VersionFromFile VARCHAR(MAX)
              DECLARE @ServiceSubTypeFromFile VARCHAR(MAX)
              DECLARE @CopyToServiceType VARCHAR(MAX)

              DECLARE @NewFileName VARCHAR(MAX)
              DECLARE @CalculatedVersion VARCHAR(MAX)
              DECLARE @CurrentFileName VARCHAR(MAX)
              DECLARE @ServiceType_Current VARCHAR(MAX)
              DECLARE @ServiceProvider_Current VARCHAR(MAX)
              DECLARE @SubmissionYear_Current VARCHAR(MAX)
              DECLARE @SubmissionMonth_Current VARCHAR(MAX)
              DECLARE @CommissionerCode_Current VARCHAR(MAX)
              DECLARE @FlexOrFreezee_Current VARCHAR(MAX)
              DECLARE @Version_Current VARCHAR(MAX)
              DECLARE @ServiceSubType_Current VARCHAR(MAX)



              DECLARE InsteadOfUpdate CURSOR LOCAL FAST_FORWARD
              FOR SELECT
                             I.SK_EmailAttachementsId,
                             I.ServiceTypeFromFile,
                             I.ServiceProviderFromFile,
                             I.SubmissionYearFromFile,
                             I.SubmissionMonthFromFile,
                             I.CommissionerCodeFromFile,
                             I.FlexOrFreezeeFromFile,
                             I.VersionFromFile,
                             I.ServiceSubTypeFromFile,
                             I.CopyToServiceType
                     FROM INSERTED I
                             INNER JOIN
                             DELETED D ON D.SK_EmailAttachementsId = I.SK_EmailAttachementsId
                     WHERE NOT(ISNULL(I.ServiceTypeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.ServiceTypeFromFile, '')
                     COLLATE latin1_general_cs_as
                                     AND ISNULL(I.ServiceProviderFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
                                     ServiceProviderFromFile, '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.SubmissionYearFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.SubmissionYearFromFile,
                                     '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.SubmissionMonthFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
                                     SubmissionMonthFromFile, '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.CommissionerCodeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
                                     CommissionerCodeFromFile, '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.FlexOrFreezeeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.FlexOrFreezeeFromFile,
                                     '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.VersionFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.VersionFromFile, '') COLLATE
                                     latin1_general_cs_as
                                     AND ISNULL(I.ServiceSubTypeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.ServiceSubTypeFromFile,
                                     '') COLLATE latin1_general_cs_as
                                     AND ISNULL(I.CopyToServiceType, '') COLLATE latin1_general_cs_as = ISNULL(D.CopyToServiceType, '')
                                     COLLATE latin1_general_cs_as)

              OPEN InsteadOfUpdate
              FETCH NEXT FROM InsteadOfUpdate INTO @SKEmailAttachmentId,
                                                                             @ServiceTypeFromFile,
                                                                             @ServiceProviderFromFile,
                                                                             @SubmissionYearFromFile,
                                                                             @SubmissionMonthFromFile,
                                                                             @CommissionerCodeFromFile,
                                                                             @FlexOrFreezeeFromFile,
                                                                             @VersionFromFile,
                                                                             @ServiceSubTypeFromFile,
                                                                             @CopyToServiceType


              WHILE @@FETCH_STATUS = 0
                     BEGIN


                           SET @CurrentFileName =
                           (
                                  SELECT
                                           ea.NewFileName
                                  FROM ELog.EmailAttachements ea
                                  WHERE ea.SK_EmailAttachementsId = @SKEmailAttachmentId
                           )


                           SELECT
                                    @ServiceType_Current = ServiceTypeSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceTypeSplit
                           WHERE ServiceTypeSplit.RowId = '1'

                           SELECT
                                    @ServiceProvider_Current = ServiceProviderSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceProviderSplit
                           WHERE ServiceProviderSplit.RowId = '2'

                           SELECT
                                    @SubmissionYear_Current = SubmissionYearSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') SubmissionYearSplit
                           WHERE SubmissionYearSplit.RowId = '3'

                           SELECT
                                    @SubmissionMonth_Current = SubmissionMonthSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') SubmissionMonthSplit
                           WHERE SubmissionMonthSplit.RowId = '4'

                           SELECT
                                    @CommissionerCode_Current = CommissionerCodeSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') CommissionerCodeSplit
                           WHERE CommissionerCodeSplit.RowId = '5'

                           SELECT
                                    @FlexOrFreezee_Current = FlexOrFreezeeSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') FlexOrFreezeeSplit
                           WHERE FlexOrFreezeeSplit.RowId = '6'

                           SELECT
                                    @Version_Current = VersionSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') VersionSplit
                           WHERE VersionSplit.RowId = '7'

                           SELECT
                                    @ServiceSubType_Current = ServiceSubTypeSplit.FieldValue
                           FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceSubTypeSplit
                           WHERE ServiceSubTypeSplit.RowId = '8';
                           WITH CurrentVersion
                                    AS (SELECT
                                           (
                                                  SELECT
                                                          VersionSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') VersionSplit
                                                  WHERE VersionSplit.RowId = '7'
                                           ) StoredVersion
                                           FROM ELog.EmailAttachements ea
                                           WHERE ea.SK_EmailAttachementsId <> @SKEmailAttachmentId
                                                       AND @ServiceTypeFromFile =
                                           (
                                                  SELECT
                                                          ServiceTypeSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceTypeSplit
                                                  WHERE ServiceTypeSplit.RowId = '1'
                                           )
                                    AND @ServiceProviderFromFile =
                                           (
                                                  SELECT
                                                          ServiceProviderSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceProviderSplit
                                                  WHERE ServiceProviderSplit.RowId = '2'
                                           )
                           AND @SubmissionYearFromFile =
                                           (
                                                  SELECT
                                                          SubmissionYearSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionYearSplit
                                                  WHERE SubmissionYearSplit.RowId = '3'
                                           )
              AND @SubmissionMonthFromFile =
                                           (
                                                  SELECT
                                                          SubmissionMonthSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionMonthSplit
                                                  WHERE SubmissionMonthSplit.RowId = '4'
                                           )
              AND @CommissionerCodeFromFile =
                                           (
                                                  SELECT
                                                          CommissionerCodeSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') CommissionerCodeSplit
                                                  WHERE CommissionerCodeSplit.RowId = '5'
                                           )
              AND @FlexOrFreezeeFromFile =
                                           (
                                                  SELECT
                                                          FlexOrFreezeeSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') FlexOrFreezeeSplit
                                                  WHERE FlexOrFreezeeSplit.RowId = '6'
                                           )
              AND @ServiceSubTypeFromFile =
                                           (
                                                  SELECT
                                                          ServiceSubTypeSplit.FieldValue
                                                  FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceSubTypeSplit
                                                  WHERE ServiceSubTypeSplit.RowId = '8'
                                           ))
                                    SELECT
                                            @CalculatedVersion = ISNULL(
                                                                                           (
                                                                                                  SELECT
                                                                                                          'V'+RIGHT('00'+CAST(MAX(REPLACE(StoredVersion, 'V', ''))+1 AS
                                                                                                          VARCHAR(10)), 2)
                                                                                           ), 'V01')
                                    FROM CurrentVersion


                           SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceType_Current, @ServiceTypeFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceProvider_Current, @ServiceProviderFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @SubmissionYear_Current, @SubmissionYearFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @SubmissionMonth_Current, @SubmissionMonthFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @CommissionerCode_Current, @CommissionerCodeFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @FlexOrFreezee_Current, @FlexOrFreezeeFromFile)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @Version_Current, @CalculatedVersion)
                           SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceSubType_Current, @ServiceSubTypeFromFile)

                           SET @NewFileName = @CurrentFileName





                           UPDATE EA
                              SET
                                     EA.NewFileName = @CurrentFileName,
                                     EA.CopyToServiceType = @CopyToServiceType,
                                     EA.LastUpdatedTimestamp = GETDATE(),
                                     EA.ModifiedBYUser = SUSER_SNAME()
                           FROM ELog.EmailAttachements EA
                           WHERE
                                    IsReadyForProcessing = 1
                                    AND IsMovedToRaw = 0
                                    AND EA.SK_EmailAttachementsId = @SKEmailAttachmentId
                                    AND NOT(ISNULL(EA.NewFileName, '') COLLATE latin1_general_cs_as = ISNULL(@CurrentFileName, '') COLLATE
                                    latin1_general_cs_as
                                                  AND ISNULL(EA.CopyToServiceType, '') COLLATE latin1_general_cs_as = ISNULL(@CopyToServiceType, '')
                                                  COLLATE latin1_general_cs_as)



                           FETCH NEXT FROM InsteadOfUpdate INTO @SKEmailAttachmentId,
                                                                                           @ServiceTypeFromFile,
                                                                                           @ServiceProviderFromFile,
                                                                                           @SubmissionYearFromFile,
                                                                                           @SubmissionMonthFromFile,
                                                                                           @CommissionerCodeFromFile,
                                                                                           @FlexOrFreezeeFromFile,
                                                                                           @VersionFromFile,
                                                                                           @ServiceSubTypeFromFile,
                                                                                           @CopyToServiceType
                     END
       END


GO

P.S. Earlier I was updating newFileName column directly from view, since I split the column into 8 different columns, these 8 new columns are appearing as disabled in Visual Studio

1 Answer 1

2

It's not a bug, you should use T-SQL statement as you did and not a GUI. When your update/delete statement affects more than one identical rows (duplicates) you've got this kind of error ("or they alter multiple rows"), that's because in GUI you edit one row at a time, but GUI have to produce a T-SQL statement corresponding to your action and it fails, because the statement produced cannot distinguish between duplicates, which one of them you wanted to update/delete.

7
  • 1)SKEmailAtttachment Id is unique in my base table and also unique in my View 2) Ran tracer on the server to see what update statement was being executed, copied the statement and ran it, update worked successfully. 3) My users don't know TSQL thats why they will be using data editor, they need something quick, TSQL isn't a userfriendly option 4) How come it works with Visual Studio Editor?
    – mouliin
    May 11, 2017 at 15:03
  • I tried both the possibilities to get this error just now. In case of multiple rows update/delete the statement is not generated at all, but the error specifies in parentheses how many rows you tried to modify. In case your update makes the row not unique the statement is generated but the error precises what duplicate value did you specified
    – sepupic
    May 11, 2017 at 15:38
  • Sorry, only the first case described in my previous post (multiple rows) produce this error, the second case generated PK violation, so I cannot reproduce the case where the update render the row non unique and causes the same error, but I think the behavior should be the same: no statement generated, only the error, that's why the error you've got is not a server error but the client(SSMS) error; so I doubt you could catch the statment by profiler: the statement could be generated at all
    – sepupic
    May 11, 2017 at 16:00
  • Just for testing, I deleted all the rows from my email attachment table except one and I still get the error regarding 2 rows being updated
    – mouliin
    May 12, 2017 at 7:33
  • Microsoft SQL Server 2014 Management Studio Version 12.0.5203.0 .Net Framework 4.0.30319.42000
    – mouliin
    May 16, 2017 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.