Category Archives: Database

Infamous “Sales distribution amount is incorrect” Error

Posted on by

I received an email from one of my users seeking my help in clearing this issue. While posting an invoice, this error message popped up:

SDE-01

 

Upon running the Edit List for this invoice, I realized that it was due to an imbalance in Markdown Amount and corresponding distribution line. Below was the error report that I got:

SDE-02

There seemed to be Markdown entered on one or more line item(s), but there was no distribution line for that amount. But the issue was not THAT simple and didn’t stop there.

When I ran thru’ all line items, none contained a Markdown. Now that’s the problem. After some minutes of thinking, I realized something must be stranded on header record’s Markdown field, for which GP requires a Markdown distribution line, but since line items do not contain any Markdown, it’s not creating one. Strange.

I decided to query the records from SOP10100 (SOP Header) and SOP10200 (SOP Line) to understand the issue. Below is what I found:

SDE-03

SERIOUSLY…?!?!?!?!

But yes, that was the issue. And most baffling thing is, when I tried to reconcile this sales document, this major mishap didn’t get cleared at all.

Obvious solution for this abnormal situation is as follows:

1. Take backup of this SOP10100 record.

2. Update Markdown fields with ZERO.

3. Reconcile this sales document again to see if the above update had caused any imbalance.

Happy new year and happy troubleshooting…!!!

VAIDY

DBCC SHRINKFILE

Posted on by

There is an amazingly simpler yet highly informative post up on TECHNET portal, which proved very handy this morning.

My systems admin reported to me that our SharePoint server ran out of storage for some reason unknown to him, but he could identify that it was SQL Server program files folder which consumed 80% of storage.

I immediately knew what could be the reason. It’s those crazy log files problem. To truncate and clear these overeating log files, I had to use this command: DBCC SHRINKFILE.

SP-Log-FileGrowth

Once I cleared out all unnecessary file space from these log files, I had to setup the File Growth and Maximum File Size (shown in the screenshot beside):

Never for a DB log file, we should setup the file growth as “In Percent”. This would prove disastrous over a period of time. Instead, always set it up to “In Megabytes” and enter a least sensible value; in my case I had set it up to 5MB.

For a DB Log file, 2GB should be reasonable amount of file space to hold the logs. To learn more about Log files, read this post on TECHNET: The Transaction Log (SQL Server).

We must understand one thing, that shrinking the DB log has got it’s own impact.

VAIDY

SQL Server 2012 Performance Dashboards – Some Real-Time Snapshots

Posted on by

Following are some real time SQL Server 2012 Performance Dashboards, taken from a real time SQL Server 2012 environment:

HOME PAGE

 

EXPENSIVE QUERIES – CPU

 

EXPENSIVE QUERIES – DURATION

 

and much more…

It’s a great set of reports for every SQL Server DBA to understand his/her SQL Server environment.

VAIDY

Microsoft SQL Server 2012 – Performance Dashboard Reports

Posted on by

In March 2012 (precisely 6th March 2012), Microsoft made available SQL Server 2012 Performance Dashboard Reports which can be used to identify whether there is a current bottleneck on the system, and if so, capture additional diagnostic data that may be required to resolve it.

More information on this can be read from the download page: Microsoft SQL Server 2012 Performance Dashboard Reports.

One point worth to be highlighted is that this doesn’t require Reporting Services to be installed.

It’s a very crucial and important tool for any database administrator.

VAIDY

Introducing Microsoft SQL Server 2012 – Free eBook

Posted on by

That’s a great news for those who would love to know more about Microsoft SQL Server 2012.

Microsoft Press has released a FREE EBOOK detailing SQL Server 2012. And it has got 288 pages full of information and it’s free and we have several options of the file type; EPUB, MOBI & PDF.

Get your copy now and know what it is in Microsoft SQL Server 2012.

VAIDY

Dynamics GP 2010 R2 Database SDK – Siva

Posted on by

I was in fact awaiting Sivakumar’s post about the new section that he has been working on. I got the privilege to go thru’ this first before his post went public.

Siva’s new section is all about GP 2010 R2 SQL Objects (Tables, DBs, Stored Procedures, Views, etc.). Like the one we have in MS Word documents that can be accessed from GP SDK, this is also an interesting concept and also very informative for all developers out there, who may not have access to SQL Server on a real time.

Read his post here, Dynamics GP 2010 R2 Database SDK, and share your thoughts with him. 

I am sure this is going to be of immense help for all of us.

VAIDY

Test Company Posting Journal File Destinations

Posted on by

One thing we all must remember while restoring a Live Company DB onto a Test Company DB is, that several places in GP tables, Company ID is stored. We have a SQL Script that will search ALL tables and ALL columns that contain the Inter ID (SQL ID for each GP database) and replace the live DB ID with test DB ID.

This post explains some specific cases where the Posting Journals of all transaction types are destined to a text file. Typically, in a multi-company environment, consultants would setup the path for all Posting Journals with the respective company Inter ID (or any folder name that uniquely identify each company).

I will explain you an ideal scenario, where this poses an issue.

I would restore live backup onto a Test Company and run the SQL script that will replace all live company ID references to test company ID. But my Posting Journals File destination is a simple string value, something like below (I have taken Purchase as a sample series):

:C:Journals/[CompanyID]/Purchase/[JournalReportName].txt

So if my live company ID is, for instance, VMLIVE, then the above path would like this:

:C:Journals/VMLIVE/Purchase/JournalReportName.txt

Whenever I post a purchase transaction, my Posting Journal detail would go and get append on this file, which I can audit at any point of time.

Now, consider that my Test Company ID is VMTEST. When I restore my live backup onto my test company and run the SQL script which replace live company reference with test company ID, everything would get fixed except this. Since the value stored in File Destination field in the Posting Journal Destinations table (SY02200) is NOT JUST the company ID, but the above Filename with Path.

After restoration and I post a purchase transaction on my test company, the posting information get appended on the file JournalReportName.txt on the path C:\Journals\VMLIVE\Purchase, which is WRONG. It’s not just wrong. Your posting journal file gets dumped with test entries as well as live entries. If any client audits the Posting Journal files as part of their internal process(es), then it’s a big trouble.

In such scenarios, where all posting journals are destined to a text file on a path identified specifically by a company ID or name, the GP Administrator must make sure that the field FILEXPNM (File Export Name) on table SY02200 (Posting Journal Destinations) must be properly updated before we post any test entires. Below is the simple UPDATE statement which would fix this:

UPDATE SY02200
SET FILEXPNM = REPLACE(FILEXPNM, ‘[LiveCompanyID/Name]‘, ‘[TestCompanyID/Name]‘)

Where [LiveCompanyID/Name] denotes the value which identifies your Live Company and [TestCompanyID/Name] denotes the value which identifies your Test Company.

VAIDY