Skip to content
+1-888-319-3663

HELP ARTICLE

SmartConnect ODBC query error – Restricted data type attribute violation


My customer had been using SmartConnect 2018 and an ODBC query against an on-prem Microsoft CRM for a number of years and was working fine even after updating to SC 21.

Recently, they moved to Microsoft CRM CE.  After enabling their Dataverse Tabular Data Stream (TDS) endpoint that emulates a SQL data connection, they made a new ODBC Connection in SmartConnect to connect and run that same query as before. 

The customer had selected the ODBC Driver 18 for SQL Server as they needed that for their requirements to connect/query that database.

Because SC doesn’t offer that driver as a built-in connection type, they had to create a machine DSN and then use an ODBC DSN Connection from SmartConnect as that has been done in the past with other drivers.

SC 21 ODBC Query using DSN


After this change, they found that SmartConnect and the ODBC query threw an error:
ERROR [07006] [Microsoft] [ODBC Driver 18 for SQL Server] Restricted data type attribute violation

 

ERROR [07006] [Microsoft] [ODBC Driver 18 for SQL Server] Restricted data type attribute violation



After a bit of troubleshooting, they discovered that the issue was a field that was of defined as type “Money” in the SQL table.  Because the table was from a 3rd party application (CRM CE), it couldn’t just be changed to a decimal even though that datatype was working in the query elsewhere.



The solution was to cast/convert the data from the Money to a supported datatype.

We initially tried casing/converting the datatype to decimal but that gave the same attribute violation error.  This was unexpected since another decimal column worked fine. 

What did work successfully was changing it to varchar instead.

These three methods did work for the customer and resolved the datatype issue.
Since now SmartConnect/ODBC read the data as a “string” datatype, if we needed to do math calculations on it on the SmartConnect side then we would use a calculated field and re-cast the field back into a numeric or whatever datatype was required.

Query Examples

CAST(LineAmount AS VARCHAR(12)) AS ListPrice

LTRIM(RTRIM(STR(LineAmount,19,5))) as LineAmount

CONVERT(varchar, LineAmount) AS LineAmount

RECENT POSTS


HTTP Error 500.19 - Internal Server Error when browsing the SmartConnect WCF web service.
SmartConnect.com Inactivity Timeout error running integration
Message #24574 is missing
Your registered version of SmartList Builder does not match the installed version of SmartList Builder.
How to Pay for a Product Renewal or Create a Quote

POPULAR POSTS


SSL Security error using OLEDB Connection
This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.