Skip to main content
To homepage To homepage
Solved

Issue with Retrieving a Column with the Same Name as a SQL Function

  • October 21, 2024
  • 6 replies
  • 69 views
S
Explorer
Forum|alt.badge.img

Hi everyone,

I'm facing an issue where one of the columns in my destination table has the same name as a SQL function, and because of this, I'm unable to retrieve it in a normal way. To work around this problem, I wanted to use the Query Table Tool to retrieve the column in a parameterized way.

However, for this specific data source (which is a Generic ODBC Data Source), the Query Table Tool option is greyed out. I've noticed that the Query Table Tool is available for other data source types.

Has anyone encountered this issue before?

Thanks in advance!

Best answer by Thomas Lind

Hi @sva 

https://legacysupport.timextender.com/hc/en-us/articles/4410466782621-Create-a-ODBC-data-source-in-a-ODX-or-with-a-Any-Source-ADO-Net-provider-in-a-Business-Unit#create-or-convert-to-a-any-source-net-odbc-data-source

I made a guide regarding this issue.

I also made this guide, but it is specifically about how to change a MySQL ODBC to any source .net with a MySQL provider installed

https://legacysupport.timextender.com/hc/en-us/articles/115005949443-How-to-change-a-MySQL-ODBC-type-data-source-to-a-Any-Source-ADO-MySQL-data-source

You can mix the methods, so you convert the existing ODBC to a Any Source ADO.Net pointing at the ODBC provider.

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

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

Hi @sva 

Which version of TimeXtender are you using? If using 20.10 can you please confirm if you are using Business Unit or ODX?

S
1 person likes this
S
Explorer
Forum|alt.badge.img
  • sva
  • Explorer
  • October 21, 2024

Hello @Christian Hauggaard, thanks for your reply. I am indeed using version 20.10 and make use of Business Units.

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • October 21, 2024

Hi @sva 

https://legacysupport.timextender.com/hc/en-us/articles/4410466782621-Create-a-ODBC-data-source-in-a-ODX-or-with-a-Any-Source-ADO-Net-provider-in-a-Business-Unit#create-or-convert-to-a-any-source-net-odbc-data-source

I made a guide regarding this issue.

I also made this guide, but it is specifically about how to change a MySQL ODBC to any source .net with a MySQL provider installed

https://legacysupport.timextender.com/hc/en-us/articles/115005949443-How-to-change-a-MySQL-ODBC-type-data-source-to-a-Any-Source-ADO-MySQL-data-source

You can mix the methods, so you convert the existing ODBC to a Any Source ADO.Net pointing at the ODBC provider.

S
1 person likes this
S
Explorer
Forum|alt.badge.img
  • sva
  • Explorer
  • October 22, 2024

Hi @Thomas Lind

This approach worked to retrieve the missing columns, thanks!

However, after I changed the data source provider from 'Generic ODBC Data Source' to 'Any Source ADO.NET', some tables became inaccessible. The data source in question contains both tables and views that need to be retrieved. When I specify in the advanced settings that I want to extract both tables and views, I get the correct table schemas, but this doesn’t apply to the views. For the views, I only receive those from the "sys" schema, even though I specify in the object filtering that I only want to retrieve views from the "dbo" schema.

When I disable object filtering, I receive all the table schemas correctly, but for the views, I still only get those from the "sys" schema.

I also tried creating two separate data sources, one for tables and one for views, but I still don’t retrieve the views. Is this a limitation of ADO.NET, or is something else going wrong?

Thank you very much in advance!

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

Hi,

OLEDB will wrap ODBC in some cases but not in others. I believe the way Thomas proposes should wrap the ODBC driver involved, but it will likely manifest metadata differently. Are the views you are missing really views or are they synonyms? A long time ago there was some special handling for those if I recall correctly.

What is the actual source system, and what ODBC driver are you using to communicate with it?

S
Explorer
Forum|alt.badge.img
  • sva
  • Explorer
  • October 23, 2024

Hi @rory.smith,

They are indeed views and not synonyms. The source system being accessed is Microsoft Dynamics, which runs on Dataverse. This Dataverse is replicated to a Synapse Analytics SQL endpoint. Communication with this endpoint is done via the 'ODBC Driver 18 for SQL Server,' as this allows for managed identity access to be configured.

 

Reply


Most helpful members this week

Terms and ConditionsCookie settings

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