topic Re: Replicate SQL Server Error in Qlik Replicate

Replicate SQL Server Error

yzh — Thu, 01 Jun 2023 02:21:20 GMT

The source endpoint is SQL server, and the target endpoint is Oracle. Only one table has been replicate in this task, which frequently delays and has a very high latency. Sometimes an error can be reported, causing the job to abort. In the log attachment.

Re: Replicate SQL Server Error

Anonymous — Thu, 01 Jun 2023 02:36:38 GMT

Hello,

It looks like a permission issue,

[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 229 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The EXECUTE permission was denied on the object 'attrep_fn_NumericLsnToHexa', database 'DB_PNR_PRD', schema 'dbo'. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:4000)

Please make sure you have granted all the required permission,

https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedReplicateHDD/SQLServer-Source/SQLServerDBSource_Security.htm

Also you can refer to the below link which might be useful.

https://stackoverflow.com/questions/3708348/the-execute-permission-was-denied-on-the-object-xxxxxxx-database-zzzzzzz-s

there is a detailed explanation on how to grant "Execute" permission under the "explicit" tab in the above link, kindly see if it is of any help.

Regards,

Yadunandan

Re: Replicate SQL Server Error

yzh — Thu, 01 Jun 2023 02:47:33 GMT

Thank you for your answer.But our company's DBA does not recommend enabling sysadmin permissions.

The account we replicate with CDC has read-only account permissions.

Is there any other way to prevent the suspension of task? What is the reason for the delay of more than ten

hours?

Could you please help me take a look？

Re: Replicate SQL Server Error

Anonymous — Thu, 01 Jun 2023 03:11:55 GMT

Hello,

Yes, you can setup non-sysadmin user if you don't want to enable sysadmin permissions.

the below link explains the steps to follow for the same.

https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedReplicateHDD/SQLServer-Source/nonsys_admin.htm

Re: Replicate SQL Server Error

yzh — Thu, 01 Jun 2023 03:19:43 GMT

Thank you very much.I will ask our company's DBA to try setting up.

Re: Replicate SQL Server Error

yzh — Thu, 01 Jun 2023 03:24:27 GMT

Sorry, I inquired with our DBA and our account's permissions are sysadmin permissions. Additionally, our SQL server library is always on a cluster.Could you please help me take a look again？

Re: Replicate SQL Server Error

Heinvandenheuvel — Thu, 01 Jun 2023 04:00:56 GMT

Might I suggest to connect to the (source) SQL server in an an interactive session (using SSMS?) using the same credentials Replicate is using and manually executing that 'attrep_fn_NumericLsnToHexa', database 'DB_PNR_PRD' function. Better still, let the DBA try.

fwiw, this function is created by Replicate for Replicate. It should have access if nobody changed or re-created the Attunity User.

Hein.

Re: Replicate SQL Server Error

yzh — Thu, 01 Jun 2023 05:34:59 GMT

Thanks.I will ask our company's DBA to try.