Category Archives: SQL Profiler

Quick Tip: Debugging Visual Studio Tools – Developing For Dynamics GP

Posted on by

Awesome tip up there on Developing For Dynamics GP blog on how to debug VS Tools For Dynamics GP.

I am late in blogging about this post. But NOW is the time, as this post just saved my 1/2 day. Seriously. Not kidding. I was stuck with one SQL query execution. There were records to be retrieved, but my code didn’t. After 5-10mins of frustrated troubleshooting, I remembered this post. I should have done that immediately then, but I didn’t.

I setup my VS Tools project to launch GP as instructed on this post and within 2 minutes I could find the issue.

This one deserves to be there on Microsoft Dynamics GP Cookbook (technical).

VAIDY

Where is my Custom Reminder window to add reminders to my GP home page?

Posted on by

I was reported with a very vague issue: user could not create a custom reminder from a smartlist. He received an error message as follows:

Snip20130417_3

Basically, below is the window which user is trying to open:

Snip20130417_5

Ideally, you would think that this window is a part of Microsoft Dynamics GP dictionary. But it is NOT. It is a part of Smartlist dictionary.

But trick here is, you cannot see this window listed on when you try to assign this window a particular Security Task using Security Task Setup window. Then, how? How would I give access to this window?

After around an hour or so, with several script logs and profilers misleading me, I found one interesting statement on SQL Profiler trace, that was executed when the user tried opening that window:

Snip20130417_6

Exactly after this SQL statement, the above error message was thrown at the user. Which means, I must focus my troubleshooting efforts on this table; DYNAMICS..SY10000. This table is simply called User Security.

When user tried to open Custom Reminder window, system checked this table and see whether this user has got access to a window whose resource ID is 1452 in dictionary 0 (which is nothing but product Microsoft Dynamics GP) inside company ID 1 (which is my production GP company).

I was always thinking about something in Smartlist (which is, without any doubt), but system was checking something else in a different dictionary altogether. I wanted to see the window in Microsoft Dynamics GP dictionary which is of resource ID 1452. Opened the DYNAMICS.DIC on Dexterity and checked it, only to realise with disbelief that it was referring to following window:

Snip20130417_7

I did not understand first. Why would it check something totally irrelevant from what user wanted to open? Why would it check access to this window, when opening a window in different dictionary? I have no answer to these questions.

But I just thought I would take a chance. I checked this user’s security task setup and found that Reminders window was not assigned. I assigned that window for this user as shown below:

Snip20130417_8

Tested whether he could open Custom Reminder. To my utter disbelief, IT DID.

So, if somebody is facing same issue and has already lost almost all your hair, here you have, a solution that would bring upon peace.

VAIDY

Fixed Assets Depreciate Button Disabled For User – Solution

Posted on by

This issue was a bit tricky but not THAT tricky, though.

One of the users reported to me that he was not able to run depreciation because when he opened the Depreciation Process Information window (Microsoft Dynamics GP -> Tools -> Routines -> Fixed Assets -> Depreciate), the Depreciate button was disabled. Below was the screenshot that he sent to me (disabled button is highlighted in RED box):

Depreciate Window

 

Reason: After quite a bit of analysis (Dex Script Log & SQL Profiling using SDT), I found that there is a table where FA module temporarily records the depreciation activity initiated by any user and will retain that record the process is complete.

User confirmed to me that last time when he ran the depreciation process, it got stuck at the time of printing the results.

Solution:

The table which records the activity is FA40203 (display name: Book Setup for Depreciation). The table contains three fields: User ID, FA Book Index & FA Book ID.

I took a backup of this table (a simple T-SQL SELECT * INTO…) and removed that stranded activity record for that user.

User confirmed that he could then start depreciation process.

Its always good to start a day with some brainy troubleshooting. But not certainly good for users to start with an issue, though.

VAIDY

Cashbook Bank Transfer Error: Date entered is not a current period for checkbook [Checkbook ID]

Posted on by

This was a menace for the past 4 days, to say the least.

I was reported of this issue, where a user when trying to enter a bank transfer for a checkbook, on a date in previous month, he got this error message (in the title of this post) as shown below:

The above message occurred only for one particular checkbook and not for others.

First step was to ensure the fiscal period was not closed. It was not.

Second step was to ensure that they had not run the reconciliation for this checkbook, so genuinely it had closed the period. It was not.

Third step was to run check links for Checkbook Master, to ensure that things are fine with Checkbook ID master record. It was perfect.

Fourth step was to take SQL Profile and see where exactly this message pops up. When I took twice, the results were different. Due to Analytical Accounting. But that I never got to know since, my focus was more on CB tables.

I referred to the ONLY AVAILABLE forum post, Date Entered is not for Current Period for CashBook, which was way back in year 2010. No solution was provided though.

I decided to take the SQL profile again, but this time without Analytical Accounting to avoid extra lines on the trace file. I took the profile twice and both matched line by line.

The last table where the buck stopped was CBINT605 (Reconciled Amounts) on DYNAMICS database. When I queried this table manually, following was the result:

As you see above, there was a record for this checkbook ID, with a date 30-Apr-2012. Due to which, if you enter any date on or before 30-Apr-2012, it fails.

I checked the user again to ensure that he had not run any reconciliation for this checkbook. He assured that he didn’t, but he also mentioned that he was demonstrating this process to a user, but did not complete it. Bad. Demonstrating on a live company is very bad.

I took a backup of this table and then deleted this record.

Now, my checkbook accepts any date for it’s bank transfer entry.

MORAL OF STORY #1: Messages never lie. If there is a message, it will most probably be correct.

MORAL OF STORY #2: Never ever do any R&D on live company. Never ever demonstrate anything on a live company. That’s why test companies are for.

VAIDY