Rescuing Moore's Law: Memristors

While this topic may seem totally off subject, its future application will have tremendous impact on data storage and computer memory technology. Moore's Law says the number of transistors that can be squeezed onto an integrated circuit (chip) at least doubles every two years. The issue is, memory technology and flash drives are reaching the 20 nometers barrier, this is, the distance between transistors on a chip. Here comes Memristors, or Memory Resistors, a theory developed in the 1960's, but given the time, technologically impossible to achieve. Scientists and engineers have now embrace the theory and are developing practical applications that will allow things like phones and flash drives to store data very easily in the terabyte range, but more so, reducing the amount of power needed to store this data. Researchers are even talking about being able to power computers on and off without the tedious boot up sequence needed today without the lost of data. Today's RAM is completely cleared due to lack of power. Just imagine the impact this technology will have on database storage engines! This stuff is geeky stuff and it won't be fair for me to expand on the topic, so please read the complete article, Researchers rescue Moore's Law, over at MSNBC.

Until next post!

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

Read More

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/

Read More

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/

Read More

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/

Read More

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/

Read More

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/

Read More

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/

Read More