Qlik Replicate Error: [SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...)

[OritA]

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

There are two possible root causes and two respective solutions.

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. 

Solution: 

Edit the MS-SQL source endpoint setting under Replicate GUI:

1. Go to Manage Endpoint Connections
2. Open your MS-SQL source endpoint definition
3. Open the Advanced tab
4. Go to Internal Parameters 
5. Add the internal parameter: ignoreTxnCtxValidityCheck and set it to True

When Qlik 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 Qlik Replicate processed. This error will occur if there is no access to the backups and the log has been truncated. 

Solution:

Make sure that the TLOG backups are available to Qlik Replicate.

[Steve_Nguyen]

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

[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'

[HOASTONGE]

Hello Team.

This is cool but if we are not the master of the database how can we ignore this error because we have in place only a team of DBA, Qlik, Analyst, Network Admin, etc... so we don't I don't have all the rights to the source database, that said I cannot connect with MSSQL directly to the source because I am in the BI Qlik team and not DBA
How to avoid getting this error

[Dana_Baldwin]

Hi @HOASTONGE 

Sorry for the late reply, just saw this. The most likely cause is that the backup TLOG was purged before the task could read it. This could be due to the task being down for some length of time, or just due to the retention policy. Please request that the source DBA increase the retention period of the backup TLOG files.

Dana