I use ODBC for a lot of one-off queries, pulling the results right into Excel.
Lately I’ve found a lot of text fields are coming through as blank. A quick check via a BAQ shows there is data in them.
I found a workaround online, that requires tweaking the SQL command to CAST the offending column.
SELECT Part.PartNum, Part.PartDescription
FROM LIVE.Erp.Part Part
has to be changed to
SELECT Part.PartNum, CAST(Part.PartDescription AS nvarchar(1000))
FROM LIVE.Erp.Part Part
Is the initial symptom (fields being returned as blank) anything I should be concerned about? Is some DB maintenance necessary?
Check which SQL driver you are using. In my experience, there are two of them. One produces the output you are seeing, and the other works as you would expect. I believe one is called SQL Server and the other is SQL Server Native Client.
Thanks -
Driver “SQL Server” returns the actual value of PartDescription.
Driver “SQL Native Client 11.0” incorrectly returns blanks for PartDescription for all records.
Great! You got it. Here’s another piece of trivia: In E10, the “sister” tables with UD fields (i.e., Part_UD), will have a different database “prefix” of dbo and I believe that is because they are considered views. I’m not that technical, but when pulling data from Part and Part_UD, you’ll notice Microsoft Query flips from Erp. to dbo. accordingly.
I haven’t checked since changing the driver, but with the other one (SQL Native 11) tables that had UD columns, seemed to lose their key fields. The fields were still there, but weren’t bold, and wouldn’t auto-join with related fields when adding tables.
The prefix on the tables is the Schema that object belongs to. Generally speaking the views that Epicor generates that combine the *_UD and regular tables are all in the DBO schema, the regular Epicor tables (JobDtl, LaborHed, etc…) are in the ERP schema and the Epicor system tables are in the ICE schema.