Sql Server error [SQLState 42000] (Error 325)

Question

I have a script that utilizes the new Merge Output clause. I've run it in 3 different instances (all non-Production environments) and it works great. When I tried running it in our production environment, I get the error:

Executed as user: xxx\xxx. Incorrect syntax near 'Merge'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. [SQLSTATE 42000] (Error 325) Incorrect syntax near 'Merge'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. [SQLSTATE 42000] (Error 325). The step failed.

I've checked the versions of each instance and they are all 10.0.4000.0. All of the non-system databases are set to compatibility level 90 (2005), and system databases are set to 100 (2008). What else do I need to check to see where my production instance is different from the other non-Production instances?

Here's the query:

Declare @user varchar(20),
        @message varchar(max)
Set     @user = 'ISS20120917-144'

Create Table #data
(
    CustomerEventID_Surrogate Int Identity (1,1) Not Null Primary Key,
    CustomerNumber Int Not Null,
    ConvictionEventID Int Not Null,
    CustomerEventHierarchyID Int Not Null,
    SanctionEventID Int Not Null,
    ReferenceNumber varchar(40) Null,
    ConvictionACDID Int Null,
    State_Code varchar(2) Not Null,
    County_ID Int Null,
    CitationCDLHolderValueID Int Null,
    Hazmat Bit Null,
    CMV Bit Null,
    PassengerEndorsement Bit Null,
    OccurrenceDate DateTime Not Null,
    ConvictionDate DateTime Not Null,
    CourtOrder Bit Null
)

Create Table #surrogatemap
(
    CustomerEventID_Surrogate Int Not Null,
    NewCustomerEventID Int Not Null
)

Create Table #surrogateHIDmap
(
    NewCustomerEventID Int Not Null,
    NewHistoryEventDetailID Int Not Null
)

Begin Tran

Begin Try
    Insert Into #data
    Select  ce.Cust_No,
            ce.CustomerEventID,
            ceh.CustomerEventHierarchyID,
            ceSAN.CustomerEventID,
            ce.ReferenceNumber,
            hed.ACDID,
            hed.State_Code,
            hed.County_ID,
            hed.CitationCDLHolderValueID,
            hed.Hazmat,
            hed.CMV,
            hed.PassengerEndorsement,
            hed.OccurrenceDate,
            Case    When cd.ConvictionDate IS NOT NULL Then cd.ConvictionDate
                    Else hed.OccurrenceDate
            End As [ConvictionDate],
            hed.CourtOrder
    From IADS..CustomerEvent ce
    Inner Join IADS..HistoryEventDetail hed On hed.CustomerEventID = ce.CustomerEventID
        And hed.EndDate IS NULL
    Inner Join IADS..CustomerEventCode cec On cec.CustomerEventCodeID = hed.CustomerEventCodeID
        And cec.CustomerEventCodeID <> -51
    Left Outer Join IADS..ConvictionDetail cd On cd.HistoryEventDetailID = hed.HistoryEventDetailID
    Inner Join IADS..CustomerEventHierarchy ceh On ceh.CustomerEventID = ce.CustomerEventID
        And ceh.EndDate IS NULL
    Inner Join IADS..CustomerEvent ceSAN On ceSAN.CustomerEventID = ceh.RelatedCustomerEventID
        And ceSAN.CustomerEventDispositionID IS NULL
    Inner Join IADS..CustomerSanctionDetail csd On csd.CustomerEventID = ceSAN.CustomerEventID
        And csd.SanctionDiscardedReasonID IS NULL
    Inner Join IADS..SanctionReasonCode src On src.SanctionReasonCodeID = csd.SanctionReasonCodeID
        And src.SanctionReasonCodeID = -320
    Where ce.CustomerEventDispositionID IS NULL

    Merge Into IADS..CustomerEvent
        Using #data As src On 1 = 0
    When Not Matched Then
    Insert
    (
        CustomerEventCategoryID,
        Cust_No,
        ReferenceNumber,
        CreatedBy,
        CreatedDate,
        UpdatedBy,
        UpdatedDate
    )
    Values
    (
        -2,
        src.CustomerNumber,
        src.ReferenceNumber,
        @user,
        GetDate(),
        @user,
        GetDate()
    )
    Output
        src.CustomerEventID_Surrogate,
        inserted.CustomerEventID
    Into #surrogatemap;

    Select  sm.NewCustomerEventID,
        -8 As [HistoryEventTypeID],
        -51 As [CustomerEventCodeID],
        131 As [ACDID],
        d.State_Code,
        d.County_ID,
        d.CitationCDLHolderValueID,
        d.OccurrenceDate,
        d.ConvictionDate,
        d.Hazmat,
        d.CMV,
        d.CourtOrder,
        GETDATE() As [EffectiveDate],
        @user As [UpdatedBy],
        GETDATE() As [UpdatedDate],
        d.ConvictionACDID,
        d.PassengerEndorsement
    Into    #hiddata
    From    #data d
    Inner Join #surrogatemap sm On sm.CustomerEventID_Surrogate = d.CustomerEventID_Surrogate

    Merge Into IADS..HistoryEventDetail
        Using #hiddata As src On 1 = 0
    When Not Matched Then
    Insert
    (
        CustomerEventID,
        HistoryEventTypeID,
        CustomerEventCodeID,
        ACDID,
        State_Code,
        County_ID,
        CitationCDLHolderValueID,
        OccurrenceDate,
        Hazmat,
        CMV,
        CourtOrder,
        EffectiveDate,
        UpdatedBy,
        UpdatedDate,
        UnderlyingACDID,
        PassengerEndorsement
    )
    Values
    (
        src.NewCustomerEventID,
        src.HistoryEventTypeID,
        src.CustomerEventCodeID,
        src.ACDID,
        src.State_Code,
        src.County_ID,
        src.CitationCDLHolderValueID,
        src.OccurrenceDate,
        src.Hazmat,
        src.CMV,
        src.CourtOrder,
        src.EffectiveDate,
        src.UpdatedBy,
        src.UpdatedDate,
        src.ConvictionACDID,
        src.PassengerEndorsement
    )
    Output
        src.NewCustomerEventID,
        inserted.HistoryEventDetailID
    Into #surrogateHIDmap;

    Insert Into IADS..CustomerEventHierarchy
    (
        CustomerEventID,
        RelatedCustomerEventID,
        EffectiveDate,
        UpdatedBy,
        UpdatedDate
    )
    Select  sm.NewCustomerEventID,
            d.SanctionEventID,
            GETDATE(),
            @user,
            GETDATE()
    From    #data d
    Inner Join #surrogatemap sm On sm.CustomerEventID_Surrogate = d.CustomerEventID_Surrogate

    Insert Into IADS..CourtFineDetail
    (
        HistoryEventDetailID,
        ConvictionDate
    )
    Select  s.NewHistoryEventDetailID,
            d.ConvictionDate
    From  #hiddata d
    Inner Join #surrogateHIDmap s On s.NewCustomerEventID = d.NewCustomerEventID

    -- Remove the tie to the SUS077
    Update IADS..CustomerEventHierarchy
    Set     EndDate = GETDATE(),
            UpdatedBy = @user,
            UpdatedDate = GETDATE()
    Where   CustomerEventHierarchyID In (Select CustomerEventHierarchyID From #data)

    -- Build temp table containing the records that have already purged
    Select  ce.Cust_No,
            ce.CustomerEventID,
            ceh.CustomerEventHierarchyID
    Into    #disposedRecords
    From    IADS..CustomerEvent ce
    Inner Join IADS..HistoryEventDetail hed On hed.CustomerEventID = ce.CustomerEventID
        And hed.EndDate IS NULL
    Inner Join IADS..CustomerEventCode cec On cec.CustomerEventCodeID = hed.CustomerEventCodeID
        And hed.CustomerEventCodeID <> -51
    Inner Join IADS..CustomerEventHierarchy ceh On ceh.CustomerEventID = ce.CustomerEventID
        And ceh.EndDate IS NULL
    Inner Join IADS..CustomerEvent ceSAN On ceSAN.CustomerEventID = ceh.RelatedCustomerEventID
        And ceSAN.CustomerEventDispositionID IS NOT NULL
    Inner Join IADS..CustomerSanctionDetail csd On csd.CustomerEventID = ceSAN.CustomerEventID
        And csd.SanctionReasonCodeID = -320
    Where   ce.CustomerEventDispositionID IS NOT NULL
    Order By ce.CustomerEventDispositionDate Desc

    -- Un-purge all of the records that were previously tied to a SUS077
    Update  IADS..CustomerEvent
    Set     CustomerEventDispositionID = Null,
            CustomerEventDispositionComment = Null,
            CustomerEventDispositionDate = Null,
            UpdatedBy = @user,
            UpdatedDate = GETDATE()
    Where   CustomerEventID In (Select CustomerEventID From #disposedRecords)

    -- Remove the records from the PURGEEventsReadyForPurge table
    Delete
    From IADS..PURGEEventsReadyForPurge
    Where CustomerEventID In (Select CustomerEventID From #disposedRecords)

    -- Remove tie of purged records
    Update  IADS..CustomerEventHierarchy
            Set EndDate = GETDATE(),
            UpdatedBy = @user,
            UpdatedDate = GETDATE()
    Where   CustomerEventHierarchyID In (Select CustomerEventHierarchyID From #disposedRecords)

    Delete From IADS..PURGEEventsReadyForPurge Where PURGEEventsReadyForPurgeID In
    (
        Select  PURGEEventsReadyForPurgeID
        From    IADS..PURGEEventsReadyForPurge p
        Inner Join IADS..CustomerEvent ce On ce.CustomerEventID = p.CustomerEventID
            And ce.CustomerEventDispositionID IS NULL
        Inner Join IADS..CustomerEventCategory ceg On ceg.CustomerEventCategoryID = ce.CustomerEventCategoryID
        Left Outer Join IADS..CustomerEventHierarchy ceh On ceh.CustomerEventID = ce.CustomerEventID
        Left Outer Join IADS..CustomerEventHierarchy ceh2 On ceh2.RelatedCustomerEventID = ce.CustomerEventID
        Where   p.PurgeDate IS NOT NULL
    )

    Drop Table #disposedRecords
    Drop Table #hiddata
    Drop Table #surrogateHIDmap
    Drop Table #surrogatemap
    Drop Table #data

    Commit
End Try
Begin Catch
    Drop Table #disposedRecords
    Drop Table #hiddata
    Drop Table #surrogateHIDmap
    Drop Table #surrogatemap
    Drop Table #data

    Rollback
End Catch

Answer 1

You can try any of these two things..
1. Update the compatiability level to 100.

ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100  

2. End the MERGE statement and the statement previous to MERGE with a semicolon (;)

Hope it works.