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.
VAIDY
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:
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 ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects)
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.
VAIDY
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]
UNION
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:
DECLARE @TABLE_NAME TABLE
(
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, …
FROM @TABLE_NAME
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.
VAIDY
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?
VAIDY
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.
VAIDY
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”.
VAIDY
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.
VAIDY
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:
UPDATE DYNAMICS..AAG00104
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.
VAIDY
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.
VAIDY
Dynamics GP - Learn & Discuss 