Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2022 Release Wave 2Check out the latest updates and new features of Dynamics 365 released from October 2022 through March 2023
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
I'm trying to configure ODBC to connect to Dynamics CRM but I'm getting error like "Error - Invalid table name syscharsets for schema sys"
Is there anyone can help
The following answer is stated under the assumption that you are trying to connect to the TDS / Dataverse endpoint to connect to the data:
I was testing this and using the supported authentication method.
The specific section identifying the auth requirements here:
When you are creating an ODBC connection we need to used 'AAD with MFA' . I noticed there is not a value that specifically states 'AAD with MFA'. However, I found the following documentation:
Using Azure Active Directory with the ODBC Driver - ODBC Driver for SQL Server | Microsoft Docs(Windows driver only.) Azure AD Interactive Authentication uses Azure Active Directory Multi-Factor Authentication technology to set up connection. In this mode, by providing the login ID, an Azure Authentication dialog is triggered and allows the user to input the password to complete the connection. The username is passed in the connection string. server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive;
What this means is we need to use the following value when setting up the ODBC connection
However, when I set this up and try to run the 'Test Connection' I get the same error:"Error - Invalid table name syscharsets for schema sys"
In order to understand this exception we need to understand what 'syscharsets' means
sys.syscharsets (Transact-SQL) - SQL Server | Microsoft Docs
Basically, syscharsets is a standard SQL System view which would show up in any traditional MSSql database. However, the TDS / Dataverse connection to your organization is not a connection to a standard SQL database and this view does not exist. With this in mind, I decided to query some data using the ODBC connection I created via powershell and found that the data extracted without error. This tells me that my ODBC connection is fine, but the Test Connection will never succeed due to the fact that the system view does not exist in the TDS/Dataverse connected organization.
My recommendation is to test your connection using Powershell. Here is a simple example I used replacing the sql text with:
select auditretentionperiod from organizationHow to fetch data with ODBC DSN connections using ... - Qlik Community - 1712812
Microsoft Dynamics Support
Thanks Ken, it was very helpful.
Glad I could help.
Business Applications communities