RKL eSolutions Blog Trends and Insights

Solve Running Total Problems with MS SQL

KEY LEARNING: If you are using SQL SERVER 2012 or higher, when calculating running total values or variations, use Windowing Aggregate Functions as they are much much more performant than other methods. From time to time I run across running total ...

Continue reading
Tagged With: Sage 500, SQL Server

Write Text File Direct from Transact-SQL

There are many times that exchanging data with other systems in the supply chain might be done via text-based files (such as ASCII files, CSV files, and more). Sometimes it is to make the pertinent data available to external systems—like Microsoft® ...

Continue reading
Tagged With: SQL Server

Summarizing SQL data using ROLLUP and CUBE

Recently, I was asked to assist a client with some complex Transact-SQL (T-SQL) tasks they were using to gather some sales data, group the data, summarize it, by salespersons, SKUs, and so forth. In their present mode they were using a lot of ...

Continue reading
Tagged With: SQL Server

SSIS Returns an Error When Importing an Excel Spreadsheet on SQL Server 2008 R2

I was working through a request from a customer to import an excel spreadsheet into SQL Server on a recurring basis. I decided to create an SSIS Package to accomplish this and then call the package from a job in SQL Server Agent. However, when I ran ...

Continue reading
Tagged With: SQL Server

Uncovering Hidden Characters In A SQL Database

Despite the best efforts of programmers to eliminate them through error trapping, sometimes unwanted and, usually, invisible characters sneak into user data. A common example is the presence of carriage returns (CRs) or line feed (LF) characters in ...

Continue reading
Tagged With: SQL Server

Changing the Server Collation Method in SQL for Sage ERP X3

We recently encountered a SQL Server that had Sage ERP X3 installed for testing, but it did not have the correct collation method. Sage ERP X3 requires a SQL collation method of Latin1_General_BIN. This option gets set during the installation of SQL ...

Continue reading
Tagged With: Sage X3, SQL Server

Using Excel with Sage 500 to View Supply Chain Replenishment Priorities

While Sage 500 ERP and other ERP systems generally provide several options (such as running Inventory Replenishment or MRP) for determining suggested replenishment quantities, they frequently lack the visual feedback that might help you set ...

Continue reading

SQL Trick: Pseudo-Identity Column After-the-fact

Did you ever wish you could add a SQL Identity column (or, perhaps, just a sequence number) to a table after-the-fact? Perhaps the table has been populated elsewhere in a stored procedure or by some other method and data was not available (or ...

Continue reading
Tagged With: SQL Server