Gardens 2016

Posted in Uncategorized | Leave a comment

sitefinity errors

Return code 0 when trying to save data.  


run select base_id, * from TableOfContentThatWontSave where status = 1


In my case this was in the ecommerce products module.  I needed to find the products that had a status of 1.  Each one was listed as blocked.  I checked the item under more actions in the top menu, I published the items.  Once all items identified by the querry were published and unblocked I was again able to save.

Another possible reason

using the Telerik.Sitefinity.Web.UI.Fields.ImageField, Telerik.Sitefinity with out actually adding an image will cause the same issue??

Actually found the issue today. the update procedure seems to dynamically create itself based on the string passed in and it was 261 char and the field in the database was 255. Updated the field size in sql and it saved no problem. 

The dynamic content module table will do the same. identify the item that is in edit mode.  go to the item, open and save. This was the first thing I tried. In some cases this is all that is needed.

Posted in Uncategorized | Leave a comment

GP 2010 Item last sold: Including BOM components

Starting with the SQL view for last sale by customer and item by Victoria Yudin with a couple modifications.  I created some queries to recursively capture all of the components associated with the items sold as well and where they are found.

This is the modified view based on Victoria’s Item Last sold Per Customer

--this is simple view combining work and history
dbo.SOP10100_SOP30200 AS H INNER JOIN
--looking for orders change SOPTYPE to 3 for Invoices

The above query will pull out all top level items that have been sold and the last sale date.  I made this a view to pull against.

Now the key to understanding exactly what inventory you have sold and when you sold it, requires you dive into the BOMs of an item.  Now often items are used on multiple BOMs.  It can also be helpful to know what it is found in for those components that no one remembers are even in the system.

This is the query I came up with to accomplish that.  It is surprisingly efficient too or at least I think it is for what it is doing.

declare @Months int
set @Months = 12
--create temp table to store the items
Create table #TEMPINVALL (
Lastsale datetime,
ITEM varchar(50),
FOUNDIN varchar(50),
ITEMTYPE varchar(10),
COST varchar(50),
insert into #TEMPINVALL
--top level
SELECT        Last_Sale, dj.ITEMNMBR,'' as 'FOUNDIN', dj.ITEMTYPE, STNDCOST, 0
inner join IV00101 iv on iv.ITEMNMBR = dj.ITEMNMBR
--pass number of months back or remove for all items ever
WHERE       (Last_Sale > DATEADD(m, - @Months, GETDATE()))
--create temp table to store component information
create table #tablecomps (
ITEM varchar(50),
FOUNDIN varchar(50),
Lastsale datetime)
--create fetch and pass items into the BOM tables
--to recursively pull out additonal components
declare @ITEM varchar(50)
declare @lastsale datetime
declare @FOUNDIN varchar(50)
declare itemcomp Cursor For
select ITEM, Lastsale from #TEMPINVALL
Open itemcomp
fetch Next from itemcomp
into @ITEM,  @lastsale;
while @@Fetch_status = 0
      SELECT BM010115.CPN_I, BM010115.PPN_I, BOM_LEVEL + 1 FROM BOM_No
      ON BM010115.PPN_I = BOM_No.CPN_I)
insert into #tablecomps
SELECT distinct CPN_I, PPN_I, BOM_LEVEL, @lastsale FROM BOM_No
OPTION (MaxRecursion 100)
fetch Next from itemcomp
into @ITEM,  @lastsale;
end close itemcomp
deallocate itemcomp
--combine the two temp tables with a union
select distinct MAX(Lastsale) as Lastsale, ITEM, '' as 'FOUNDIN',
when ITEMTYPE = 1 then 'Sales Inventory'
when ITEMTYPE =  2 then 'Discontinued'
when ITEMTYPE = 3 then 'Kit'
when ITEMTYPE = 4 then 'Misc Charges'
when ITEMTYPE = 5 then 'Services'
when ITEMTYPE = 6 then 'Flat Fee'
inner join IV00102 q on ITEM = q.ITEMNMBR
and RCRDTYPE = 1
select distinct MAX(Lastsale) as Lastsale, ITEM, FOUNDIN,
when ITEMTYPE = 1 then 'Sales Inventory'
when ITEMTYPE =  2 then 'Discontinued'
when ITEMTYPE = 3 then 'Kit'
when ITEMTYPE = 4 then 'Misc Charges'
when ITEMTYPE = 5 then 'Services'
when ITEMTYPE = 6 then 'Flat Fee'
,STNDCOST, BOMEBLEVEL + 1, QTYONHND  from #tablecomps
inner join IV00101 i on ITEM = i.ITEMNMBR
inner join IV00102 q on ITEM = q.ITEMNMBR
and RCRDTYPE = 1
--finally drop temp tables
drop table #tablecomps
drop  table #TEMPINVALL

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Posted in GP, GP 2010, GP SQL | Leave a comment

ClickDimensions: Fancy Formatting Forms

I do think Click Dimensions is a pretty good content style management tool for CRM 2011.  Although you are extremely limited in the formatting of forms.  The labels have to go on top of the text box, the submit button has to go on the bottom of the form.  Specific styling can be near impossible.  Now I’m a developer and amazed at this being a tool used by marketing people that it is so limited in this way.  Marketing people are the first to want some really unusual formatting on just about everything.

This is how you do it.

When you have finished adding all the fields to your form.  click to embed.


When the embed screen comes up go to the link.  Then right click and copy all of the text out.  Then create an .htm file.  You can just use notepad for this and paste everything into it and save it with .htm instead of .txt


Go back into CRM  and create a landing page or something that will give access to the documents. Upload the .htm file.


Then add the link to the document to your landing page.

This link will then become the url for the customized form you have just created.  This keeps the actual form inside of click dimensions so it can be modified as needed and uploaded again, check to “Override if file exists”,  and will push out to the website it resides on.  If you make any changes to the web form, like change the confirmation text, add an action or anything you will need to update everything in the <form> tag.  There is posting information encrypted in there.

Here is my final form.  Not a ton of mods, but none of these could have been done with the traditional From web content inside of Click Dimensions.


Posted in CRM 2011, Dynamics | Leave a comment

SSRS Special Date Stamp Format Name Changing Scheduled report: DTS package do your thing…

We have a requirement for the naming of a report to have a special date stamp format.  From everything I have found this seems an impossible task in SQL 2008 SSRS with out the help of a dts package.

I am really writing this post, because I rarely work dts packages.  Often they are the kind of thing that once they are set up they just run.

Created report in MS2008 SQL Reporting services

Scheduled report to write a file to a system file every day at 5:30.  This was a simple task done right in subscriptions.

Then I have a scheduled dts package in SQL Business intelligence Development or BIDS that will update the name of the file and then email it to the list of individuals who have been receiving these reports.

First I added a file system Task to my control flow tab.  I am actually doing this on two different files, so I have two tasks, one for each file.


Now we need to create some variables to store some information in them.

The not so obvious.  Where are the variables and how can you add them?


click on the tab Package Explorer


this will dropdown and give lots of choices..


when you select variables it will give you this handy little area, click the +icon to add a new one.  so intuitive ;D

I added a variable “src_file” for the name  of the file that needs to be opened and renamed.

Add a new connection this will be where the files will be saved. This is where the renaming of file happens. Navigate to the place where the file is and select the file. Use the expression button to add the code to change the name of the file.    Now the name of my connection becomes the name of my file.


Next we will add these variables and connections to the file system task.


add event handler that will send an email on the execution of the file system task



when you select edit then expressions then drop down the expressions and you should see attachments. Add the same code you used to rename the file to go get the attachment.


now when you look it will always have the name of the last file renamed in the attachments when you open to edit the mail in the future.  (that’s ok, it gets over written)


To get this into Sql to start scheduling te DTS package.

create a manifesto

right-click on the name of your package and select properties


select deployment utility and then under create deployment utility change the value to true.


then build the package.


navigate the deployment output folder and double-click the file with the “manifesto” extension.

this will open the installation wizard


click Next


I am saving mine to a file system.  this way you could potentially add it to any sql instance.  if you save it to the sql it is basically stored in the sql server that it is deployed to and you would need to do this again to move it to another server.


then click finish and now you will have a .dts package that you can schedule in sql.

Posted in SSRS | Leave a comment

Dynamics Connector: The CrmReference mapping query returned no results for property

First I want to give a huge thanks to this blog:

This blog had the exact solution to a problem that was difficult to diagnose and then finding a fix seemed near impossible.

I have committed to using the Dynamics Connector for my integration between GP 2010 and CRM 2011.  We use CRM 2011 for opportunities and then create quotes and sales documents in GP that reference an opportunity number (custom field in CRM) .  This opportunity number is used to associate our sales documents to the opportunity.

I simply mapped the Opportunity number that was stored in a user defined field in GP to the appropriate look up field on the map. I get

  The CrmReference mapping query returned no results for property: Opportunity with dynamics_integrationkey = 000020722.

So I ran the configuration utility to allow opportunities to be seen as a lookup.

still getting error….

I quickly realized that at no point had the dynamics_integrationkey been populated with anything in the opportunity entity.  Once I had the opportunity number in the dynamics_integrationkey field in CRM, I expected to no longer see that error.  The accounts pulling over from GP were using the GP customer number as a dynaimcsintegration_key and they were mapping across just fine.  But when I ran the integration I continued to see that error.  For a little debugging I create a mock opportunity with a name of 12345 and used that as my dynamicsintegration_key in the map and sure enough it associated all of the sales documents to that opportunity.  So with that I had determined that it was NOT using the dynmaicsintegration_key at all to do the lookup.  It was using the name.  Now this is a problem in this situation.  It seems unreasonable to have users enter the “name” of the opportunity into GP.  Sometimes those can be very long.

I came across the blog mentioned above. In this blog he was using a solution that was created orginally for mapping custom entities that have the new_ prefix and reworked it to alter the look up from name to the dynamicsintegration_key.  It seemed like a very strong-arm way to approach this issue.  But I have dealt with this long enough and needed a solution.

I did what is suggested.  I created a plugin with the code just exactly like Hosk.  Only I updated it to only look at the opportunity entity when intercepting the FetchXML.

this is what my code ended up looking like

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Xrm;
using Microsoft.Xrm.Sdk.Query;
namespace CRM2011Plugin
 public class InterceptFetchforConnector: IPlugin
 public void Execute(IServiceProvider serviceProvider)
 // Obtain the execution context from the service provider.
 Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
 if (context.PrimaryEntityName.Contains("opportunity")) //Replace with your prefix
 // tracingService.Trace("in the if statement");
 if (context.InputParameters.Contains(("Query")))
 QueryExpression query = (QueryExpression)context.InputParameters["Query"]; ;
 FilterExpression fexp = query.Criteria;
 foreach (ConditionExpression condition in fexp.Conditions)
 if (condition.AttributeName == "name")
 condition.AttributeName = "dynamics_integrationkey";

Walla it worked like a charm. (sorry the formatting is bad on the code)

I registered  two steps for the plugin

Message: Retrieve
Entity: Opportunity

Message: RetrieveMultiple
Entity: Opportunity

At this point I only need this plugin to intercept the fetch xml on opportunities.  I don’t want it to run on every retrieve.

My conclusion on this is.  With the connector it seems that any lookups are going to use the name field to find a match.  This is usually not an issue.  But my assumption was the reason for the dynamicsintegration_key was so you could map your lookups to the fields that you need.  Name is not always the one that is used. I wrote a workflow that on the creation of an opportunity it will add the Opportunity Number to the dynamicsintegration_key.

Now all my sales documents are being successfully associated with their opportunity.

Posted in Connector for Microsoft Dynamics, CRM, CRM 2011, Dynamics | 3 Comments

CRM 2011 Plugin causing “Could not load file or assembly ‘Microsoft.Xrm.Client” error

I know I am not the first person to come across this error.  I see a lot of solutions out there to resolve this error.  This is how I resolved this error.

First for a little back story.  I have not done a lot with plugins. In fact the only plugins that I had running in my system were implemented on the initial install of CRM 4.0 and they were done by the consulting company who did our install.  I finally had a need to build my own plugin.  I will be writing a blog on the plugin that I had to write because it solved a major issue that I was having with connector.

So in the sdk there is a nice walk through of creating a plugin. You can also find it here

Everything was working perfectly I followed the instructions perfectly.  except for one apparently very important step.

  1. Add all dependent assemblies to the GAC on the server. To do this, open your project’s Debug/binfolder and copy all *.dll files except for the main PluginWalkthrough.dll file to the GAC on the server.

Not doing this step did not impact the registration of my plugin.  This also did not impact the running of my plugin.  Everything was going along just fine, until I needed to run my Customer Portal  from adx studios.  I had not realized that a plugin was registered for the portal.  I also did not think that my plugin would have any impact on the portal.

I ran the portal and the site crashed.  Of course all I was getting this error to start with…

The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs

I had seen this before. It was annoying.  Where…?  Do what???  So after a little digging I found the solution on stack over flow

Here is the gist of it.  Go on to the CRM Server.  Find your program files folder and navigate to

\Program Files\Microsoft Dynamics CRM\CRMWeb

Open the Web.cofig file and find this tag <system.serviceModel>

make it look like this

<serviceHostingEnvironment aspNetCompatibilityEnabled=”true” />
                                          <serviceDebug includeExceptionDetailInFaults=”true”/>

Now you will get more relevant error messages.  And the one I was getting is that the application

Could not load file or assembly ‘Microsoft.Xrm.Client….

I thought back to the documentation and the line about the GAC.  Well it looks like I was going down that road.  In Server 2003 you could easily register assemblies in the GAC with a simple drag and drop.  Well not anymore in Server 2008.  You have to use the Gacutil.exe.  I was just looking for some simple step by step how too’s on this and I could find a lot of almost step by step, but they were all missing little pieces of info that I did not know.

so this is what I did.

Open cmd and type cd C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools

this should put you in the folder with the gacutil.exe.  To make sure you are there.  You can physically navigate to the folder or type this

gacutil.exe /?

this should spit out a bunch of help information and you know you are in the right place.

now type gacutil.exe /i fullpath and name of your dll

replace “fullpath and name of your dll” with the path and name of your dll.

You should see

Assembly successful added to the cache.

If you see this error

Failure adding assembly to the cache: this assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

Then you are using the wrong gacutil for the version of .net you compiled the dll in.

I ran the cmd for all the dependant dlls in my bin folder for my plugin and registered them all in the GAC, restart IIS and now everything is all back to normal 😀

Posted in CRM, CRM 2011, Dynamics | 1 Comment