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
