Unable to make column NOTNULL in indexed view

  • I am trying to setup indexed view replication from A --> B

    So far we have applied ISNULL (as shown in datetime) and able to create the IV as NOT NULL for the columns.

    This time around need to convert INT --> smallint. I have tried CONVERT and CAST with ISNULL and when i setup Transaction Replication using the tsql i get the following error:

    The replication option 'publish' of database 'HR' has already been set to true.

    Job 'USLVBIODSPC_1-HR-30' started successfully.

    Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.

    Msg 14089, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 1472

    The clustered index on materialized view '[xx].[Table]' may not contain nullable columns if it is to be published using the transaction-based method.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [XX].[TABLE_VW] WITH SCHEMABINDING AS

    (

    SELECT

    [EMPL_DRVD_ID],

    CASE

    WHEN [CLCK_IN_ACTL_DT] < CONVERT(DATE,'19000101',103) THEN isnull(CONVERT(SMALLDATETIME,'19000101',103),CONVERT(SMALLDATETIME,'19000101',103))

    WHEN [CLCK_IN_ACTL_DT] BETWEEN CONVERT(DATE,'19000101',103) AND CONVERT(DATE,'20790607',103) THEN isnull(CONVERT(SMALLDATETIME,[CLCK_IN_ACTL_DT],103),CONVERT (SMALLDATETIME,'19000101',103))

    ELSE isnull(CONVERT(SMALLDATETIME,'20790606',103),CONVERT(SMALLDATETIME,'19000101',103))

    END AS [CLCK_IN_ACTL_DT],

    ISNULL(cast (CLCK_IN_SEQ_ID as smallint), cast (CLCK_IN_SEQ_ID as smallint)) AS [CLCK_IN_SEQ_ID]

    FROM XX.TABLE

    Any thoughts and suggestion would be appreciated

    SQL Edition:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64)

    Noli Timere
  • I have added a snapshot showing how the columns look once the Indexed view is created.

    Noli Timere

Viewing 2 posts - 1 through 1 (of 1 total)

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