Simulate DEX_ROW_ID in a SQL View Using ROW_NUMBER() – #MSDYNGP


I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).

Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.

Dex View

Everything looks perfect till you actually see below error messages at runtime:

Dex View - Error 1

Dex View - Error 2

Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.

So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:

ROW_NUMBER USAGE

 

Definition of ROW_NUMBER() can be found here: ROW_NUMBER (Transact-SQL).

A simple yet powerful SQL function has given me the power to do what I wanted in no time. Oh, and my custom lookup referring to this view is working like a charm. Users are happy and so am I.

VAIDY

Delete a Company in Microsoft Dynamics GP – Compatible With GP 2013


We have a SQL script named ClearCompanies.sql, which is available on Customer Source or Partner Source. This script removes all references to those companies that are not available in SQL Server, but pretty much exist in GP records.

It’s an all important script for all implementers, developers and consultants. Now this script has been updated to cater for also GP 2013. I had not used this script for a long time, so never realised it till today. This is particularly important as GP 2013 now support multi-tenant architecture (multiple GP System DB on same SQL instance).

You can download this script from here (provided you have a Customer Source / Partner Source account): ClearCompanies.sql.

VAIDY

SQL Server Uninstall: Removal Architecture Mismatch Error


When I was trying to uninstall SQL Server 2008 from my machine, I received the following error message:

Snip20140106_5

 

At a glance, this error may seem to be something critical, but it is not. It’s something quite silly to be honest. When you install SQL Server 2008 on a 64 bit machine, it installs SQL Server for both x86 and x64 compatibility, leaving two separate items under Control Panel -> Programs and Features, as shown below:

Snip20140106_4

 

If you try to uninstall by clicking on first one (above 64-bit one), then you will get this rule mismatch error. You must select the second one (64-bit one) to successfully uninstall.

When I selected the appropriate one, my uninstall validations passed without hassles.

Snip20140106_3

 

Happy troubleshooting…

VAIDY

syExcelReports Table & SQL Server Native Client 10.0 – Thanks Aaron Berquist


While upgrading our GP from GP2010 SP3 to GP2013 SP1, DYNAMICS database upgrade stopped unexpectedly with GP crashing without any information for me to debug.

But it stopped exactly at table syExcelReports and crashed. Without spending anymore time, I just went and checked my ODBC DSN setup, as explained by Aaron Berquist on his blog High Dynamics Range. Expectedly, the ODBC DSN driver was not pointing to SQL Server Native Client 10.0.

For some reason, GP fails to process upgrade further if your DSN driver is NOT native client 10.0 or above.

Those who upgrade your GP to latest version, please check DSN before you start database upgrade using GP Utilities.

VAIDY

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


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?


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

Move Expired SOP Quotes To History – Leslie Vail


Leslie Vail has posted an article at a time when I am currently working on closing down thousands and thousands of SOP quotes which users failed to close down. It’s about a SQL Script which moves all expired SOP quotes to history.

(Some really lame) reasons I use to hear for not closing down quotes:

1. We don’t know when that quote would be materialised.

Seriously? Gotta be kidding me. If it’s a quote of age 2+ years and you still don’t know whether it would get materialised or not, then something is wrong fundamentally.

2. If it’s voided, we won’t be able to copy the line items of that quote.

Wrong. You can. Copying line items from an SOP document is very much possible, even if it’s transferred or voided.

3. We don’t know whether the comments that we had added specific to that quote would be available for us to reuse it.

Answer is a positive YES. You can reuse the comments, because comments are NOT stored on that document but on Comments Master. Just select the ID and there it is. Unless, you have edited that comment on the document. But again, come on, it’d take 5 mins for you to inquire the voided one, copy the comment text and paste it on new one.

And so on…

VAIDY