Error - [SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...)

OritA · Sep 3, 2021 3:47:41 AM

When running a Replicate CDC task replicating from MS-SQL source endpoint, you may get an error like the following error:

[SOURCE_CAPTURE ]E: SqlStat: 42000 NativeError:9005 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...). (PcbMsg: 105) [1020417] (sqlserver_log_processor.c:4310)

Environment

Qlik Replicate with CDC task running on MS-SQL source endpoint

Resolution

1. If the cause of the problem is reason 1 mentioned below, to eliminate the error, you should edit the MS-SQL source endpoint setting under Replicate GUI:

manage endpoints connections --> [your MS-SQL source endpoint definition] --> advanced tab --> internal parameters

add the internal parameter : ignoreTxnCtxValidityCheck and set it to True

2. If the cause of the problem is reason 2 mentioned below, you should make sure that the TLOG backups are available to Replicate

Possible Causes

1. When working with MS-SQL source endpoint, COMMIT event usually show up with LCX_NULL context. Here it comes with a context which is not recognized yet and therefore Replicate produces an error. An easy and immediate option to solve the error is to ignore the validity of the CONTEXT value in case of COMMIT, assuming that MS-SQL storage engine knows its job better than Replicate.

2. When Replicate reads from the online log it will use the fn_dblog function, the first parameter passed to the query is the last LSN that Replicate processed. If There is no access to the backups and the log has been truncated you can get this error

(If probelm persists need to produce detailed debug information for further analysis)

Steve_Nguyen · ‎2021-12-08

Normally when we see this error :

That error means that when we went to the online log and searched for the last LSN we processed it was not there. The log has been truncated.

=====

to validate if the log exist have your DBA run the query on the source db and provide the query output.

example :

use EnterprisePaymentServices;

select

[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),

[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),

bmf.* , bs.*

from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs

where bmf.media_set_id = bs.media_set_id

and bs.database_name=db_name() and bs.type='L'

and cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar (24) ) <=cast( '0x003ba696:00004290:0005' collate SQL_Latin1_General_CP1_CI_AS as varchar (24) )

and cast( '0x003ba696:00004290:0005' collate SQL_Latin1_General_CP1_CI_AS as varchar (24) ) < cast( [dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar (24) );

** notes to change 0x003ba696:00004290:0005 to the LSN report in the task error **

===

If the above does not return any data then the results of this query:

select top 10