Post reply

Trigger woes

  • March 27, 2018 at 5:54 am

    #403211

    Hi all

    We've got an application that inserts information into an SQL table (table A).
    The trigger on table A then either copies this data to table B or updates table B with the info from table A.
    The above is dependant on an "Action" field in table A that can be either "INSERT" or "UPDATE".

    Now for the odd bit.......
    The trigger work when inserting data, but fails when trying to update a record with exactly the same data.
    The table definitions are as follows:-
    Table A
    CREATE TABLE [dbo].[tbl_SharePoint_Data_Entry](
        [pkID] [INT] IDENTITY(1,1) NOT NULL,
        [KPIID] [INT] NOT NULL,
        [PeriodEndDate] [DATE] NOT NULL,
        [Location] [VARCHAR](45) NOT NULL,
        [ConcatPrimaryKey] AS ((CONVERT([VARCHAR](7),[KPIID])+CONVERT([VARCHAR](8),[PeriodEndDate],(112)))+[Location]) PERSISTED,
        [Numerator] [REAL] NULL,
        [Denominator] [REAL] NULL,
        [Result] [REAL] NULL,
        [Submitter] [VARCHAR](100) NULL,
        [SubmittedDateTime] [DATETIME] NULL,
        [Authoriser] [VARCHAR](100) NULL,
        [Status] [VARCHAR](25) NULL,
        [StatusDateTime] [DATETIME] NULL,
        [Action] [VARCHAR](6) NULL,
    CONSTRAINT [PK__tbl_Shar__40A359E36F7C2C12] PRIMARY KEY CLUSTERED
    (
        [pkID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Table B
    CREATE TABLE [dbo].[tbl_Results](
        [pkKPIID] [INT] NOT NULL,
        [pkPeriodEndDate] [DATE] NOT NULL,
        [pkLocation] [VARCHAR](45) NOT NULL,
        [ConcatPrimaryKey] AS ((CONVERT([VARCHAR](7),[pkKPIID])+CONVERT([VARCHAR](8),[pkPeriodEndDate],(112)))+[pkLocation]) PERSISTED,
        [Numerator] [REAL] NULL,
        [Denominator] [REAL] NULL,
        [Result] [REAL] NULL,
        [Submitter] [VARCHAR](100) NULL,
        [SubmittedDateTime] [DATETIME] NULL,
        [Authoriser] [VARCHAR](100) NULL,
        [Status] [VARCHAR](25) NULL,
        [StatusDateTime] [DATETIME] NULL,
        [SYSDateLastUpdated] [DATETIME] NULL,
        [SYSDateLoaded] [DATETIME] NULL,
        [SYSStartDateTime] [DATETIME] NOT NULL,
        [SYSEndDateTime] [DATETIME] NOT NULL,
    CONSTRAINT [PK_tbl_Results_1] PRIMARY KEY CLUSTERED
    (
        [pkKPIID] ASC,
        [pkPeriodEndDate] ASC,
        [pkLocation] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF__tbl_Resul__SYSDa__3C34F16F] DEFAULT (GETDATE()) FOR [SYSDateLastUpdated]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF__tbl_Resul__SYSDa__3D2915A8] DEFAULT (GETDATE()) FOR [SYSDateLoaded]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF_tbl_Results_SYSStartDateTime] DEFAULT (GETDATE()) FOR [SYSStartDateTime]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF_tbl_Results_SYSEndDateTime] DEFAULT ('20991231') FOR [SYSEndDateTime]
    GO

    For example, this code works:-
    INSERT INTO [dbo].[tbl_SharePoint_Data_Entry]
        ([KPIID]
        ,[PeriodEndDate]
        ,[Location]
        --,[Numerator]
        --,[Denominator]
        --,[Result]
        --,[Submitter]
        --,[SubmittedDateTime]
        --,[Authoriser]
        --,[Status]
        --,[StatusDateTime]
        ,[Action]
             )
      VALUES
        (1580
        ,'20181231'
        ,'A3'
        --,10
        --,10
        --,101
        --,'1'
        --,GETDATE()
        --,'1'
        --,'A'
        --,GETDATE()
        ,'INSERT'
             )
    GO

    The trigger fires and everything is good.

    If I then run this:-
    INSERT INTO [dbo].[tbl_SharePoint_Data_Entry]
        ([KPIID]
        ,[PeriodEndDate]
        ,[Location]
        --,[Numerator]
        --,[Denominator]
        --,[Result]
        --,[Submitter]
        --,[SubmittedDateTime]
        --,[Authoriser]
        --,[Status]
        --,[StatusDateTime]
        ,[Action]
             )
      VALUES
        (1580
        ,'20181231'
        ,'A3'
        --,10
        --,10
        --,101
        --,'1'
        --,GETDATE()
        --,'1'
        --,'A'
        --,GETDATE()
        ,'UPDATE'
             )
    GO

    it fails with the following error:-
    Msg 8152, Level 16, State 14, Procedure trg_tbl_Results_Update_SYSDateLastUpdated, Line 17 [Batch Start Line 2]
    String or binary data would be truncated.

    The trigger code is here and looks fine:-
    CREATE TRIGGER [dbo].[tr_tbl_SharePoint_Data_Entry]
    ON [dbo].[tbl_SharePoint_Data_Entry]
    AFTER INSERT, UPDATE
    AS
      BEGIN
         INSERT INTO [KPIData].dbo.tbl_Results
             (
                 [pkKPIID]
                ,[pkPeriodEndDate]
                ,[pkLocation]
                ,[Numerator]
                ,[Denominator]
                ,[Result]
                ,[Submitter]
                ,[SubmittedDateTime]
                ,[Authoriser]
                ,[Status]
                ,[StatusDateTime]
             )
         SELECT
                KPIID
              ,PeriodEndDate
              ,[Location]
              ,Numerator
              ,Denominator
              ,Result
              ,Submitter
              ,SubmittedDateTime
              ,Authoriser
              ,[Status]
              ,StatusDateTime
         FROM
                inserted
         WHERE
                [Action] = 'INSERT';

         UPDATE
                R
         SET
                R.Numerator = I.Numerator
              ,R.Denominator = I.Denominator
              ,R.Result = I.Result
              ,R.Submitter = I.Submitter
              ,R.SubmittedDateTime = I.SubmittedDateTime
              ,R.Authoriser = I.Authoriser
              ,R.[Status] = I.[Status]
              ,R.StatusDateTime = I.StatusDateTime
         FROM
                [KPIData].dbo.tbl_Results AS R
             INNER JOIN
                 inserted AS I
                    ON R.pkKPIID = I.KPIID
                      AND    R.[pkPeriodEndDate] = I.PeriodEndDate
                      AND    R.pkLocation = I.[Location]
         WHERE
                I.[Action] = 'UPDATE';

      END;

    If I swap the literal dates in the INSERT statement for GETDATE() (as in the other two places) it works.

    Anyone any ideas?

  • March 27, 2018 at 6:39 am

    #1984640

    One thing you are doing that might cause trouble is the CONVERT(varchar(7), [pkKPIID]) portion, which will fail on any value of the pkKPIID column that is greater than 9,999,999 or less than -999,999.  As the value range for integers goes from roughly negative 2 billion to positive 2 billion, you need varchar(12), not varchar(7) to ensure you never have that kind of problem.   Not sure if that's what is happening, but over time, that could bite you big time.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 27, 2018 at 6:54 am

    #1984645

    Thanks for the heads up, I'll see what I can do to get that changed (unfortunately, it's not our system).

    It would also appear to be an intermittent problem as inserting :-

    KPIID1590
    PeriodEndDate31/12/2018
    LocationA20
    Numerator10
    Denominator10
    Result150
    Submitterrdgh\greenri
    SubmittedDateTime27/03/2018 13:32
    Authoriserrdgh\greenri
    StatusA
    StatusDateTime27/03/2018 13:32
    ActionUPDATE

    (apologies for the dodgy formatting) works as both an INSERT and an UPDATE (as you can see).

  • March 27, 2018 at 10:20 am

    #1984677

    richardmgreen1 - Tuesday, March 27, 2018 6:54 AM

    Thanks for the heads up, I'll see what I can do to get that changed (unfortunately, it's not our system).

    It would also appear to be an intermittent problem as inserting :-

    KPIID1590
    PeriodEndDate31/12/2018
    LocationA20
    Numerator10
    Denominator10
    Result150
    Submitterrdgh\greenri
    SubmittedDateTime27/03/2018 13:32
    Authoriserrdgh\greenri
    StatusA
    StatusDateTime27/03/2018 13:32
    ActionUPDATE

    (apologies for the dodgy formatting) works as both an INSERT and an UPDATE (as you can see).

    I would just go ahead and SELECT MIN(KPIID), MAX(KPIID) FROM [dbo].[tbl_SharePoint_Data_Entry] and see if you have any integer values in the range I referred to, and at least that way, you'll know if perhaps you already have that problem.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 28, 2018 at 2:51 am

    #1984745

    Thanks for that.
    I've just checked and I've got a min of 1522 and a max of 1591.

    I can insert the exact same data into each table without an issue, it's just the trigger that throws a hissy-fit.

    I've managed to narrow it down to the PeriodEndDate field.

    If I use a literal date (i.e. "20181231") it fails.
    If I use the getdate() function, all is fine (with or without the time portion included).
    If I then use the same literal date as above, all works as it should.

    I've checked the database and the language is set to British English.

  • March 28, 2018 at 3:12 am

    #1984746

    richardmgreen1 - Tuesday, March 27, 2018 5:54 AM

    Hi all

    We've got an application that inserts information into an SQL table (table A).
    The trigger on table A then either copies this data to table B or updates table B with the info from table A.
    The above is dependant on an "Action" field in table A that can be either "INSERT" or "UPDATE".

    Now for the odd bit.......
    The trigger work when inserting data, but fails when trying to update a record with exactly the same data.
    The table definitions are as follows:-
    Table A
    CREATE TABLE [dbo].[tbl_SharePoint_Data_Entry](
        [pkID] [INT] IDENTITY(1,1) NOT NULL,
        [KPIID] [INT] NOT NULL,
        [PeriodEndDate] [DATE] NOT NULL,
        [Location] [VARCHAR](45) NOT NULL,
        [ConcatPrimaryKey] AS ((CONVERT([VARCHAR](7),[KPIID])+CONVERT([VARCHAR](8),[PeriodEndDate],(112)))+[Location]) PERSISTED,
        [Numerator] [REAL] NULL,
        [Denominator] [REAL] NULL,
        [Result] [REAL] NULL,
        [Submitter] [VARCHAR](100) NULL,
        [SubmittedDateTime] [DATETIME] NULL,
        [Authoriser] [VARCHAR](100) NULL,
        [Status] [VARCHAR](25) NULL,
        [StatusDateTime] [DATETIME] NULL,
        [Action] [VARCHAR](6) NULL,
    CONSTRAINT [PK__tbl_Shar__40A359E36F7C2C12] PRIMARY KEY CLUSTERED
    (
        [pkID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Table B
    CREATE TABLE [dbo].[tbl_Results](
        [pkKPIID] [INT] NOT NULL,
        [pkPeriodEndDate] [DATE] NOT NULL,
        [pkLocation] [VARCHAR](45) NOT NULL,
        [ConcatPrimaryKey] AS ((CONVERT([VARCHAR](7),[pkKPIID])+CONVERT([VARCHAR](8),[pkPeriodEndDate],(112)))+[pkLocation]) PERSISTED,
        [Numerator] [REAL] NULL,
        [Denominator] [REAL] NULL,
        [Result] [REAL] NULL,
        [Submitter] [VARCHAR](100) NULL,
        [SubmittedDateTime] [DATETIME] NULL,
        [Authoriser] [VARCHAR](100) NULL,
        [Status] [VARCHAR](25) NULL,
        [StatusDateTime] [DATETIME] NULL,
        [SYSDateLastUpdated] [DATETIME] NULL,
        [SYSDateLoaded] [DATETIME] NULL,
        [SYSStartDateTime] [DATETIME] NOT NULL,
        [SYSEndDateTime] [DATETIME] NOT NULL,
    CONSTRAINT [PK_tbl_Results_1] PRIMARY KEY CLUSTERED
    (
        [pkKPIID] ASC,
        [pkPeriodEndDate] ASC,
        [pkLocation] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF__tbl_Resul__SYSDa__3C34F16F] DEFAULT (GETDATE()) FOR [SYSDateLastUpdated]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF__tbl_Resul__SYSDa__3D2915A8] DEFAULT (GETDATE()) FOR [SYSDateLoaded]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF_tbl_Results_SYSStartDateTime] DEFAULT (GETDATE()) FOR [SYSStartDateTime]
    GO

    ALTER TABLE [dbo].[tbl_Results] ADD CONSTRAINT [DF_tbl_Results_SYSEndDateTime] DEFAULT ('20991231') FOR [SYSEndDateTime]
    GO

    For example, this code works:-
    INSERT INTO [dbo].[tbl_SharePoint_Data_Entry]
        ([KPIID]
        ,[PeriodEndDate]
        ,[Location]
        --,[Numerator]
        --,[Denominator]
        --,[Result]
        --,[Submitter]
        --,[SubmittedDateTime]
        --,[Authoriser]
        --,[Status]
        --,[StatusDateTime]
        ,[Action]
             )
      VALUES
        (1580
        ,'20181231'
        ,'A3'
        --,10
        --,10
        --,101
        --,'1'
        --,GETDATE()
        --,'1'
        --,'A'
        --,GETDATE()
        ,'INSERT'
             )
    GO

    The trigger fires and everything is good.

    If I then run this:-
    INSERT INTO [dbo].[tbl_SharePoint_Data_Entry]
        ([KPIID]
        ,[PeriodEndDate]
        ,[Location]
        --,[Numerator]
        --,[Denominator]
        --,[Result]
        --,[Submitter]
        --,[SubmittedDateTime]
        --,[Authoriser]
        --,[Status]
        --,[StatusDateTime]
        ,[Action]
             )
      VALUES
        (1580
        ,'20181231'
        ,'A3'
        --,10
        --,10
        --,101
        --,'1'
        --,GETDATE()
        --,'1'
        --,'A'
        --,GETDATE()
        ,'UPDATE'
             )
    GO

    it fails with the following error:-
    Msg 8152, Level 16, State 14, Procedure trg_tbl_Results_Update_SYSDateLastUpdated, Line 17 [Batch Start Line 2]
    String or binary data would be truncated.

    The trigger code is here and looks fine:-
    CREATE TRIGGER [dbo].[tr_tbl_SharePoint_Data_Entry]
    ON [dbo].[tbl_SharePoint_Data_Entry]
    AFTER INSERT, UPDATE
    AS
      BEGIN
         INSERT INTO [KPIData].dbo.tbl_Results
             (
                 [pkKPIID]
                ,[pkPeriodEndDate]
                ,[pkLocation]
                ,[Numerator]
                ,[Denominator]
                ,[Result]
                ,[Submitter]
                ,[SubmittedDateTime]
                ,[Authoriser]
                ,[Status]
                ,[StatusDateTime]
             )
         SELECT
                KPIID
              ,PeriodEndDate
              ,[Location]
              ,Numerator
              ,Denominator
              ,Result
              ,Submitter
              ,SubmittedDateTime
              ,Authoriser
              ,[Status]
              ,StatusDateTime
         FROM
                inserted
         WHERE
                [Action] = 'INSERT';

         UPDATE
                R
         SET
                R.Numerator = I.Numerator
              ,R.Denominator = I.Denominator
              ,R.Result = I.Result
              ,R.Submitter = I.Submitter
              ,R.SubmittedDateTime = I.SubmittedDateTime
              ,R.Authoriser = I.Authoriser
              ,R.[Status] = I.[Status]
              ,R.StatusDateTime = I.StatusDateTime
         FROM
                [KPIData].dbo.tbl_Results AS R
             INNER JOIN
                 inserted AS I
                    ON R.pkKPIID = I.KPIID
                      AND    R.[pkPeriodEndDate] = I.PeriodEndDate
                      AND    R.pkLocation = I.[Location]
         WHERE
                I.[Action] = 'UPDATE';

      END;

    If I swap the literal dates in the INSERT statement for GETDATE() (as in the other two places) it works.

    Anyone any ideas?

    Wrap the literals in a convert with either 12 or 121 as the format parameter.
    😎


    CONVERT(DATE,'20181231',12)

  • March 28, 2018 at 3:47 am

    #1984747

    The data is coming from a SharePoint list and we've got the company coming as well next week so we'll have to see what they can do with that from there side.
    It's just a bit odd that if I use getdate() first, then the date literal will then work.
    It's also a bit odd that we can do a straight insert into both tables with the data.

    I'll keep the CONVERT in mind in case they can use it.

  • March 28, 2018 at 7:35 am

    #1984788

    richardmgreen1 - Wednesday, March 28, 2018 3:47 AM

    The data is coming from a SharePoint list and we've got the company coming as well next week so we'll have to see what they can do with that from there side.
    It's just a bit odd that if I use getdate() first, then the date literal will then work.
    It's also a bit odd that we can do a straight insert into both tables with the data.

    I'll keep the CONVERT in mind in case they can use it.

    Sounds an awful lot like it's either a data type precedence or date format problem.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 28, 2018 at 8:14 am

    #1984803

    Oh, wonderful. 🙁

    Thankfully, I don't think it's my problem, it'll be the 3rd-party vendor's problem.
    Our database tables are set up to specification, it's just the insert (I think) that's the problem.

  • March 28, 2018 at 8:54 am

    #1984812

    The trigger mentioned in the error is not the trigger for which you posted code. 

    Could you post the definition of the trigger mentioned in the error (trg_tbl_Results_Update_SYSDateLastUpdated)?

    Cheers!

  • March 28, 2018 at 9:24 am

    #1984825

    Jacob Wilkins - Wednesday, March 28, 2018 8:54 AM

    The trigger mentioned in the error is not the trigger for which you posted code. 

    Could you post the definition of the trigger mentioned in the error (trg_tbl_Results_Update_SYSDateLastUpdated)?

    Cheers!

    Nice catch, Jacob...  didn't see that.  We might well be barking up the entirely wrong tree...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 28, 2018 at 9:29 am

    #1984834

    :blush: Oops!
    That'll teach me to read the error message properly.
    The code for the problem trigger is here:-
    CREATE TRIGGER [dbo].[trg_tbl_Results_Update_SYSDateLastUpdated]
    ON [dbo].[tbl_Results]
    AFTER UPDATE
    AS
    BEGIN
        DECLARE @Now DATETIME2 = GETDATE()

        --create temp table to hold keys for changed records
        DECLARE @UpdatedRecords TABLE
        (
            [pkKPIID] INT
            ,[pkPeriodEndDate] DATE
            ,[pkLocation] VARCHAR(45)
        )

        --get a list of updated records where data has changed
        ;WITH Updates AS
        (
            SELECT
                [pkKPIID]
                ,[pkPeriodEndDate]
                ,[pkLocation]
                ,[ConcatPrimaryKey]
                ,[Numerator]
                ,[Denominator]
                ,[Result]
                ,[Submitter]
                ,[SubmittedDateTime]
                ,[Authoriser]
                ,[Status]
                ,[StatusDateTime]

            FROM DELETED

            EXCEPT SELECT
                [pkKPIID]
                ,[pkPeriodEndDate]
                ,[pkLocation]
                ,[ConcatPrimaryKey]
                ,[Numerator]
                ,[Denominator]
                ,[Result]
                ,[Submitter]
                ,[SubmittedDateTime]
                ,[Authoriser]
                ,[Status]
                ,[StatusDateTime]

            FROM INSERTED
        )
        --insert old records into history table
        INSERT INTO [tbl_Results_History]
        (
                [pkKPIID]
                ,[pkPeriodEndDate]
                ,[pkLocation]
                ,[ConcatPrimaryKey]
                ,[Numerator]
                ,[Denominator]
                ,[Result]
                ,[Submitter]
                ,[SubmittedDateTime]
                ,[Authoriser]
                ,[Status]
                ,[StatusDateTime]
                ,[SYSDateLastUpdated]
                ,[SYSDateLoaded]
                ,[SYSStartDateTime]
                ,[SYSEndDateTime]
        )
        OUTPUT INSERTED.[pkKPIID], INSERTED.[pkPeriodEndDate], INSERTED.[pkLocation] INTO @UpdatedRecords
        SELECT
            u.[pkKPIID]
            ,u.[pkPeriodEndDate]
            ,u.[pkLocation]
            ,u.[ConcatPrimaryKey]
            ,u.[Numerator]
            ,u.[Denominator]
            ,u.[Result]
            ,u.[Submitter]
            ,u.[SubmittedDateTime]
            ,u.[Authoriser]
            ,u.[Status]
            ,u.[StatusDateTime]
            ,d.[SYSDateLastUpdated]
            ,d.[SYSDateLoaded]
            ,d.[SYSStartDateTime]
            ,[SYSEndDateTime]            = DATEADD(MILLISECOND,-1,@Now)
        FROM
            Updates u

            INNER JOIN Deleted d
                ON    u.[pkKPIID] = d.[pkKPIID]
                AND    u.[pkPeriodEndDate] = d.[pkPeriodEndDate]
                AND    u.[pkLocation] = d.[pkLocation]

        --update SYSDateLastUpdated for records where data has changed
        UPDATE t
        SET
            SYSDateLastUpdated = @Now
            ,SYSStartDateTime = @Now
        FROM
            [tbl_Results] t

            INNER JOIN @UpdatedRecords u
                ON    u.[pkKPIID] = t.[pkKPIID]
                AND    u.[pkPeriodEndDate] = t.[pkPeriodEndDate]
                AND    u.[pkLocation] = t.[pkLocation]
    END
    GO

    ALTER TABLE [dbo].[tbl_Results] ENABLE TRIGGER [trg_tbl_Results_Update_SYSDateLastUpdated]
    GO
    Now I've read the code for the problem trigger, I've seen there's a history table which had the status at 8 characters.
    I've expanded that to 25 characters like the main tables and I'll get the vendor to test again.

    Thanks all.

  • March 28, 2018 at 9:37 am

    #1984839

    Heh, no worries. We've all skimmed over some important detail once or twice (no more than that, right? 🙂 )

    It would be odd for it to be a difference in that column, given what you've described so far (in the example queries you gave you don't even insert a value for Status, and the behavior changes based on what you insert for PeriodEndDate).

    Just to round things out, could you also post the definition for tbl_Results_History?

    Cheers!

  • March 29, 2018 at 2:56 am

    #1984918

    I've updated the field size on this table already but here's the definition:-
    CREATE TABLE [dbo].[tbl_Results_History](
        [pkKPIID] [INT] NOT NULL,
        [pkPeriodEndDate] [DATE] NOT NULL,
        [pkLocation] [VARCHAR](45) NOT NULL,
        [ConcatPrimaryKey] [VARCHAR](60) NULL,
        [Numerator] [REAL] NULL,
        [Denominator] [REAL] NULL,
        [Result] [REAL] NULL,
        [Submitter] [VARCHAR](100) NULL,
        [SubmittedDateTime] [DATETIME] NULL,
        [Authoriser] [VARCHAR](100) NULL,
        [Status] [VARCHAR](25) NULL,
        [StatusDateTime] [DATETIME] NULL,
        [SYSDateLastUpdated] [DATETIME] NULL,
        [SYSDateLoaded] [DATETIME] NULL,
        [SYSStartDateTime] [DATETIME] NOT NULL,
        [SYSEndDateTime] [DATETIME] NOT NULL,
    CONSTRAINT [PK_tbl_Results_History] PRIMARY KEY CLUSTERED
    (
        [pkKPIID] ASC,
        [pkPeriodEndDate] ASC,
        [pkLocation] ASC,
        [SYSStartDateTime] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply