Monday, August 30, 2010

Enabling DEXSQL logs with Support Debugging Tool

It must be the Support Debugging Tool week. After the fabulous security article by David Musgrave explaining the inner works of the Resource Information and the Security Information windows and how they aid in finding those pesky security tasks and roles, I thought it would be a good idea to pickup on an old subject that often gets lost in the archives of support: DEXSQL logs.

It still surprises me how many questions are posted on the various community forums asking for guidance on troubleshooting errors in stored procedures, table access errors, and even database connectivity errors. Likewise, it's often forgotten that the main purpose of the DEXSQL.LOG is to capture all interactions between the Microsoft Dynamics GP client and the Microsoft SQL Server database. These interactions can be tailored to the actual ODBC messaging and/or the SQL statements that are executed against the database.

KB article 850996 explains how to setup a DEXSQL.LOG the old fashioned way, this is by editing the DEX.INI. This approach has an inherent problem: it requires that the user exit the Microsoft Dynamics GP system and re-enter to have the settings take place. After all, the DEX.INI is also known as the parameters file and parameters are loaded upon startup of the application. In addition, as the application loads and up to the point of recreating the error, the DEXSQL.LOG file would have already recorded a set of events totally unrelated to the issue at hand.

Note: the DEXSQL.LOG can be edited with Notepad to remove the entries recorded up to the point of an error. The empty file can be saved to proceed with the logging activity.

Support Debugging Tool allows you to create a DEXSQL.LOG without ever having to leave the application.



Furthermore, one of the capabilities I appreciate from the Support Debugging Tool feature is to be able to create logs based on date and time, this is, the log file name will include the date and time the log was initiated. I can even setup my own file name if needed. The following is a sample DEXSQL.LOG:


/* Date: 08/11/2010 Time: 22:53:01
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22190, 2 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22190, 2 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(597450192):*/
{ CALL TWO.dbo.zDP_SVC00998SS_1 ( 0.00000 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(597450192):*/
{CALL TWO.dbo.zDP_SVC00998F_1(NULL,NULL)}

/* Date: 08/11/2010 Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/* Date: 08/11/2010 Time: 22:53:01
stmt(597446960):*/
{ CALL TWO.dbo.zDP_SVC05501SS_1 ( 'SVC' ) }

/* Date: 08/11/2010 Time: 22:53:03
stmt(12353984):*/
{ CALL TWO.dbo.zDP_RM00101SS_1 ( 'AARONFIT0001' ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22218, 2 ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22218, 2 ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:05
stmt(597443728):*/
SELECT COUNT(RETTYPE) FROM TWO.dbo.SVC05501

/* Date: 08/11/2010 Time: 22:53:05
stmt(597446960):*/
{CALL TWO.dbo.zDP_SVC05501F_1(NULL,NULL)}

/* Date: 08/11/2010 Time: 22:53:05
stmt(597446960):*/
SELECT TOP 25 RETTYPE,DSCRIPTN,RETSTAT,Received_Status,Shipping_Status,Close_Status,RETPATH,VNDWARRTYPE,RTV_Type,DEPOTTYPE,ORDDOCID,CRDOCID,INVDOCID,LOCNCODE,ITLOCN,ITEMNMBR,SVC_Discrepancy_Status,SVC_RMA_Invoice_Batch_ID,SVC_RMA_Credit_Batch_ID,SVC_RMA_Order_Batch_ID,SVC_Sales_Return_Index,SVC_COGS_Index,SVC_Scrap_Index,SVC_Repair_Sales_Index,SVC_Partial_Received_Sta,SVC_Ready_To_Close_Statu,SVCSCRAPCOGREV,DEX_ROW_ID FROM TWO.dbo.SVC05501 WHERE RETTYPE = 'CREDIT' ORDER BY RETTYPE ASC

/* Date: 08/11/2010 Time: 22:53:07
stmt(597446960):*/
{ CALL TWO.dbo.zDP_SVC05501SS_1 ( 'CREDIT' ) }

/* Date: 08/11/2010 Time: 22:53:07
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:07
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22244, 2 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22244, 2 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/* Date: 08/11/2010 Time: 22:53:12
stmt(597445344):*/
SELECT COUNT(CUSTNMBR) FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3 /* Date: 08/11/2010 Time: 22:53:12 stmt(597443728):*/ SELECT TOP 25 SOPTYPE,SOPNUMBE,DOCID,DOCDATE,LOCNCODE,CUSTNMBR,CUSTNAME,CSTPONBR,DEX_ROW_ID FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3 ORDER BY CUSTNMBR ASC ,SOPTYPE ASC ,SOPNUMBE ASC /* Date: 08/11/2010 Time: 22:53:13 stmt(597443728):*/ SELECT TOP 25 SOPTYPE,SOPNUMBE,DOCID,DOCDATE,LOCNCODE,CUSTNMBR,CUSTNAME,CSTPONBR,DEX_ROW_ID FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3 AND CUSTNMBR = 'AARONFIT0001' AND SOPTYPE = 3 AND SOPNUMBE = 'STDINV2002' ORDER BY CUSTNMBR ASC ,SOPTYPE ASC ,SOPNUMBE ASC /* Date: 08/11/2010 Time: 22:53:14 stmt(597399424):*/ { CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22239, 2 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(597400232):*/ { CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(597401040):*/ { CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22239, 2 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12387752):*/ { CALL TWO.dbo.zDP_IV40100SS_1 ( 1 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(597445344):*/ BEGIN DECLARE @stored_proc_name char(29) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'TWO.dbo.SVC_Check_Dupe_Return' EXEC @retstat = @stored_proc_name 'STDINV2002', 0, '' SELECT @retstat set nocount on END /* Date: 08/11/2010 Time: 22:53:14 stmt(597445344):*/ {CALL TWO.dbo.zDP_SOP30300F_1('STDINV2002',3,-2147483648,-2147483648,'STDINV2002',3,2147483647,2147483647)} /* Date: 08/11/2010 Time: 22:53:14 stmt(12354792):*/ { CALL DYNAMICS.dbo.zDP_MC40200SS_1 ( 'Z-US$' ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) } /* Date: 08/11/2010 Time: 22:53:14 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) } /* Date: 08/11/2010 Time: 22:53:16 stmt(12354792):*/ { CALL DYNAMICS.dbo.zDP_MC40200SS_1 ( 'Z-US$' ) } /* Date: 08/11/2010 Time: 22:53:17 stmt(12364488):*/ { CALL TWO.dbo.zDP_IV00101SS_1 ( 'PHON-ATT-53WH' ) } /* Date: 08/11/2010 Time: 22:53:17 stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) } /* Date: 08/11/2010 Time: 22:53:17 stmt(597443728):*/ BEGIN DECLARE @stored_proc_name char(29) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'TWO.dbo.SVC_Check_Dupe_Return' EXEC @retstat = @stored_proc_name 'STDINV2002', 32768, '' SELECT @retstat set nocount on END


A DEXSQL.LOG is a powerful troubleshooting tool and should be the first line of defense before contacting Support. You can take the thinking out of setting up DEXSQL.LOGs when you use Support Debugging Tool.



To obtain the Support Debugging Tool be sure to contact your Microsoft partner for a free copy. Be sure to check the the Learning Resources page on this blog to learn more about Support Debugging Tool and other capabilities. Also, the Microsoft Dynamics GP 2010 Cookbook has an entire chapter dedicated to the tool.


Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, August 26, 2010

Using T-SQL and recursive CTE to find related sales documents

In Sales Order Processing, master numbers are used to track the flow of documents throughout the sales process. Documents with the same master number as the current document will be listed in the Sales Document Detail Entry window. A master number is a single number assigned to a series of documents that is used to track related documents. For example, when you issue a quote, then transfer it to an order, and then an invoice, each document will be assigned the same master number. Each document also maintains its own document number.

However, I have seen cases where the master number becomes damaged or out of sequence causing SOP documents to no longer follow a chain. The case was also recently reported in the Partner forum and the partner wanted to understand how they could find what SOP documents were related to each other. Fortunately enough, Microsoft Dynamics GP also tracks the original document number (ORIGNUMB) and document type (ORIGTYPE) from which a SOP document originated. These columns can be found on the Sales Transaction Work table (dbo.SOP10100) and Sales Transaction History (dbo.SOP30200) table.

Given this, we could create a hierarchical structure of documents were the previous document state -- say for example a quote -- becomes the parent of the following document state -- say for example the order that originated from the quote. Knowing this, we can then produce the following recursive Common Table Expression (CTE) -- see my previous article Using T-SQL and recursive CTE to generate a BOM tree for a definition of CTE and another practical example.


-- Mariano Gomez, MVP
-- This query is provided "AS IS". No warranties expressed or implied.
with sopHierarchy as (
select
CAST(RTRIM(ORIGNUMB) + '/' + RTRIM(SOPNUMBE) AS VARCHAR(MAX)) as sopPath, sopnumbe, soptype, orignumb, origtype, 0 as lvl from (
select sopnumbe, soptype, orignumb, origtype from sop10100
union all
select sopnumbe, soptype, orignumb, origtype from sop30200
) anchlvl

union all

select CAST(RTRIM(h.sopPath) + '/' + RTRIM(sublvl.SOPNUMBE) AS VARCHAR(MAX)) as sopPath, sublvl.sopnumbe, sublvl.soptype, sublvl.ORIGNUMB, sublvl.ORIGTYPE, h.lvl + 1 from (
select sopnumbe, soptype, orignumb, origtype from sop10100
union all
select sopnumbe, soptype, orignumb, origtype from sop30200
) sublvl inner join sopHierarchy h on (sublvl.ORIGTYPE = h.SOPTYPE) and (sublvl.ORIGNUMB = h.SOPNUMBE)
)
select distinct lvl, sopPath, soptype, sopnumbe, origtype, orignumb, SPACE(lvl * 4) + sopnumbe as hierarchy
from sopHierarchy
order by sopPath


When this query is executed against the TWO database on SQL, the following result is produced:


Note the path and graphic hierarchical representation of the data on the query window. Also, the level of the relationship is detailed. In this case, this query proved extremely helpful to find related SOP documents in lieu of a master number.

In general, recursive CTEs can be a very powerful tool to uncover data relationships and aid in rebuilding missing or damaged information.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, August 24, 2010

Top 10 Microsoft Dynamics GP freebies

With the amount of money customers pay for software and implementations nowadays, it only makes sense that they would want to maximize their investment. What better than free (as in no strings attached!) software that works as intended. Because of this, I have set out to compile a list of the top 10 free add-ons -- in no particular order -- that I would drop into ANY Microsoft Dynamics GP implementation, no questions asked!

1) Support Debugging Tool. Ok, so I am biased! I have talked about this product in two Microsoft conferences and to outright show how biased I am, I am also personal friends with the product's author, David Musgrave. SDT is a collection of some of the most powerful administrative tools for Microsoft Dynamics GP currently available from anywhere, with rich features like Resource Information, Security Profiler, Dictionary Control, SQL Execute, Runtime Execute, XML Import/Export and the even more powerful Advance Debugger mode. So, where can you download this product? The product is available from Microsoft. If you are customer you MUST request it from your Microsoft Partner, and no, you shouldn't need to pay for it. The product is available via PartnerSource. For more information on SDT, visit the Support Debugging Tool Portal page over at Developing for Dynamics GP.

2) VersionPro. I really like when someone can identify a problem and produce a simple, yet powerful solution. Are you currently having problems keeping tabs of all the dictionary product versions throughout your entire organization? Version Pro is an administrative tool designed to ease the hassles of maintaining workstations on your Dynamics GP network. It keeps track of each product you have installed at a system level – and takes the next step in ensuring that each and every workstation on your network is on the same version for every product. VersionPro is available from Rockton Software -- yes, Mark Rockwell and his crew -- and can be downloaded here.

3) Password Expiration Notice. How many times have you been frustrated by passwords expiring with no prior notification to you or your system administrator? While I have addressed this issue from a SQL Server perspective in my article Enforcing Password Policy with Microsoft Dynamics GP, my friend and fellow blogger Michael Johnson, the MBS Guru takes it a step forward by implementing a Visual Studio Tools customization to check up on expiring passwords. You can download Michael's solution here.

4) Dynamics GP Productivity Pack Add-on. Do you work all day long with customer records? Won't you like to be able to capture information much faster and visit the typical inquiry and transaction entry screens just from the Customer Maintenance window? Matt Landis and the folks over at Landis Computer have the answer. You can download Matt's Productivity Pack Add-On here.

5) DexSense. Are you a die hard Dexterity developer? Wish you could have IntelliSense embedded in Dexterity to speed up your product development cycle? Just a few weeks aback I posted a call to vote on IntelliSense for Dexterity, but the answer came from the community. Tim Gordon at Alpine Limited in South Africa worked tirelessly to produce a solid first approach to IntelliSense with the product now being featured by MVP Leslie Vail in her new round of Dexterity training here in the United States. Now you can download Tim's DexSense here.

6) SpellCheck for Dynamics GP. Who knew right? Tired of misspelling every word typed into those pesky note fields? SpellCheck for Dynamics GP provides spell-checking on any Note, Comment, Text or String field in GP and 3rd party products. SpellCheck can be attached to any text entry field in the application. This add-on is an essential tool for environments that rely heavily on the accuracy of every single word stored in their database. You can download SpellCheck here.

7) Menus for Visual Studio Tools. This is another great free add-on that allows the developer in you to place your WinForms on a standard Microsoft Dynamics GP menu. This gem is also created by Microsoft's David Musgrave. For more information and links to download Menus for Visual Studio Tools click here.

8) Purchasing Account Required. Missing an expense account on a purchase order line item can be just a bit as annoying as having to figure out which line was missed. Michael Johnson puts out another piece of code that takes the thinking out of entering POs by making the Purchase Order account required in the process of entering the order. As if it wasn't enough, you can download both the VBA and the VST versions of the customization here.

9) SmartSort.Perhaps you’ve already discovered that getting your hands on information inside of Microsoft Dynamics GP may not always be an easy proposition, despite the range of tools available. EthoTech SmartSort speeds up your inquiries — and your data entry — by eliminating as many as 3 mouse clicks per lookup. Now you can automatically sort lookup information in almost every conceivable way! Quickly find the information you need by customizing your own view based on built-in advanced sorting options or SmartList favorites. AND… you can use SmartSort with over 40 Microsoft Dynamics GP lookup windows. Download SmartSort here.

10) SOP Batch Split. I couldn't have wrapped up the list without one of my own. Ever wish you could segregate invoices that have been transferred from orders in the same batch? What if all you want to do is change the batch name for a few sales transactions? Now you can do this with the SOP Batch Split tool. SOP Batch Split is a free add-on that uses Modifier with VBA and T-SQL stored procedures to perform a SOP batch transfer and is available here.

If you enjoyed this list and would like to know about other free products or suggest your own please add a comment to this post.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

The Dynamics GP Blogster now has a new look

A couple things I have learned over the years from blogging are a) you want to always bring interesting, high quality content that your audience will want to read, and b) you need to spruce up your site very often. The latter is especially true as the same "look and feel" or site layout if you will becomes tiresome and boring to readers.

With that said, The Dynamics GP Blogsters is undergoing some new changes and like the sign would read, "Pardon our construction!". In the mean time you can continue accessing the same great content and enjoying the progress of the layout as it reaches completion. The ultimate goal is your satisfaction.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Sunday, August 22, 2010

Jivtesh Singh disects the Microsoft Dynamics GP Community

If by now you are not familiar with fellow blogger Jivtesh Singh, it's time for you to go take a look at his work. Jivtesh has set out to disect the Microsoft Dynamics GP community by the numbers in his latest article Dynamics GP Experts. In addition, he has worked tirelessly in putting together what I have denominated The Mother of All Blogs. Yes! Jivtesh is the author and matermind behind the now popular GPWindow (see my previous article GPWindow: The next best thing after the Microsoft Dynamics GP 2010 Cookbook). In his latest iteration of the popular site, Jivtesh has incorporated searches to all the Microsoft Dynamics GP community forums currently available. If at first you complained about not being able to search every site for what you are looking for, there are virtually no excuses now for not being able to find a specific topic across the spectrum of resources across the community.

Jivtesh already did his part, now you do yours! Check out GPWindow, do a few searches on something you could not find before, see what comes back. But more importantly, let Jivtesh know what you think about his work. After all, he's only spent over a year and a half putting it together as, like you, he had trouble finding things with the growing number of resources.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, August 20, 2010

Using SQL Server CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Configuring SQL Server and creating table triggers

In my previous article I outlined some of the steps and methods needed on the Visual Studio side to create an integration to Microsoft CRM. In particular, the article showed you how to create the SQL CLR methods and how these methods interact with the Microsoft CRM Web services to create or update an item in the Product entity.

Following along, once you have created the assemblies (integration assembly and XML serialization assembly), you must proceed to install these in the Framework directory -- the assemblies were created targeting the .NET Framework 3.5, so they were installed there -- and also register these in the Global Assembly Cache (GAC) under the %windir%\assembly folder.

Once the assemblies are GAC'ed you can now begin the process of registering these with Microsoft SQL Server 2005, 2008, or 2008 R2. To begin registering the assemblies with SQL Server, we must first define an Asymmetric Key from the signed assembly created in our previous project.



USE master;
GO

CREATE ASYMMETRIC KEY CrmKey
FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
GO


An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly. For additional information on asymmetric keys click here.

Next, you must define a SQL Server login that's associated to the asymmetric key for code signing purposes. One of the characteristics of the .NET Framework is that all external resources being accessed will require a certain level of trust. SQL Server accomplishes this by using a login for code signing with specific permissions to the outside world.



USE master;
GO

CREATE LOGIN [crmlogin] FROM ASYMMETRIC KEY [CrmKey];
GO

GRANT UNSAFE ASSEMBLY TO crmlogin;
GO


For more information on granting permissions to assemblies click here.

Once we have created the asymmetric key, it's now time to create the assemblies in your company database.


USE [CompanyDB];
GO

CREATE ASSEMBLY [Crm.Integration]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [Crm.Integration.XmlSerializers]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

For more information on creating assemblies, click here.

With the assemblies created, it's now time to expose our CLR stored procedure to SQL Server. In order to register our CLR method, we use the standard CREATE PROCEDURE statement with a twist:


SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[crmInsertProduct]
@itemNumber NVARCHAR(31),
@itemDescription NVARCHAR(100),
@VendorName NVARCHAR(65),
@VendorItem NVARCHAR(31),
@ItemShipWeight NUMERIC(19,5),
@defaultUofM NVARCHAR(20),
@defaultUofMSched NVARCHAR(20),
@defaultPriceList NVARCHAR(20),
@currencyID NVARCHAR(15),
@decimals INT,
@quantityOnHand NUMERIC(19,5),
@listPrice NUMERIC(19,5),
@priceListPrice NUMERIC(19,5),
@standardcost NUMERIC(19,5),
@currentCost NUMERIC(19,5),
@productTypeCode INT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Crm].[Integration].[clrProcedures].[CreateProduct]
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

GRANT EXECUTE ON [dbo].[crmInsertProduct] to [DYNGRP]
GO

Note that the stored procedure must be created with the same number of parameters as the CLR method.

Finally, we can create a trigger on the IV00101 table to call the stored procedure and pass in the parameters required.

Here are some final notes from and things I had to implement at the SQL Server configuration level to make all this work:

1. First, you must enable CLR integration on SQL Server to allow it to execute assemblies. To enable CLR integration, you must change the 'CLR Enabled' option in SQL Server configuration.


USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO
EXEC sp_configure 'CLR Enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE;
GO


2. In order to recreate all the above objects, you must first drop the stored procedure, then drop the assemblies, then login, and finally the asymmetric key, this is, objects need to be dropped in reverse order to avoid dependency errors.


USE [CompanyDB]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crmInsertProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[crmInsertProduct]
GO

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration.XmlSerializers' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration.XmlSerializers]

GO

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration]
GO

USE master;
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'crmlogin')
DROP LOGIN [crmlogin]
GO

DROP ASYMMETRIC KEY CrmKey;
GO


I hope you had a good time reading this series. A lot of what you read here I had to learn on the fly, so a lot of reading and research went into building this integration approach. I am sure there are things that could be improved, but this is the down and dirty version.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, August 18, 2010

Microsoft Dynamics GP 10.0 Service Pack 5 now available

The long awaited Microsoft Dynamcis GP 10.0 Service Pack 5 is fresh out of the oven. The service pack is available for English only installations at this time and can be downloaded from:


PartnerSource
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0

CustomerSource
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0


One big note of caution: after applying Service Pack 5 for Microsoft Dynamics GP 10.0, you will not be able to upgrade to Microsoft Dynamics GP 2010 until the release of Service Pack 1 for the latter.

Related Articles:

Microsoft Dynamics GP 10.0 Service Pack 5 @ Developing for Dynamics GP

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, August 17, 2010

Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Creating a CLR assembly and working with CRM web methods

Before we get started, there are a few rules: a) I assume you have good working knowledge of both Microsoft Dynamics GP and Microsoft CRM and that you know enough about the Item Master in GP and the Product entity in CRM, b) you are familiar with Microsoft Visual Studio and can create class libraries and create Web references, c) you have done some coding with either VB.NET and/or C#, and d) you will not ask me if I have the same code snippets in VB.NET. As I have said in multiple occassions -- no offense to VB developers -- when I work on commercial grade code I will choose C# over VB.NET any day of the week.

A bit of a reminder of the objective of today's code: a) we will create our CLR methods that will serve as bridge to the Microsoft CRM web methods. The resulting assembly will be registered on SQL Server with the CLR methods exposed as stored procedures that can be called from a trigger, and b) we will create the code that will allow us to establish a connection to Microsoft CRM and in turn insert a new or update an existing Product in CRM.

We begin by creating a class library project and renaming our default class library file to clrProcedures.cs. Once this is done, we can start declaring all namespaces to help us control the scope of class and method names that we will be using throughout the project. In particular, SQL Server CLR methods will benefit from using the Microsoft.SqlServer.Server namespace contained in the System.Data.dll assembly.

clrProcedures.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Services.Protocols;
using Microsoft.SqlServer.Server;
using Crm.Integration;

Also note that in the above code, I have declared the Crm.Integration namespace. This namespace will be created as a new Class Library file (Crm.Integration.cs) within our project further on in this article.

We must now implement the clrProcedures class. One note about CLR methods is that they are not encapsulated within a namespace and rather begin with the class declaration. This behavior is by design. Within our clrProcedures class, we will create a method, CreateProduct, that can be registered as a stored procedure in SQL Server. We will declare all the parameters that will be passed to the stored procedure. I believe these are pretty self-explanatory, but if you have any questions please follow up with a comment on the article.



public class clrProcedures
{
[SqlProcedure]
public static void CreateProduct(
string itemNumber
, string itemDescription
, string vendorName
, string vendorItem
, decimal itemShipWeight
, string defaultUnitOfMeasure
, string defaultUnitOfMeasureSched
, string defaultPriceLevel
, string currencyID
, int decimalsSupported
, decimal quantityOnHand
, decimal unitPrice
, decimal priceLevelPrice
, decimal standardcost
, decimal currentCost
, int productTypeCode
)

Now we will proceed to create a few local variables, particularly the CRM server name, the CRM server port, and CRM Organization Name. These will be passed to our connection method to, well, open a connection to CRM. These values are read from a custom SQL table, dbo.crmInfo, in our company database. You may ask, why not create these values in a configuration file? One of the goals for my client was to provide easy access to database administrators to quickly reconfigure CRM server names and organization names without having to bother the network administrators, so it was easier to store this information in a table. In turn, our configuration file would be left to the network adminstrators to configure the address of the CRM web services as needed. My client is a public company and required segregation of duties between database admins and network admins.


{
string crmServerName, CrmServerPort, CrmOrgName;
string sSQL = "SELECT CrmServerName, CrmServerPort, CrmOrgName FROM crmInfo";

using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(sSQL, connection);
SqlDataReader r = command.ExecuteReader();

r.Read();
crmServerName = Convert.ToString(r["CrmServerName"]);
CrmServerPort = Convert.ToString(r["CrmServerPort"]);
CrmOrgName = Convert.ToString(r["CrmOrgName"]);
}

Now that we have queried our CRM server settings, we can establish a connection to CRM. Our CRM authentication is done via Active Directory. This is important to know when using CLR methods as the SQL Server service startup credentials will be passed to the CRM connection. Hence, the SQL Server service account must exist in the CRM users and be associated to a role that has access to create Products in CRM. Suffice to say, we will be expanding on the crmIntegration class later and the crmConnection() and crmInsertProduct() methods.


//create an instance of the crm integration class
crmIntegration crmInt = new crmIntegration();

try
{
// Establish connection with CRM server
crmInt.crmConnection(crmServerName, CrmServerPort, CrmOrgName);

// Insert product
crmInt.crmInsertProduct(
itemNumber.Trim()
, itemDescription.Trim()
, vendorName.Trim()
, vendorItem.Trim()
, itemShipWeight
, defaultUnitOfMeasure.Trim()
, defaultUnitOfMeasureSched.Trim()
, defaultPriceLevel.Trim()
, currencyID.Trim()
, decimalsSupported
, quantityOnHand
, unitPrice
, priceLevelPrice
, standardcost
, currentCost
, productTypeCode
);
}
catch (System.Exception ex)
{
if (ex.InnerException != null)
{
SqlContext.Pipe.Send("Exception occurred: " + ex.InnerException.Message);

SoapException se = ex.InnerException as SoapException;
if (se != null)
SqlContext.Pipe.Send("Exception detail: " + se.Detail.InnerText);
}
}
finally
{
//do something else here
}
}
}

Since one of the main concerns of the client was the ability to upgrade with each new release of CRM, we made use of the CRM web services provided by the Microsoft CRM 4.0 SDK. For this, we will add a new class library to our project and call it Crm.Integration.cs which will implement the Crm.Integration namespace and the CrmIntegration class. But first, we must create two web references: one for the CRM Service, contained under the CrmSdk namespace and one for the CRM Discovery Service, contained under the CrmDiscovery namespace.

Crm.Integration.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Web.Services.Protocols;
using CrmDiscovery;
using CrmSdk;

Now that we have declared our namespaces, we can proceed to implement the crmIntegration class. The first method to be implemented will be the connection method. This method contains all the code needed to use the Discovery service to obtain the correct URL of the CrmService Web service for your organization. The code then sends a WhoAmI request to the service to verify that the user has been successfully authenticated. This method consists of three specific operations: a) instantiate and configure the CRMDiscovery Web service, b) Retrieve the organization name and endpoint Url from the CrmDiscovery Web service, and c) create and configure an instance of the CrmService Web service.


namespace Crm.Integration
{
public class crmIntegration
{
CrmService crmService;

// Establishes a connection to CRM
public void crmConnection(string hostName, string hostPort, string orgName)
{
try
{
// STEP 1: Instantiate and configure the CrmDiscoveryService Web service.

CrmDiscoveryService discoveryService = new CrmDiscoveryService();
discoveryService.UseDefaultCredentials = true;
discoveryService.Url = String.Format(
"http://{0}:{1}/MSCRMServices/2007/{2}/CrmDiscoveryService.asmx",
hostName, hostPort, "AD");

// STEP 2: Retrieve the organization name and endpoint Url from the
// CrmDiscoveryService Web service.
RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
RetrieveOrganizationsResponse orgResponse =
(RetrieveOrganizationsResponse)discoveryService.Execute(orgRequest);

String orgUniqueName = String.Empty;
OrganizationDetail orgInfo = null;

foreach (OrganizationDetail orgDetail in orgResponse.OrganizationDetails)
{
if (orgDetail.FriendlyName.Equals(orgName))
{
orgInfo = orgDetail;
orgUniqueName = orgInfo.OrganizationName;
break;
}
}

if (orgInfo == null)
throw new Exception("The organization name is invalid.");

// STEP 3: Create and configure an instance of the CrmService Web service.

CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = orgUniqueName;

crmService = new CrmService();
crmService.Url = orgInfo.CrmServiceUrl;
crmService.CrmAuthenticationTokenValue = token;
crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

// STEP 4: Invoke CrmService Web service methods.

WhoAmIRequest whoRequest = new WhoAmIRequest();
WhoAmIResponse whoResponse = (WhoAmIResponse)crmService.Execute(whoRequest);

}
// Handle any Web service exceptions that might be thrown.
catch (SoapException ex)
{
throw new Exception("An error occurred while attempting to authenticate.", ex);
}
}

For more information on using the CRM Discovery service with Active Directory authentication, click here. Following the authentication process, we can now implement the method that will insert or update a product in the Product entity.


// Insert product method
public void crmInsertProduct(
string productNumber,
string productName,
string productVendorName,
string productVendorItem,
decimal productWeight,
string defaultUnitOfMeasure,
string defaultUnitOfMeasureSched,
string defaultPriceLevel,
string currencyID,
int decimalsSupported,
decimal quantityOnHand,
decimal listPrice,
decimal priceLevelPrice,
decimal standardCost,
decimal currentCost,
int productTypeCode)
{
try
{
string strProductId;
product crmProduct = new product();

bool found = crmGetProduct(productNumber, out strProductId);
if (!found)
{
// is a new product, create
crmProduct.productnumber = productNumber;
crmProduct.name = productName;

// quantity decimal places
crmProduct.quantitydecimal = new CrmNumber();
crmProduct.quantitydecimal.Value = decimalsSupported;

// quantity on hand
crmProduct.quantityonhand = new CrmDecimal();
crmProduct.quantityonhand.Value = quantityOnHand;

// unit price
crmProduct.price = new CrmMoney();
crmProduct.price.Value = listPrice;

// standard cost
crmProduct.standardcost = new CrmMoney();
crmProduct.standardcost.Value = standardCost;

// Current cost
crmProduct.currentcost = new CrmMoney();
crmProduct.currentcost.Value = currentCost;

// Vendor Name
crmProduct.vendorname = productVendorName;

// Vendor Item
crmProduct.vendorpartnumber = productVendorItem;

// Shipping Weight
crmProduct.stockweight = new CrmDecimal();
crmProduct.stockweight.Value = productWeight;

//------------------------------------------------//
// Product type code //
//------------------------------------------------//
crmProduct.producttypecode = new Picklist();
if (productTypeCode != 0)
crmProduct.producttypecode.Value = productTypeCode;
else
crmProduct.producttypecode.IsNull = true;


// retrieve guid's for the default unit of measure
string strUofM;
string strUofMSched;

bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
if (isUofM)
{
crmProduct.defaultuomid = new Lookup();
crmProduct.defaultuomid.Value = new Guid(strUofM);
crmProduct.defaultuomid.type = EntityName.uom.ToString();

crmProduct.defaultuomscheduleid = new Lookup();
crmProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
crmProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
}

// create the product
Guid productId = crmService.Create(crmProduct);

// create pricelist
crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

// Create the column set object that indicates the fields to be retrieved.
ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { "productid", "pricelevelid" };

// Retrieve the product from Microsoft Dynamics CRM
// using the ID of the record that was retrieved.
// The EntityName indicates the EntityType of the object being retrieved.
product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), productId, columns);
updatedProduct.pricelevelid = new Lookup();

string guidPriceLevel;
bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
if (isPricelevel)
{
updatedProduct.pricelevelid = new Lookup();
updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

}

// update the record
crmService.Update(updatedProduct);
}
else
{
// Create the column set object that indicates the fields to be retrieved.
ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { "productid", "name", "quantityonhand", "price", "standardcost", "currentcost", "defaultuomid", "defaultuomscheduleid" };

// Retrieve the product from Microsoft Dynamics CRM
// using the ID of the record that was retrieved.
// The EntityName indicates the EntityType of the object being retrieved.
Guid _productGuid = new Guid(strProductId);
product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), _productGuid, columns);

updatedProduct.name = productName;

// quantity decimal places
updatedProduct.quantitydecimal = new CrmNumber();
updatedProduct.quantitydecimal.Value = decimalsSupported;

// quantity on hand
updatedProduct.quantityonhand = new CrmDecimal();
updatedProduct.quantityonhand.Value = quantityOnHand;

// unit price
updatedProduct.price = new CrmMoney();
updatedProduct.price.Value = listPrice;

// standard cost
updatedProduct.standardcost = new CrmMoney();
updatedProduct.standardcost.Value = standardCost;

// Current cost
updatedProduct.currentcost = new CrmMoney();
updatedProduct.currentcost.Value = currentCost;

// Vendor Name
updatedProduct.vendorname = productVendorName;

// Vendor Item
updatedProduct.vendorpartnumber = productVendorItem;

// Shipping Weight
updatedProduct.stockweight = new CrmDecimal();
updatedProduct.stockweight.Value = productWeight;

//------------------------------------------------//
// Product type code //
//------------------------------------------------//
updatedProduct.producttypecode = new Picklist();
if (productTypeCode != 0)
updatedProduct.producttypecode.Value = productTypeCode;
else
updatedProduct.producttypecode.IsNull = true;

// retrieve guid's for the default unit of measure
string strUofM;
string strUofMSched;

bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
if (isUofM)
{
updatedProduct.defaultuomid = new Lookup();
updatedProduct.defaultuomid.Value = new Guid(strUofM);
updatedProduct.defaultuomid.type = EntityName.uom.ToString();

updatedProduct.defaultuomscheduleid = new Lookup();
updatedProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
updatedProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
}

string guidPriceLevel;
bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
if (isPricelevel)
{
updatedProduct.pricelevelid = new Lookup();
updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

}

// create pricelist
crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

// update the record
crmService.Update(updatedProduct);
}
}
catch (SoapException ex)
{
throw new Exception("An error occurred while attempting to insert a record in the CRM product entity.", ex);
}
}

In order to establish whether a product should be inserted or updated in the Product entity, you must first lookup the product. That's accomplished by invoking the crmGetProduct() method (to be implemented below). If the product is not found in the catalog, we can proceed to setup all the attributes to be inserted, then call the crmService.Create() method.

If the product is found, then we can just retrieve all the columns that will be subsequently updated, then invoke the crmService.Update() method to commit the changes.

Finally, the crmGetProduct() method is shown below:


public bool crmGetProduct(string productNumber, out string pId)
{
pId = null;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "productnumber";
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new string[] { productNumber };

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] { condition1 };

ColumnSet resultSetColumns = new ColumnSet();
resultSetColumns.Attributes = new string[] { "productid", "productnumber" };

// Put everything together in an expression.
QueryExpression qryExpression = new QueryExpression();
qryExpression.ColumnSet = resultSetColumns;

// set a filter to the query
qryExpression.Criteria = filter;

// Set the table to query.
qryExpression.EntityName = EntityName.product.ToString();

// Return distinct records.
qryExpression.Distinct = true;

// Execute the query.
BusinessEntityCollection productResultSet = crmService.RetrieveMultiple(qryExpression);

// Validate that an expected contact was returned.
if (productResultSet.BusinessEntities.Length == 0)
return false;
else
{
bool productFound = false;
foreach (product aProduct in productResultSet.BusinessEntities)
{
if (aProduct.productnumber.ToUpper().Trim().Equals(productNumber.ToUpper()))
{
productFound = true;
pId = aProduct.productid.Value.ToString();
break;
}
}

return productFound;
}
}
}
}

The beauty about Microsoft Dynamics CRM platform services is that it provides a number of methods and implementations that facilitate querying any piece of data stored in the platform. The above method shows the use of the ConditionExpression, FilterExpression and QueryExpression classes, that when combined together, form the basis of the query platform. Finally we can create a collection with the filtered Product entity and navigate to see if the product was found.

This completes the first part of our implementation, but here are some final notes and things that I discovered throughout the project:

1. Assemblies that will be registered against SQL Server require signing. You must create a strong name key file that will be used to sign your assembly. To do this, go to the project Properties and select the Signing tab.

2. You cannot simply register an assembly that references a Web service against SQL Server without creating an XML serialization assembly. Serialization assemblies improve the startup performance of the Web service calls. To do this, go the project Properties and select the Build tab. Select On from the Generate Serialization Assembly drop down list.

Keep in mind that the above code is only provided as a sample and that other implementations are required to deal with Unit of Measures and Price Schedules. The bottom line is, the crmGetProduct() method provides the basis for the implementation of the other methods not shown.

Friday, I will show you how to register the assemblies on Microsoft SQL Server and how to implement some basic triggers that will exploit the CLR stored procedures.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, August 16, 2010

Learning Resources page updated to include tutorials

In the past, I have published a series of tutorial articles that now have been incorporated as part of the Learning Resources page. These tutorials have been visited more than 20 thousand times and have earned great comments from you the Community. The following 3 tutorials have been added.


Please visit the Learning Resources page to find materials from past conferences and webinars to help you ramp up on some of the most intriguing topics in the Microsoft Dynamics GP world.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Sunday, August 15, 2010

Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM's Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes


You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week's series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 - Creating a CLR assembly and working with CRM web methods

08/20/2010 - Configuring SQL Server and creating table triggers

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, August 9, 2010

Microsoft Dynamics GP 2010 Connect feature and IE popups

Since the days of beta testing Microsoft Dynamics GP 2010, I had been researching an issue with the Connect feature where, each time the content banner rotates, an Internet Explorer window would pop up showing the same content displayed by the Connect web part. One night, I left my laptop on with Microsoft Dynamics GP 2010 opened and woke up the next morning to the tune of 100+ individual Internet Explorer windows and a very slow, almost crawling, machine.


The pop-ups seemed to intensify when the GP 2010 main desktop becomes unfocused. The following is a sample of the URL that would show in IE address bar for a pop-up window (my voice account has been crossed out for security purposes).

https://online.dynamics.com/lus/?se=popup&product=GPRoleCenter&version=11.00.0218.000&roleid=4050&voiceaccount=XXXXXX&country=US&locale=EN-US&company=Fabrikam%2c+Inc.&role=IT+Operations+Manager

Interestingly enough, the URL contains a parameter (se) which value happens to be interestingly enough popup. A few days aback, the Connect content changed on the Microsoft side to a static content and there were no rotating banners anymore which in turn ceased the IE pop ups.

Initially, I never thought of associating this issue with something very familiar to all of us: pop-up adds. Pop-up adds are mostly generated with JavaScript which is how the Connect feature on the home page was programmed. Below, a sample from the home page code:



//These are needed to control the interface
var ConnectsourceURLs;
var ConnectsourceURLParams;
var ConnenctCompanyParam;
var ConnenctRoleParam;


ConnectsourceURLs = "https://go.microsoft.com/fwlink/?linkid=152456";
ConnectsourceURLParams = "&product=GPRoleCenter&version=11.00.0218.000&roleid=4050&voiceaccount=XXXXXXX&country=US&locale=EN-US";
ConnenctCompanyParam = encodeURIComponent("Fabrikam, Inc.");
ConnenctRoleParam = encodeURIComponent("IT Operations Manager");


ConnectsourceURLs = ConnectsourceURLs + ConnectsourceURLParams + "&company=" + ConnenctCompanyParam + "&role=" + ConnenctRoleParam;
ConnectsourceURLs = ConnectsourceURLs.replace(/ /g, "%20");

// Initialize the iframe
if (ConnectsourceURLs.length > 0)
{
setConnectFrameSource("connectIFrame");
}


With this understanding, I checked Internet Explorer's configuration and realized that the Pop-up Blocker was disabled. By enabling IE's Pop-up Blocker feature and restarting Microsoft Dynamics GP 2010, the Internet Explorer pop-ups immediately ceased. Nonetheless, the question remains: Why is Microsoft Dynamics GP 2010 Connect feature raising popups to begin with?

If you have had to deal with this, just enable your browser's Pop-up Blocker.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, August 6, 2010

All About Dexterity OLE Container - Follow Up

Mr. Steve Endow over at Dynamics GP Land managed to unearth my All about the Dexterity OLE Container article in response to a question he fielded from a user who no longer used GP, but wanted to know if they could extract the content embedded in the note files.

In my 2008 article I explained the intrinsic details regarding how OLE notes where created and stored in GP and promised a follow up with a way to automate or access these files externally from GP. During the past two years I have never stopped researching (on and off of course) how these files could possibly be accessed. At the end of the day, it comes down to identifying the actual storage compression algorithm used.

As part of this research, I tested a few tools including Corel's WinZip tool, which is often considered a very good tool for identifying obscure storage compression formats. However, I had no luck with WinZip. I also tried (successfully I may add) creating my own OLE container application, but failed to be able to open the Dexterity OLE Container files created by GP with it.

Over the course of my research, I came across an open source tool called 7-Zip written by Russian programmer Igor Pavlov. Pavlov managed to implement a number of compression algorithms in his tool, some even used in the early days of computing. So I decided to download 7-Zip, install it and give it a try.

To my surprise, 7-Zip was able to open a Dexterity OLE Container note file and display its content as shown below:


7-Zip shows a directory structure for the file including a contents file and an Embedding folder. I was then curious about the Embedding folder. As the name would suggest, it would probably hold the actual content of the notes itself. Earlier, I had attached a file called intellisense.txt to our favorite customer AARONFIT0001 to verify what I would see. So, on I went to open the Embedding folder, which now showed two files:


The larger file ( [1]OLE10Native ) seemed more promising than the smaller one, so I decided to edit it. All that's needed is to press F4 or go to the file menu to edit the file. Upon editing it, the file displayed in the traditional NOTEPAD.


Note that the [1]OLE10Native file displays the actual text stored in the IntelliSense.txt file. Since this file was directly embedded into the note not as a link, it would suggest that changes to this file would not affect the actual file stored on my desktop. In addition, it should be noted that the path to the actual file is stored at both the header and footer levels. This is crucial for those of you attempting to migrate your OLE files from one location to another.

I then decided to perform another test by attaching something a bit more complex, a PDF file. This time though, I decided I would attach the file as a link and would display as an icon. When a file is attached as a link, changes made to the actual file, are reflected automatically in the container.



In exploring each file, the [1]Ole file had all the information on where to find the actual link to the file:



Once again, these links can be edited if you must migrate your notes to another directory on your network.

But the question still looms -- What storage compression algorithm is used to store these files? Clearly, 7-Zip was able to open them and even expose the contents. I have a theory on the algoritm, but more on that in my next post -- and trust me, you won't have to wait 2 more years!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, August 2, 2010

DexSense: IntelliSense for Microsoft Dexterity


In past weeks I posted an article explaining how Microsoft Dexterity could benefit from IntelliSense technology and enter a request for the community to vote on the Microsoft Connect product suggestion 570553 so the feature could be considered by Microsoft for introduction into a future release of Microsoft Dexterity. You can find the link to those articles, as follows:

Dexterity and IntelliSense
Dexterity and IntelliSense: It's time to vote!

A couple weeks later, the answer came from the Community after Tim Gordon, a software developer and consultant with Alpine Limited saw the post referencing my articles over at Developing for Dynamics GP. Tim contacted David Musgrave, who then copied me after the first couple exchanges they had. Tim had been working on a very rudimentary prototype of IntelliSense for Dexterity. His Visual Studio IntelliSense application sits in the Windows system tray "listening" for a Dexterity process. When Dexterity becomes available, the application attaches to the process. Tim's initial implementation used text files to store pre-loaded table and forms resources, but after a couple more exchanges with David and myself, Tim changed the implementation to use the Continuum Library with Dexterity and is now able to read resources in real time.

Over time, the exchanges grew from simple requests to more complex ones, each new request building on the previous implementations. This is how DexSense took on a life of it's own. After a good number of hours working with Tim, you can now reap the benefits of our collective effort by downloading the DexSense application here. The following is a list of current features:


  • Support for Microsoft Dexterity versions 9, 10, 11
  • IntelliSense support for tables, forms, reports, and scripts (functions and procedures) resources
  • Ability to toggle between physical table names and technical table names during intellisense, for example, get table RM20101 would translate automatically to get table RM_OPEN.
  • Predefined Dex constructs, if...then...else
  • Intellisense for get statement

As with all version 1 products, there's still long ways to go, but you have to start somewhere and the current build (DexSense 1.6) has sure started out with a huge leap forward. If you are one of those who love to criticize, make sure you do so constructively and after installing and working with the application for at least a couple weeks.

To enter your comments and/or suggestions, click on Tim's picture above.

Also, be sure to read David Musgrave's article on the subject:

IntelliSense for Dexterity - Ask and You Shall Receive

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/