topic Qlik Data Catalyst June 2020 SQL Server data source error in Catalog and Lineage

Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Tue, 07 Dec 2021 22:35:04 GMT

After installing QDC June 2020 release, I am observing strange behavior when trying to pull data from a SQL Server data source. I have deployed the correct driver and successfully added the source connection, however, when attempting to 'Add Data' from this source, I am seeing the error below(Please note i've replaced my actual db name with DBNAME):

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1137)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:319)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.podiumdata.base.error.PodiumFault: core.error.code.METADATA_LOAD_FAILURE - Failed to get schema metadata
at com.nvs.core.metadata.MetaDataObject.getSchemaMetaData(MetaDataObject.java:132)
at com.nvs.core.metadata.MetaDataObject.getSchemaMetaData(MetaDataObject.java:36)
... 104 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '[DBNAME].sys.schemas'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(SQLServerStatement.java:699)
at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFromInternalQueries(SQLServerDatabaseMetaData.java:290)
at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getSchemasInternal(SQLServerDatabaseMetaData.java:1480)
at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getSchemas(SQLServerDatabaseMetaData.java:1499)
at com.nvs.core.metadata.MetaDataObject.getSchemaMetaData(MetaDataObject.java:130)

While trying to diagnose, I captured the query from QDC on the SQL server side using the XEvent Profiler only to find that QDC is adding erroneous brackets as seen here:

select [[DBNAME]]].sys.schemas.name 'TABLE_SCHEM', CASE WHEN [[DBNAME]]].sys.schemas.name IN ('dbo', 'guest','INFORMATION_SCHEMA','sys','db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin' ,'db_backupoperator','db_datareader','db_datawriter','db_denydatareader','db_denydatawriter') THEN null ELSE '[DBNAME]' END 'TABLE_CATALOG' from [[DBNAME]]].sys.schemas order by 2, 1

I've attempted to remove the escape characters from the database driver settings located @ Admin>Database Drivers>SQLSERVER> Edit Details...

However, this throws a new error when attempting to pull data:

Error! pgui.error.code.DYNAMIC_ERROR - null

Has anyone been able to successfully pull data from a SQL Server connection on June 2020? This was a clean install but I was previously on December 2019 which was working without issues. After attempting to upgrade from Dec 2019 > June 2020 and experiencing issues, I started with fresh server.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Fri, 26 Jun 2020 21:43:52 GMT

Sql Server version? And what version driver are you using?

Re: Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Mon, 29 Jun 2020 13:06:19 GMT

SQL Server version 2017 & Microsoft JDBC Driver 8.2 for SQL Server (mssql-jdbc-8.2.0.jre8.jar)

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Mon, 29 Jun 2020 15:29:49 GMT

Thanks @jonrayos ,

Would you be willing to try 8.2.2 jre8?

This looks a lot like it's a jdbc driver issue, and that's one I've had some good success with.

David

Re: Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Mon, 29 Jun 2020 16:25:50 GMT

Thanks for the suggestion David,

Unfortunately we are seeing the same error with 8.2.2 jre8

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Mon, 29 Jun 2020 21:02:44 GMT

Thanks for that...it will be needed at some point in any case.

Attached is a copy of the Driver registration screen, can you show me what yours looks like?

Re: Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Mon, 29 Jun 2020 21:10:05 GMT

Dave,

Please see the attached screenshot. I did play around with the escape character default values while attempting to fix the issue but have set them back to their original values.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Wed, 01 Jul 2020 12:34:50 GMT

Thanks @jonrayos

Can you please create a support ticket, so we can help troubleshoot this issue better with you. The link to create a support ticket is :https://support.qlik.com/QS_ContactUs

Thanks, David

Re: Qlik Data Catalyst June 2020 SQL Server data source error

qliktechie15 — Sat, 04 Jul 2020 02:16:57 GMT

Hi,

I am facing the same issue.

Has there been any solution for this ?

Thanks

Re: Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Mon, 06 Jul 2020 13:42:34 GMT

@qliktechie15 ,

I have submitted a support ticket - I will post the solution when it comes.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

jonrayos — Mon, 14 Sep 2020 19:58:00 GMT

@qliktechie15 I have been informed that the September release includes a fix for this bug.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

qliktechie15 — Mon, 14 Sep 2020 22:39:48 GMT

Thanks @jonrayos - I will check !

Re: Qlik Data Catalyst June 2020 SQL Server data source error

maksim_senin — Mon, 05 Apr 2021 10:25:54 GMT

...just in case you're still straggling with this please follow this link - https://community.qlik.com/t5/Qlik-Catalog-Discussions/QDC-and-JDBC-error-after-click-on-Test-Connection/m-p/1796843/highlight/true#M568 - maybe it'll help.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

aswinmawlee — Thu, 15 Apr 2021 18:53:08 GMT

It is like an unresolved issue for me literally, been trying to get out of but at the end of the day same error is coming.

Let's hope solutions comes to this issue very soon. njmcdirect

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Thu, 15 Apr 2021 20:25:20 GMT

Hello,
From this picture, you can see that there are escape characters you can set to control quote identifiers.
I wonder if it would be a good test to remove those completely, and assuming they are currently at ‘[‘, they would reform your sql from ‘[[‘ to ‘[‘, making it legal.
If you’re unclear where that setting is .. that page is super helpful.. can I ask that you give that a shot?
Thanks!

Re: Qlik Data Catalyst June 2020 SQL Server data source error

aswinmawlee — Thu, 15 Apr 2021 20:30:10 GMT

Thank you so much for the boost up sir,

let me give it another try.

Re: Qlik Data Catalyst June 2020 SQL Server data source error

DaveHowland — Thu, 15 Apr 2021 20:40:20 GMT

And here is the page I was alluding to .
The hyperlink didn't make it before.
https://help.qlik.com/en-US/catalog/February2021/Content/QlikCatalog/Administration/Database_Driver_Registration.htm
Database driver registration ‒ Qlik Catalog
Database driver registration. Users must register drivers for all JDBC source databases. Administration (top navigation bar) provides a wizard for the modular registration of databases; the database name populates as an option when registering sources to Qlik Catalog via JDBC.To initiate the driver registration wizard from the Administration screen, select Database Drivers from the menu ribbon ...
help.qlik.com