Skip to main content
Solved

Different output query tool vs transfer in the odx TX SQL connector (Classic)

  • October 15, 2024
  • 5 replies
  • 62 views

Bernarddb
TimeXtender Xpert
Forum|alt.badge.img+4

We are experiencing a small issue with one of the connectors while using managed queries. When I use the query tool, we are utilizing the SQL endpoint from a Fabric Lakehouse to retrieve the data into the ODX. When I query the DBO table, I receive 12 records. For example, when running this query:

SELECT [SinkCreatedOn],
       [SinkModifiedOn],
       [omoperatingunitnumber],
       [modifieddatetime],
       [modifiedby],
       [createddatetime],
       [createdby],
       [dataareaid],
       [recid],
       [tableid],
       [createdon],
       [modifiedon],
       [IsDelete]
  FROM dbo.[omoperatingunit]
 

I also get 12 records. However, when we transfer this managed query, we only receive 11 records. This issue has occurred with multiple tables. We are not applying any filters to this dataset.

Do you have any idea why this might be happening? Manually excluding or including all the columns is not manageable on the scale we are working with, so we rely on writing managed queries.

Best answer by Christian Hauggaard

Issue appears to be resolved by upgrading to 20.10.52 and TX SQL 16.3.0.0 data source

View original
Did this topic help you find an answer to your question?

5 replies

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 649 replies
  • October 16, 2024

Hi @Bernarddb ,

which connector are you using to connect? In theory, you should be able to use OleDB (make sure you have the most recent drivers installed on TX machines: https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server), ODBC and others. I expect you should be able to treat a SQL Analytics endpoint as if it were SQL Server being called with read-only intent.

I wonder if all connectors result in the same result or whether it is limited to one or more. If you run a COUNT (*) FROM table Table Query, does it return 12 or 11?

 


Bernarddb
TimeXtender Xpert
Forum|alt.badge.img+4
  • Author
  • TimeXtender Xpert
  • 53 replies
  • October 16, 2024

@rory.smith 

 we are currently using 

When i use the count(*) it returns 12. and that's the same number of rows i receive if i use the normal dbo transfer.


rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 649 replies
  • October 16, 2024

Hi @Bernarddb,

maybe if you install the most recent SQL Server OleDB drivers and try Microsoft OLE DB Driver for SQL Server the result will be different? At least the count shows you that it is not some kind of rights situation with respect to the credentials Ingest is using to extract the data from Fabric.

So something is either going wrong in general, or with the TX SQL source specifically. If the Microsoft OLE driver shows the same behaviour that may help narrowing down where the problem lies. I suspect it is support ticket time regardless though.


Bernarddb
TimeXtender Xpert
Forum|alt.badge.img+4
  • Author
  • TimeXtender Xpert
  • 53 replies
  • October 17, 2024

I'm going to create a Support ticket. Will update if we have an outcome


Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Issue appears to be resolved by upgrading to 20.10.52 and TX SQL 16.3.0.0 data source


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings