Case TSQL statement in WHERE clause causing scanning (ss2005)

AND 1 = CASE
WHEN @IncludeFreight='Y' THEN 1
WHEN (ItemNumber NOT LIKE '9898%') AND (ItemNumber <> 'FREIGHT') THEN 1
ELSE 0
END

The above is an snippet from a “catch all” query, I uncovered it in one of our reporting SQL statements it, was declared “WITH RECOMPILE”  as it should, on order to properly optomise, but the query was refusing to finish executing on the 4.5million rows in the table involved. It lots of IO wait states on our table.

It seems that the query optomiser just couldn’t handle the case in the where clause with parameters.

Changed it to the following and it executed in seconds.

AND ( @IncludeFreight='Y'
OR (@IncludeFreight='N' AND ItemNumber != 'FREIGHT' AND ItemNumber NOT LIKE '9898%'))

Most of the theory behind this behaviour can be seen in this article: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Finding execution plan of executing query

If you have ever had a query that won’t run to completion, try this…

Captured to protect the information and as personal quick reference from Viewing the Execution Plan of a Running Query in SQL Server

  1. From SSMS find the session id of interest
  2. SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = {sessionid}
  3. SELECT query_plan FROM sys.dm_exec_query_plan (0x06001D009FE38431400399D4000000000000000000000000);
  4. Save the results as from above as .sqlplan and then open the file in management studio

There you go! Job saver!

.image

Dynamics GP Visual Studio Addin - Open Form Parameters

Opening a native GP form from VS addin

This post covers a self help pattern, to assist developers wishing to open a native GP form from .NET using Visual Studio Tools For GP addin.
An example is needed to work with, say it was required to open the Manufacturer’s Item Number Maintenance form from our .NET code:

image 

This form is normally only available from the Item Purchasing Options Maintenance Form but in this example it is required to launch it from our .NET code.

Identify the form name

Using the title caption from the form, go into Tools>Customise>Modifier to find the name of the form, or use the resource tool found under Tools>Resource Descriptions>Windows

IF using the resource tool, the product should be selected to which the form belongs, in this example Microsoft Dynamics GP, set Series Inventory, set View by display name.

image

Here we can see its called IV_MFG_Item_Nmbr_Mnt internally. Forms have display and internal names.

By typing in Visual Studio the window can be located by following intellisense, starting at the dictionary it belongs to (MicrosoftDynamicsGP) and taking out the underscores from the Dex name found above as it is typed.

Once the form is located, put a period after the form name to see what procedures it supports, typing “open” flushes out the procedures of interest.

image

It can be seen that to open the form, this form uses: OpenIvMfgItemNmbrMntProcedure

Intellisense also guides as to how to call it using  _Instance.Invoke
image

However, a familiar issue is hit at this point. The open procedure requires two parameters passing. What are the two parameters that intellisense is asking for? Luckily help is at hand, from the tooltip (see image above),

Invokes form procedure “Open_IV_MFG_Item_Mnt” of form “IV_MFG_Nmbr_Mnt”

Thus recalling that all that is happening here is a call down to procedures in the underlying dexterity code, we can find what we need to call this by checking the Software Developer Kit (SDK).

You of course have the GP SDK installed? – if not get it now…

Using the SDK to find parameters

Searching on the SDK folder, C:\program Files\Microsoft Dynamics\GP11.0 SDK\Content   for the start of the procedure name “Open_IV_MFG” a hit is returned in a file called “CoreForms_1100.txt”

Opening CoreForms_1100.txt, and search for the same term. The following text is found in the document …

------------------------------------------------------------------------
INVENTORY FORM PROCEDURE:  Open_IV_MFG_Item_Nmbr_Mnt
    of form IV_MFG_Item_Nmbr_Mnt
------------------------------------------------------------------------
in                       'Item Number'            l_item;
in                       'Item Description'       l_Description;

So the two parameters needed are Item Number and Item Description.

So now we call it like this:

MicrosoftDynamicsGpDictionary.IvMfgItemNmbrMntForm.OpenIvMfgItemNmbrMntProcedure._Instance.Invoke
("testpart", "testpart description")

Compile, deploy and you should find the form opens with the item selected!

 

SQL Sever Management Studio Auto-save / Auto-Recover location

My SQL Server Management Studio (SSMS) rarely prompts to auto recover unsaved work. It is possible to get back “lost” work. Anything not saved is stored in the location below even if I have not created a solution. Hence by opening these ~AutoRecover files it is possible to get your work in progress back from the dead.

Sometimes this location or folder does nt exist when I want to recover after a windows update restart. By right clicking on the folder above and selecting previous versions in windows explorer, I get my auto saved files back…

I usually work with multiple SSMS instances open (multitasking on operational and development issues) and wonder if this may be the reason I never get prompted on opening SSMS when there are files that can be recovered?

 

C:\Users\{user}\Documents\SQL Server Management Studio\Backup Files\Solution1

image