AWS DMS CDC SQL Server Source fails to start when calling sys.fn_dump_dblog

Question

AWS DMS ongoing replication from SQL Server 2014 Standard edition. Reduced the task down to just one simple table. All tables have primary keys and replication is enabled on the server.

The task is starting not resuming, it fails when trying to start, the following is in the logs:

[SOURCE_CAPTURE ]D: Going to execute: ', select top 1 [Current LSN], from sys.fn_dump_dblog (NULL, NULL,NULL, 0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), where operation='LOP_BEGIN_XACT', and [Begin Time]>= cast('2018-09-05T09:47:08' as datetime), ' (sqlserver_log_queries.c:4172)

[SOURCE_CAPTURE ]D: Failed to execute statement: '' [1022502] (ar_odbc_stmt.c:2478)'

[SOURCE_CAPTURE ]D: RetCode: SQL_ERROR SqlState: 22007 NativeError: 242 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:2484)

If I recreate the query as:

select top 1 [Current LSN], * from sys.fn_dump_dblog (NULL, NULL,NULL, 0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) where operation='LOP_BEGIN_XACT' and [Begin Time]>= cast('2018-09-05T09:47:08' as datetime)

and then run this in SSMS it runs but does not return a value.

Based on the error; 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value', it makes me think the issue is something to do with '[Begin Time]>= cast('2018-09-05T09:47:08' as datetime)'

Has anyone come across this before?

Is it specific to the SQL Server version?

Is there anything I can do in the config of the replication task?

Is there a change I can make to the replication distribution of the SQL Server?

Andrew Melvin Andrew Melvin · Accepted Answer · 2018-09-05T12:21:50

Found the solution. It was I thought to do with the 'cast'

Changing the language for the user to us_english

USE [master]
GO
ALTER LOGIN my_dms_user WITH DEFAULT_LANGUAGE = us_english
GO

AWS DMS CDC SQL Server Source fails to start when calling sys.fn_dump_dblog

1 Answers