Udostępnij za pośrednictwem

Snapshot agent fails with Error: 241, Severity: 16, State: 1 (Conversion failed when converting date and/or time from character string)

  • Artykuł

ISSUE

Let’s say you are monitoring Replication Agent status and noticed that the Snapshot Agent has failed with the following error while generating a snapshot, particularly during creation of BCP files.

Error: 241, Severity: 16, State: 1

Conversion failed when converting date and/or time from character string.

To further investigate the above error and to understand which query is causing it, run a SQL Profiler trace in the background.

CAUSE

In order to understand the cause of the issue, it’s important to look at the schema structure and the column on which indexing is done. So let’s take a look at a schema script for a sample table “product” which has been published for Transactional Replication.

CREATE TABLE [dbo].[ product] (

                [ItemID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL

                ,[ReportDate] [date] NOT NULL

                ,[ProviderID] [int] NOT NULL

                ,[InvoiceImmediately] [bit] NOT NULL

                ,CONSTRAINT [PK_ItemUnbilled] PRIMARY KEY NONCLUSTERED ([ItemID] ASC)

                )

CREATE CLUSTERED INDEX [IX_ItemUnbilled_ReportDate] ON [dbo].[ product] (

                [ReportDate] ASC

                ,[InvoiceImmediately] ASC

                ,[ProviderID] ASC

                )

So the primary key (which is non-clustered index) is on [ItemID] column, and the clustered composite index is on three columns with the leading column as [ReportDate]. Before the error is raised, you will see something like the following query getting executed.

Select * from [dbo].[syncobj_0x4434414538323435] where ([ReportDate] is null) or ([ReportDate] <= N'31/01/2014 00:00:00') order by [ReportDate] ASC,[InvoiceImmediately] ASC,[ProviderID] ASC

The “Where” clause we see here in the SELECT statement is because of BCP partitioning and the BCP file partitioning was done based on the [ReportDate] column (the leading column in the clustered index key). [ReportDate] column is of data type Date. The conversion fails because of the difference in language used by the account used to run the snapshot agent which was “UK English” - (dd/mm/yyyy) in this case, and the default language of SQL Server is “ US English “- (mm/dd/yyyy).

WORKAROUNDS

Now there are multiple easy workarounds available for the problem in question. I have listed the workarounds below.

A) Use a different account, for which the default language setting is same as “ US ENGLISH” to run the Snapshot agent. Alternatively if possible, change the default language setting of the existing account to “ US English”.

Note: This is the most preferred method.

B) You can disable BCP partitioning only for the problematic table by using static row filters. We do not go for BCP partitioning for tables that are filtered. Thus you can have a static row filter on the table in question but still retrieve all the rows without triggering BCP partition, using a query like, “Select * from table where 1=1”.

Note: BCP partitioning will be disabled only for the table in question not the entire publication.

C) If @sync_method property of publication is set to “Native”, it’s recommended to change it to the default option of ”Concurrent”. This will be helpful if the snapshot agent is picking up a Date type column for BCP partitioning just because it has clustered index on it and the actual primary key column in the table is non-clustered. In the default setting, which is “Concurrent” , BCP partitioning happens only if the primary key column has a clustered index.

Exec sp_addpublication <---@sync_method> = N'concurrent'----

Note: If the primary key column does not have clustered index and the @sync_method is set to “concurrent” then the BCP partitioning will be disabled for the entire publication. You might find snapshot agent taking more time to generate snapshot if there is any other big table in the same publication.

D) You can disable BCP partitioning for the entire publication by using the -EnbaleArticleBCPPartitioning 0 in the Snapshot Agent job step properties. Please refer to Paul Ibison’s blog for more details: https://www.replicationanswers.com/BCPPartitioning.asp.

Disclaimer: Links to third-party web sites are provided for information purposes only. Microsoft does not endorse nor support the content in third-party links. Microsoft is not responsible for the content of a third-party website. Privacy and security policies may differ from those practiced by Microsoft.

Note: BCP partitioning will be disabled for the entire publication; you might find snapshot agent taking more time to generate snapshots if there is any other big table in the same publication.

E) The last option is to ensure that the primary key column has a clustered index and is not of data type Date.

Note: This needs schema level changes.

While I strongly suggest using the first workaround because it’s the recommended approach, I am eager to hear about the workaround you choose for your environment and the reasons for your choice. So please state the details in the comments section.

Author:

Madhumita Tripathy , Support Engineer, Microsoft India GTSC

Reviewed by:

Rishi Maini, Escalation Engineer, Microsoft India GTSC

Vikas Rana, Technical Advisor, Microsoft India GTSC

Comments

  • Anonymous
    May 10, 2015
    NICE