CODEWITHSUNDEEP
sql-serversubquery
Wayne Ivory
Wayne Ivory

Reputation: 431

Subquery returns more than 1 value

I'm posting this more as a tip because I found the answer myself (with the help of a colleague).

We have a SQL Server 2012 system in the Production domain of our company (server P) that replicates records from a table (T) of the Main database (M) to another SQL Server 2012 system in the Business domain (server B, same database and table names).

We are in the process of installing a new ERP system in a separate database (E) on the Business server. The vendor asked that I populate data from table T into a table of the same name in their database as it arrives on the Business server, including data from a joined table (J) and a view (V). I wrote the following Trigger:

    CREATE TRIGGER [dbo].[After_Insert_T] ON  [dbo].[T] AFTER INSERT AS 
    BEGIN
      SET NOCOUNT ON;  -- Prevent extra result sets interfering with SELECT statements.
      SET XACT_ABORT OFF;  -- Avoid automatically rolling back current transaction

      BEGIN TRY
        INSERT E.dbo.T
        SELECT inserted.C1, inserted.C2, inserted.C3, J.C5, V.C7
        FROM inserted LEFT OUTER JOIN
             J ON inserted.C4 = J.C1 LEFT OUTER JOIN
             V ON inserted.C6 = V.C1 
             AND inserted.C3 BETWEEN J.C8 AND J.C9;
      END TRY

      BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_PROCEDURE() + ':  ' + ERROR_MESSAGE(), 
               @ErrorSeverity = ERROR_SEVERITY(), 
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState ,16,1) WITH LOG;
      END CATCH
    END

It all worked fine for a couple of days but then records were no longer being updated in the Business database and Event Viewer told me I was getting the error:

Error: 50000 Severity: 16 State: 1 After_Insert_T: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I could not for the life of me figure out how I could get a Subquery error out of the SQL I had written. I thought maybe the view V had a subquery but that was not the case. I searched online and saw a case where a stale record in the actual system replication tables was causing SQL Server to return duplicate records so I examined all the replication-based tables but could find nothing wrong.

And then came the answer...

Upvotes: 2

Views: 799

Answers (1)

Wayne Ivory
Wayne Ivory

Reputation: 431

The ERP vendor had written his own Trigger on the table that I was inserting into. His SQL had a subquery which in certain circumstances would return more than one row, however he had no TRY/CATCH so it fell back to the CATCH in my Trigger which logged the error. The only thing I was still puzzled about was that I thought the SET XACT_ABORT OFF statement was supposed to allow the original record to be written to the table despite the error, so I reread the help for it and saw this:

The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR.

My CATCH routine was pretty much copied from the Help for RAISERROR so I'd missed that point.

The lesson I learned out of this is, just because your code reports the problem don't take it for granted that you caused it!

Happy coding.

Wayne Ivory
Wespine Industries Pty Ltd

Upvotes: 1

Related Questions