February 25, 2013 at 12:17 pm
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)
February 25, 2013 at 12:23 pm
I have added a snapshot showing how the columns look once the Indexed view is created.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply