Analytical Accounting – Budget Vs. Actual

Siva has added one more to his awesome list of AA posts. Check out his SQL View to analyze AA Budget Vs. Actuals.

I am sure this is going to delight people across world who use AA and are dying to see some kind of ways to enhance their AA analysis capabilities.



Analytical Accounting Menu Master Table (SY07110) Records Multiplying – Bug & Workaround

This one is another SY07110 (syMenuMstr) table issue.

For the past 1 week, users were complaining about GP slow down as soon as they select a company and click OK. The delay was anywere between 30 seconds to 1 minute, which is quite huge, considering the fact that GP is suppose to get initiated within 3-5 seconds maximum.

We have two different launch files; those users who are not suppose access AA and those who access AA. This delay was reported by only AA users. And that eased my debugging task out.

Troubleshooting started by taking a SQL Profile exactly at the time of selecting a company and clicking on OK. After painfully long time, GP got initiated with all Menu and other startup objects. I stopped SQL Profiler and noticed the following piece of SQL query being executed for as many as 143 times, which in total resulted in 4730 records:

The above query is same except some of the fields. But all these SQL SELECTs where targeting the same product; AA (CmdDictID = 3180).
Quite shockingly, I had 4730 records in SY07110 for Dictionary ID 3180, i.e. AA.
So how to solve this? Simple. I took a backup of this table (just for precaution) and executed the following SQL query:
WHERE CmdDictID = 3180
It removed all 4730 records for AA module. I then launched GP. Bingo. It’s getting initiated in as long as 3 seconds.
The story does not end here. For the first time SY07110 table gets inserted with 33 records for AA module. As and when a user launch GP, with AA module, it inserts exactly ONE additional row onto SY07110 table.
Now this certainly looks like a bug.
So what’s the solution? Time being, I have to write a scheduled SQL job which will execute the above DELETE query on a weekly or daily basis.
I am not sure who else is currently facing this issue. If any of you people have this issue, then at least you have a workaround.
I am planning to raise a Support Ticket with MSFT team if no one out there gets back to me with a solution and make sure that this is not a bug as such.

Getting Month Numbers between two dates in T-SQL

I literally had to spend around an hour to crack this code, with some little help from my best geek friend, GOOGLE, of course.

Issue is this: I wanted to get Month in Numbers (Jan = 1, Feb = 2, etc.) that exist between two dates. For instance, if my date range is 1-Jan-2011 to 31-Mar-2011, then I should find and retrieve the months Jan, Feb and Mar as 1, 2 and 3 respectively.

After much much toiling and reading some stuff online, the following is what I got for myself:


;WITH Numbers (Number) AS
SELECT MONTH(DATEADD(MONTH, Number – 1, ‘2011-01-01’)) Month_Number
FROM Numbers
WHERE Number – 1 <= DATEDIFF(MONTH, ‘2011-01-01’, ‘2011-03-31’)


IMPORTANT: Make sure that you have more than 12 records in sys.all_objects table, which by default SHOULD have.

I hope this code piece is useful for some who have got similar requirement.


T-SQL "UNION" Vs "TABLE variable"

Very recently I learned this. And what a change it has made to all of my SQL based projects! I am sure people who are hardcore SQL programmers would already know this. Thought of sharing this for those who would want to know something interesting.

I had a SQL Stored Procedure that is about to retrieve records of a same table from two different databases. The usual approach was something like below:

SELECT [column_name_1], [column_name_2], [column_name_3], …
FROM [db_name_1]..[table_name]


SELECT [column_name_1], [column_name_2], [column_name_3], …
FROM [db_name_2]..[table_name]

This was working till these tables contained records less than 30000 rows. And it started taking significantly unacceptable time (like 10-15 seconds). I then thought of creating a temporary table and dump records from one DB then from other. There is a potential issue in that, in the form MULTI-USER environment. When two different users invoke this stored procedure at the same time, this temporary table must cater for both. I was not really sure about how I can address this.

Then came this idea of using TABLE VARIABLE. It’s like a Structure in C++. This is how I modified my stored procedure:

Column_1 DATATYPE,
Column_2 DATATYPE,


INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT [column_name_1], [column_name_2], …
FROM [db_name_1]..[table_name]

INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT [column_name_1], [column_name_2], …
FROM [db_name_2]..[table_name]

SELECT Column_1, Column_2, …

After this, the program started taking around 2-5 seconds with more than 50000 records to handle. Not to forget, this program contained several grouping and calculations.

I don’t have to worry about delay and also TEMP TABLE issues that are potentially disastrous. I am still not sure how this is handled in terms of memory allocation. What happens after this program completes it’s process, is also a factor that I am yet to figure out.

But, so far, I have found this as one of the best methods in recent times.


Changing a Smartlist Favorite’s visibility thru’ SQL

Warning: Method shared in this post is NOT supported or advised by Microsoft team. This article explains a SQL method and therefore the intended audience is a GP and SQL Consultant / Developer / Administrator.

Couple of Smartlist Favorites created by an end user, but created it with a wrong visibility: Visible by User ID. What this user should have selected is: Visible by System. Since these favorites are suppose to be accessed by all Users across all Companies.

This end user is currently out and even as SA I could not access the Smartlist Favorites. Easiest and direct approach is to reset this user’s password, modify the smartlist favorite and notify him of the new password. Once this user is back, he/she would change the password.

But, is there any other simplest method for someone like me? A GP & SQL Administrator? Of course, I do.

I can modify a Smartlist Favorite using a simple SQL UPDATE statement, targeting the Smartlist Favorite tables ASIEXP81 and ASIEXP86. Display Name for these tables are Smartlist Favorite Master and Smartlist Favorite Columns Master respectively. Both tables are in DYNAMICS database.

Below are SQL statements that I used to change the visibility:

UPDATE DYNAMICS..ASIEXP81 SET ASI_Favorite_Save_Level = 1, USERID = ”, CMPANYID = 0 WHERE ASI_Favorite_Name = [Favorite_Name_Given_By_That_End_User]

UPDATE DYNAMICS..ASIEXP86 SET ASI_Favorite_Save_Level = 1, USERID = ”, CMPANYID = 0 WHERE ASI_Favorite_Name = [Favorite_Name_Given_By_That_End_User]

In above statements, field ASI_Favorite_Save_Level is the Visibility Type that we set up when we create a Smartlist. Value 1 denotes the first option in that Drop Down, which is SYSTEM. Since I have to set visibility to all users across all companies, all I have to do is to set USERID as an Empty String and Company ID as ZERO.

This will change a particular Smartlist Favorite’s visibility as required.

Simple, isn’t it?


Last Sale By a Customer & Item – Victoria’s SQL View

Victoria‘s latest SQL view retrieves the last sale by customer and an item. With little tweaks here and there, this SQL view will serve us immensely to show various information pertaining to Customer and/or Item sales.

Thanks Victoria for this view. This has come especially when I am working on some dashboards and reporting. It’s surely going to help me deriving some important reports.


GP & Mixed Mode Authentication

I was attending a LinkedIn query from a User who was facing issues with GP Utilities & DB Creations. Upon checking  his environment by a remote-controlled session, I identified that it was SQL Server Authentication issue, which was denying him to even log on to GP Utilities and move further.

It’s very important to understand that GP Application supports only SQL Server Mixed Mode Authentication. What if we accidentally selected (rather left changing the default SQL Authentication option which is) “Windows Authentication”?

With SQL Server 2008 (I have not checked this in SQL Server 2005 and earlier versions), we have a very simple way to change it.

1. Open SQL Server Management Studio (SSMS).
2. Login to the respective SQL Server Instance.
3. On the left pane, right click on the Instance Name and select “Properties”, as shown below:

4. Under the Security Page, select “SQL Server and Windows Authentication Mode” as shown below:

5. Click on OK and restart the SQL Server service to effect this change.

Earlier I remember, I had to uninstall and reinstall SQL Server instance had I selected the wrong authentication mode. I think the version was SQL Server 2000.

With SQL Server 2008, you will be saved with an hour or more.

Point to Remember: If you enter “sa” and it’s password correctly and still you are invited with a message that reads like “Login failed”, then make sure that SQL Authentication mode is “SQL Server and Windows Authentication Mode”.


GP2010 – Sales Charts & KPIs – Top Items By Sale Amount – Stored Procedure Bug

I stumbled across a bug (of sorts) in GP2010 SSRS Charts & KPIs for Sales Module.

GP2010 has got default Charts & KPIs designed in SSRS and we have quite hands full of Charts & KPIs which covers most of our basic requirements. One module which is very good in terms of coverage is Sales. Under Sales, we have a Chart & KPI called Top Items By Sale Amount.

When I ran this report to view the results, I got the following error message:

The error message mentions that there is an extra bit of string data that’s getting forcefully truncated due to size restrictions. This clearly indicates a SQL Error Message. I ran the SQL Profiler furthering the probe. And the issue was rounded in to a Stored Procedure which is run to extract the needed data for this Chart & KPI, which is seeSOPTopTenSalesItemsByPriceMetric.

In that stored procedure, a temporary table is created with a column named ItemNumber which is of size char(18). We all (Consultants/Developers) know that, in GP, size of a Item Number is 31 in SQL.

So, this SSRS Chart & KPI will fail if any Item Number exceeds the size 18 in variably, which is a bug in this Stored Procedure.

FIX: I ALTERed this stored procedure to resize this particular temporary ItemNumber column to char(31) and report retrieved the details excellently.


Analytical Accounting Error – After Restoring a Company Database

CAUTION: This post is NOT intended for SQL Administrators who are not familiar with GP Table Structures. The following script may have serious implication on certain GP environments, so please use it at your own risk. This method is also not supported by Microsoft for obvious reasons.

This message was quite annoying when I created a Test Company in GP10 and restored a Production DB for R&D and Testing purpose.

Analytical Accounting windows didn’t get opened and was throwing the following error message:

There were several articles which cautioned / warned off from restoring only Company DB, which I am quite aware of. Because restoring another Company’s DB on to a Test Company will not have any issues unless it has got some products, like AA, depending heavily and real-time on DYNAMICS DB.

After a bit of R&D here and there, this is what I did. There is a table called AAG00104 in DYNAMICS Database, which is Analytical Accounting Company Setup table. I wrote an UPDATE on this table for the newly created company record as below:

SET aaCompanyStatus = 4, aaRelease = 10 
WHERE CMPANYID = [Test Company ID Integer value]

This obviously worked in my environment and released the Test Company for AA usage.

As I mentioned at the start of this post, be cautious and do not venture into this unless you are sure of the consequences.

I am also not quite sure whether we have any fix for this issue, as I could not get one from CustomerSource or PartnerSource. I may have overlooked. Please do write a comment if we already have a script for this issue.


GP Blogs for Useful SQL Views

We are now having three blogs helping us out in SQL Views for varied needs.

1. Victoria Yudin’s Blog
2. Mark Polino’s Blog
3. Ron’s Blog – Real Life Dynamics User

To my best knowledge, the above are the active blog till now and continue to enrich us with more SQLs.

Do follow these blogs without fail.