Solved

STRING_SPLIT error when trying to print a PO for international supplier

  • 7 September 2023
  • 3 replies
  • 51 views
M
Userlevel 1

Last week we had an issue where we could not get a PO to print for a supplier. Turns out it was for a new ship to that is in Canada. When I looked at System Monitor the error referenced a STRING_SPLIT error.

Contacted Epicor and googled it and got a recommendation to up our compatibility level on the SQL Server 2017 to the 140 level from the 120 level I had it set on. Great PO and Subcontract Pack Slip now printed.

However, there were some SSRS custom reports that started taking 3-5 minutes to render where they had been seconds. Not all reports, just some. And when those were running the SQL Server CPU pegged out at 100%. Not good. I contacted Epicor again, since they assured me that upping that level would not affect anything else in our system. 

I put compatibility back at 120 since the Intern’l supplier POs will be few and far between so I’ll just work around it. But now there is a dashboard that shows a “Bad SQL Statement - See Logs” error. When I review the logs, it shows that same STRING_SPLIT error. Not sure why all of a sudden I am seeing these issues since we moved to our current 2022.2.14 back in April.

Now Epicor is suggesting that I do and setup Index Maintenance on the SQL Server but no recommendations on frequency. 

Any thoughts or additional information on how we can move forward would be greatly appreciated. I am no SQL expert, and it is such an integral part of the system, I am trying to find a solution to get everything my users need without crashing our system. 😀

Thanks

Melissa

icon

Best answer by melissah 14 September 2023, 17:52

View original

3 replies

fred
Userlevel 3

If you are not doing standard maintenance on your SQL server, you will run in to issues.  Here is what I usually recommend:

Hourly (or as needed to support your recovery objectives): Transaction Log Backups

Daily - Full backups of all user databases

Weekly - DBCC check on all (user and system) databases, index defragmenting (see below), Update stats (see below), Clean up History, backup full, backup transaction log, remove old backup files, cycle SQL logs, run sp_blitz.

Make sure you have backups stored locally as well as on a different machine/storage, and a copy offsite.  Look up “3-2-1 rule” for more information.

If you need more information on any of this, let me know. Happy to help out.  CodaBears offers a managed IT/Managed ERP service to help with this.

If you’re not familiar with the various Recovery Models in SQL and how they impact your backups, I wrote a paper on this and did a presentation at the IA User Group: http://digioh.com/em/20867/56106/3tbv9v4q2a

 

Index defrag - I STRONGLY recommend using Ola Hallengren’s scripts for this.  Epicor marked some tables as having to rebuild the index instead of defragmenting it.  These scripts handle that.  They also look at how fragmented something is before doing the rebuild/reindex.  This will also update your statistics.  Statistics are critical for good performance.  I’ve literally had a 3 minute query drop to 2 seconds because stats were updated.

 

Cycle logs - you want to do this or else your logs keep growing and will become unusable when you need to search.

 

sp_blitz - this will help you find problems before they are problems.  You can find this by searching sp_blitz download Ozar.  Brent Ozar is the original author.

 

Turn on Query Store for your critical databases (Epicor Production).  This helps SQL tune itself across reboots.  SQL is constantly tracking things and will tune itself based on what you are doing.  But that information is wiped when you restart SQL Services.  Query store is a way to keep (some of) that information across restarts.

 

Let me know if you need more information.

Fred Zelhart, CodaBears, Inc.
M
Userlevel 1

I will review with my infrastructure team and see where we are at with the items above. We have alway been on Prem so I can’t imagine we don’t have some of this setup already.

Thanks for the tips. I’ll report back

Melissa

M
Userlevel 1

Update:

Turns out is was CASE WHEN statement issue when using in a Group By section when on SQL2017 Compatibility 140. Changed the compatibility down to SQL 2016 130 and reports run fine without pegging server and can still print the forms that were the original reason we were updating the Compatibility level.

So now doing research on how to either reformat the CASE WHEN statements to SQL 2017 likes it better or pulling the data into a temp table and then doing a join and select from that. Then SQL is only looking at getting the data once versus trying to do the CASE WHEN on every line. At least that is my understanding.

Thanks All!

Melissa

Reply


Terms & Conditions