Dynamics GP 2010 R2 Database SDK - Siva

I was in fact awaiting Sivakumar’s post about the new section that he has been working on, so I can post my thoughts here. 

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, use this section to your betterment and very importantly share your thoughts with (and thank) him for his work.

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

VAIDY

GP Homepage Layout Mass Update - Thru' SQL

I love SQL. Simply because, it eases lot of pain in doing some redundant work.

When you create a user, by default, GP creates the following segments on his/her homepage:

1. To Do (Reminders/Tasks)
2. My Reports
3. Microsoft Outlook
4. Quick Links
5. Connect (links to Online Resources such as CustomerSource, Forums, Blogs, etc.)
6. Metrics (quick dashboards for users)

All of the above work perfectly, but at some stages (especially when you are working on a GP Terminal Server) some of these components would halt GP for a while, not allowing users to do anything.

One classic example is Microsoft Outlook. We recently migrated our Email Services to Microsoft Office 365. For those users who use Thin Client to on their day-to-day job, Terminal Server is THE destination. Quite obviously, Terminal Server has got Outlook installed.

Before Office 365, Outlook profile used to connect to our on-premise Exchange Server. After migration, we had to change the profile from Exchange Server to Office 365 hosting server.

Whenever a user logs on to Terminal Server and opens GP, GP will halt for at least 3-5 mins to get this Outlook configured. I am getting calls from users (yes, even now) that their GP session does not work and they are not able to do anything. Following icon shows up on their taskbar when this happens:

It didn't take me much time to realize that all these users had added Microsoft Outlook on their GP homepage.

"Do we really need Outlook on GP homepage?" is a question to be asked to oneself. Yes, Microsoft has integrated your ERP with one of the most used product by maximum business users. Agreed. But is that alone a reason for you to use it? Let's not get into that argument in this post, anyways.

I had to take a stand and remove Microsoft Outlook from those users' homepage. But then, I don't know, right now, about how many users out of 100+ total users have added Outlook on their homepage.

Certainly not without the help of a SQL query.

The table where this information has got stored is SY08100 (Technical Name: syHomePageLayout). In this table, the column SectionID denotes the sections which are available for a user to add to his/her homepage. Following is the legend:

1 - To Do (Reminders/Tasks/Cues)
2 - Microsoft Outlook
3 - Metrics (quick dashboards for users)
4 - My Reports (links to users' favorite reports and smartlists)
5 - Quick Links (links to users' frequently used windows)
6 - Connect (links to Online Resources for GP such as Forums, Blogs, etc.)

Typically, for each user, this table will contain 6 rows, each row denoting each of above section. SY08100 also contains a column named Visible. This denotes whether or not to show a particular section on GP homepage.

For instance, if user SA has setup his GP homepage to show To Do, My Reports & Quick Links to show up, the following will be SY08100 records:

So, if I want to hide Microsoft Outlook from all users' homepage, I just have to execute the following SQL query on DYNAMICS database:

UPDATE SY08100 SET Visible = 0 WHERE SectionID = 2

We must also understand one thing. Users can always add this again thru' Customize this page... option. So this is NOT A PERMANENT SOLUTION.

Whenever there is a necessity, as in my case, you can certainly rely on this SQL method to do a homepage layout mass update.

VAIDY

Test Company Posting Journal File Destinations

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

SQL Tip: Find Table(s) Having A Particular Column

This SQL query is my savior for a long time now. Thought I would just share it with all budding SQL developers.

Q: How to find the table(s) which contain a particular column which I know?
A: Below is the query that would do the trick.

SELECT name 
FROM sys.objects 
WHERE object_id IN 
(
SELECT object_id 
FROM sys.columns 
WHERE name = [columnname] --Column which you would want to locate
)

This query will return all tables which contains the specific column. And this query also would save your time immensely.

VAIDY

Analytical Accounting Class Maintenance Error: This record has been created since your attempt to create it. Changes won't be saved.

WARNING: This post explains a method which is carried out in SQL and also affects an active table record. Consultants / Developers / End Users, who are not aware of GP & SQL processes, MUST NOT carry out this without an experienced consultant's / developer's supervision or assistance.

This one's another nightmare. I have no idea about how this happened, but now I know how to rectify this without affecting any of the system's normal behavior.

When a user tried creating an Analytical Accounting Class, the following error message was thrown at and the record did not get saved:

Analytical Accounting Class Master table is AAG00201 in SQL Server. It is also aaAccountClassMstr in AA dictionary. When I queried this table, it did not give me any indication that something could be wrong there.

Upon taking a SQL Profiler (I trust SQL Profiling to my life), I found that the flow stops at a table DYNAMICS..AAG00102, which is  (table AAG00102, database DYNAMICS) and technical name of this table is aaSyTableRow. When I queried this table, it was quite interesting. Below is a snapshot of the table records:

Logically, the table, DYNAMICS..AAG00102, stores the ID information for each type of record that is stored in Master tables. For instance, in our case, Account Class Master table is AAG00201. See the highlighted record in the above snapshot.

aaTableID: 201 (denotes Account Class Master table)
CMPANYID: 1 (denotes my Production Company ID)
aaRowID: 29 (denotes Last Class ID that was generated when I created Account Classes)

ISSUE: My last Account Class ID at the moment is 30. This is NOT the same as it is in my AA System Table Row (DYNAMICS..AAG00102) table. When the user tried creating the new AA Account Class, it took the aaRowID from AAG00102 table, which is 29, and that clashed with my Account Class Master table, since I already have one record with Class ID 29.

FIX: I realized that I must reset this aaRowID to the last generated Class ID, which I can get from Account Class Master table.

I took a backup of the table DYNAMICS..AAG00102 (SELECT * INTO ...) as a precautionary method. Then I executed the below query to reset it:


UPDATE DYNAMICS..AAG00102 --AA System Table Row Table
SET aaRowID = 30 --Last generated class ID in my case
WHERE aaTableID = 201 --Denotes Account Class Master table
AND CMPANYID = 1 --Denote my production company ID

Above query reset it to correct *Next Class ID*. User is now able to create new class IDs.

Issue is resolved, but how aaRowID on DYNAMICS..AAG00102 was not incremented when last Account Class ID got created, remains a mystery.

I am unable to recreate that scenario so far.

VAIDY

Perils of Using of DEX_ROW_ID on Customization / Integration - David

This one is certainly a nasty issue, if we are going to use DEX_ROW_ID value on customization or integration.

I have personally fallen into a trap by using DEX_ROW_ID on my customization. But now, it's all wisdom out of that bad experience.

Only time when I normally depend on a DEX_ROW_ID is to sort records to understand which record was entered last in the table. That itself may not be always correct. So we may have to add other logical fields that are relevant to a particular record.

For instance, if I have to list out transactions entered on a particular day and check in the order in which the records are created, I would use the Transaction Date and DEX_ROW_ID as SORT ORDER.

There are many other reasons, but restrict yourself to use DEX_ROW_ID only for selecting records and analyze it.

VAIDY

Using SQL to retrieve number of records in a table - David

David gives an alternate SQL option for the simple COUNT(*) and explain how it improves the performance as against COUNT(*).

You may have to go thru' the comments as well; some comments discuss the post further.

VAIDY

User Access Setup Error: GPS Error RevokeUserAudit: 58

I faced this error message from the time I upgraded to GP2010 R2. The error message is thrown when we revoke access to a user on a company. Below is the exact sequence of error messages that I received:

Since I had not doing this frequently (it's maximum 3 users in 6 months) and also the purpose of revoking access was working without any issues, I did not consider this as a harmful message.

Yesterday, I had to revoke access to users for an existing company due to some maintenance. Minimum of 10 users and I started receiving this error message each time I select a user and revoke access. It's then I decided to check this out and clear it once for all.

I could get only one link with exact error message, which I found on our Community Forum. User Access Setup Error: GPS Error RevokeUserAudit: 58.

Let me explain what I did to get rid of this.

1. Check the second error message. It says it did not find the stored procedure *mxCallRevokeUserAudit*.

2. I checked whether this stored procedure is available on any other company that I had on my GP environment. I could not find one on any company.

3. I was intrigued by the name of this stored procedure since, it's not similar to any Microsoft Dynamics GP SQL object naming convention.

4. I then launched GP and opened Tables Resource Description (Microsoft Dynamics GP -> Tools -> Resource Descriptions -> Tables).

5. Since the message said something about *Audit*, I thought I would first check with *Audit Trails* module. To my surprise (and joy, of course), Physical Name of all tables started with *MX*.

6. I then concluded that the SQL Stored Procedure what I am looking at, is also of *Audit Trails* module.

Now I could not do much with that, since I could not find that stored procedure anywhere. I checked my GP licenses and it did not cover *Audit Trails* module at all.

When I upgraded to Microsoft Dynamics GP2010 R2, I had selected this module also for installation. Since this module is not covered under my license, it installed the dictionary but it did not create the SQL procedures (that's my assumption and I may be wrong).

The only option that was left; to remove this product from my environment (by removing DYNAMICS.SET file contents alone and not the dictionary itself).

This removed that now (in)famous error message.

VAIDY

Replicate Multi-Currency & Exchange Table Access Information From Existing Company

I had to create a new company with Functional Currency being same like existing production. Quite obviously, all Exchange Table IDs are going to be exactly same like in existing production company.

There were around 25 currencies that we have setup. Under normal circumstances, I have to open Multi-Currency Access (Tools -> Setup -> System -> Multi-Currency Access) and select each currency & respective exchange table ID and activate it. 25 times 3 mouse-clicks.

I then decided to tread SQL way. Following is the script which did that 75 clicks in less than a second:

/* MC60100 - Multi-Currency Access Table */
INSERT INTO DYNAMICS..MC60100 (CMPANYID, CURNCYID, INACTIVE) 
SELECT [new company id], CURNCYID, 0
FROM DYNAMICS..MC60100 
WHERE CMPANYID = [existing company id]


/* MC60200 - Exchange Table ID Access Table */

INSERT INTO DYNAMICS..MC60200 (CMPANYID, EXGTBLID, INACTIVE) 

SELECT [new company id], EXGTBLID, 0

FROM DYNAMICS..MC60200 

WHERE CMPANYID = [existing company id]

To know the company ID for each of your companies, you can query the table SY01500. Below is that query:

SELECT CMPANYID, INTERID, CMPNYNAM

FROM DYNAMICS..SY01500 

ORDER BY CMPANYID

Happy querying.

VAIDY

Don't update "Batch Status" thru' SQL when users are logged on

A user complained about a batch not being able to be posted. The error message is something like this: This batch is currently receiving transactions.

Normally when a user complains about something like this, a GP Administrator or Consultant's immediate step is to check what the *Batch Status* field holds in SY00500 (Batch Header) table. The legend for this field says:

0 – Available - BATCH_AVAILABLE
1 - Marked to Post - BATCH_POSTING
2 - Marked to Delete - BATCH_DELETING
3 - Marked to Receive - BATCH_RECEIVING
4 - BATCH_DONE_POSTING
5 - BATCH_PRINTING
6 - BATCH_UPDATING
7 - BATCH_POSTING_INCOMPLETE
8 - BATCH_PRINTING_INCOMPLETE
9 - BATCH_UPDATING_INCOMPLETE
10- BATCH_RECURRING_INCOMPLETE
11 - BATCH_POSTED_WITH_ERROR

And quite apparently, in my case, it was 3. i.e. BATCH_RECEIVING. It's very easy to update this field back to ZERO that denotes BATCH_AVAILABLE, so user can post it.

I could have also done the same thing. But I did not. I just wanted to make sure that this batch indeed did not receive any transactions. How do we identify whether a batch is receiving transactions or not? That I will address in another post.

This one is intended to caution a Consultant or Administrator NOT to update *Batch Status* thru' SQL for any batch which is marked as BATCH_RECEIVING when users are logged on.

It would break a valid transaction being saved in this particular batch. When a batch receive transactions, GP locks this batch from getting posted only by updating the *Batch Status* field with BATCH_RECEIVING. By changing that from back end, we are risking batch getting corrupted.

Tell the user that he/she has to wait. And wait till users log off from GP. And then check whether the *Batch Status* becomes BATCH_AVAILABLE. In case it is not, then you update it thru' SQL.

It is worth waiting for sometime than trying to clear a mess. I take this opportunity to insist again: SQL is not ALWAYS an appropriate approach for issues that we face in GP.

UPDATE: David has sent me a link, which quite obviously I missed mentioning here. How did I miss this? We do have a way to release stuck batches without logging off users.

VAIDY

Stuck Receivables Transaction due to Incorrect Amounts

Yesterday, I received a support request from one of our users who tried posting a batch full of receivables transactions and received an error message for one transaction whose distribution amounts did not match document amount.

It was suppose to be a simple task to release this transaction for edit, but due to some reason this transaction got stuck in between. It did not come back to it's original batch and quite obviously it did not get posted either due to that error.

Following are the steps involved in releasing that transaction:

CHECKS

1. Check whether the transaction is still in RM10301 (RM Sales Work) table.

In my case it was still in RM10301 table.

2. Check the fields BACHNUMB (Batch Number) and BCHSOURC (Batch Source) in RM10301 table for that transaction.

In my case, BACHNUMB contained the USER ID who tried posting the batch; BCHSOURC contained XRM_Sales instead of RM_Sales, since it was in the middle of posting process and got stuck.

3. Check whether a batch with that user's USER ID is created in SY00500 (Batch Header) table.

In my case, there was indeed a batch with that User ID. Because of which, this user could not open Receivables Transaction Entry window and was receiving the (in)famous error message "A previous transaction level posting... ... ...".

CORRECTION STEPS

1. I first created a Receivables batch (I named it as RM-RECOV).

2. Through SQL Server Management Studio, I updated RM10301 table fields BACHNUMB and BCHSOURC as follows:

BACHNUMB = 'RM-RECOV'
BCHSOURC = 'RM_Sales'

3. Deleted the batch [USER ID] from SY00500 table, to remove user's transaction posting session lock.

4. Tried opening that transaction on Receivables Transaction Entry form. Since the batch number RM-RECOV was updated through backend, it did not obviously update batch information. Due to which, I still could not open this transaction.

5. As a final step, I ran Receivables Reconcile Batch Information (Tools => Utilities => Sales => Reconcile). This step updated the amount from that erroneous transaction on to batch RM-RECOV.

After above steps, user was then able to reopen that transaction and edit it.

Bottomline: SQL approach is not always the only option for consultants to clear such issues. We need to perform some crucial application level tasks that would compliment SQL methods.

VAIDY

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.

VAIDY

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:

DELETE DYNAMICS..SY07110

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.

VAIDY

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 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

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]

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

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?

VAIDY

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.

VAIDY

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".

VAIDY

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.

VAIDY

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:

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