MS CRM Rollup 8 and 9 fail with Invalid length parameter passed to the LEFT or SUBSTRING function.

While installing MS CRM Rollup 8 or MS CRM rollup 9, we received an error that looked like this:

—————————
Microsoft Dynamics CRM 4.0 Update Rollup 9
—————————
Action Microsoft.Crm.Setup.Common.Update.DBUpdateAction failed.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

—————————
Retry   Cancel  
—————————

It turns out this error is caused by an invoice having an invalid invoice number. By default, MS CRM gives invoices numbers that look something like this: INV-06881-39HRRD

In our CRM project, we had some invoice number formats being inserted manually by a remote Events Booking system we have written for the web (cool application actually). This used slightly different format invoice numbers intentionally. The solution to the problem was to either remove or renumber in the same format the invoice records. Once your invoice numbers are all as CRM expects them, then you can install either or both updates.

This is a SQL script that may help:


Execute this SQL script to generate correct invoice number, specifying 
correct length for your invoice number format 

(verify in your CRM parameters Automatic Numbering for invoice): 
@InvoiceNumberPref = 'INV' 
@invoicePART1Len = 5 
@invoicePART2Len = 6 
In this case : INV-02365-6Y5TRE 



-- Start Script ---------------------------------------- 
DECLARE @InvoiceNumberPref nvarchar(5) 
SET @InvoiceNumberPref = 'INV' 
DECLARE @invoicePART1Len int 
SET @invoicePART1Len = 5 
DECLARE @invoicePART2Len int 
SET @invoicePART2Len = 6 

DECLARE @i int 
DECLARE @j int 
DECLARE @n int 
DECLARE @strnum nvarchar(10) 
DECLARE @RandomChars nvarchar(10) 
DECLARE @RandomInvoiceNumber nvarchar(20) 

SET @j = 1 

DECLARE @invoiceid nvarchar(50) 
DECLARE @invoicenumber nvarchar(20) 

DECLARE cursor_invoices CURSOR FOR 
SELECT CONVERT(NVARCHAR(50),invoiceid), invoicenumber FROM dbo.invoice 
order by createdon 

OPEN cursor_invoices 

FETCH cursor_invoices INTO @invoiceid, @invoicenumber 

WHILE @@FETCH_STATUS = 0 
BEGIN 

SET @strnum = CONVERT(nvarchar(10), @j) 
SET @j = @j + 1 
SET @strnum = REPLICATE('0', @invoicePART1Len-LEN(@strnum)) + @strnum 


SET @RandomChars = '' 
SET @i = 0 
WHILE @i < @invoicePART2Len 
BEGIN 
SET @n = ASCII(0)+CEILING(RAND()*(ASCII('Z')-ASCII(0))) 
--exclude value range 58-64 
IF @n>ASCII(9) AND @n<ASCII('A') SET 
@n=ASCII(0)+CEILING(RAND()*(ASCII(9)-ASCII(0))) 
SET @i = @i + 1 
SET @RandomChars = @RandomChars + NCHAR(@n) 
END 

SET @RandomInvoiceNumber = @InvoiceNumberPref + '-'+@strnum+'-'+@RandomChars 

PRINT @invoiceid + ' ' + @invoicenumber + ' ' + @RandomInvoiceNumber 

UPDATE invoicebase 
SET invoicenumber = @RandomInvoiceNumber 
WHERE invoiceid = @invoiceid 

FETCH cursor_invoices INTO @invoiceid, @invoicenumber 
END 

CLOSE cursor_invoices 
DEALLOCATE cursor_invoices 

Check out our website for more CRM integration into DotNetNuke.

Digg This

DotNetNuke.Services.Exceptions.PageLoadException: Failed to load viewstate Problem Solved / Fixed

Error
An error has occurred.
DotNetNuke.Services.Exceptions.PageLoadException: Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request. —> System.Web.HttpException: Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request. at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Control.LoadChildViewStateByIndex(ArrayList childState) at System.Web.UI.Control.LoadViewStateRecursive(Object savedState) at System.Web.UI.Page.LoadAllState() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) — End of inner exception stack trace —

How we got this error.

Today while playing with a new clients site, I received this error while uploading a module. Now under normal circumstances I would run around looking at the DNN install, or the module developer to challenge them as to what is wrong, and can they fix it.

However as I was the Server Admin, and the person who setup the database and website, I had enough confidence to know that all that side of things was correct. And in this case the dnn module developer was InteractiveWebs (us). So no need to go hunting there.

So I started looking closely at the error and thinking outside the box.

Knowing all the other “ducks were in a row” I concluded that the user session state must no longer be valid. Even though I could move around in the DNN Host Account and all appeared to work fine.

Solution / Fix

  • Log our of the site
  • Log back into the site 

All was resolved! So I cannot explain exactly why the session state had expired, but I do know that if an Airbus A380 can require 20 computer resets to flying, then a DNN website can require one every now and again. Hope it helps.

Digg This

Windows Live Messenger on Multiple Monitors System Windows 7

I have a system that has multiple monitors and we use a great little application called ultramon that allows you to have a desktop tray on all monitors. The idea being that the monitor with the application running on it will have a desktop try icon to control the application.

This is good to allow you to control applications from the system try on the appropriate monitor.

It has bugged me that the Windows Live Messenger is the only program I run that kept a system try icon on the primary monitor and duplicated another icon on the second monitor where the program was running.

But there is a solution.

1. Close Windows Live Messenger

2. Search for Live Messenger in the start menu, and right click and select Properties.

3. Select Compatibility Tab

 image

4. Select run this program in compatibility mode for: Windows Vista

image

This will put the main monitor desktop icon back into the mini icon format.

image

And as they say… Bob is your fathers brother!

Digg This

Backup Exec could not find a Microsoft Volume Shadow Copy Services (VSS) software or hardware provider on the media server.

We setup a new job recently on our Backup Exec 12.5 server. One of the jobs had the following error:

Backup Exec could not find a Microsoft Volume Shadow Copy Services (VSS) software or hardware provider on the media server. To use offhost backup, a VSS provider must be installed on both the media server and on the remote computer.

image

The solution to the is actually easy. It was a mistake we made in setup of the Backup Job.

All we had to do was un check the option in Advanced Disk-base Backup.

image 

Hope this helps others.

flash content and z index

Just to set up the scene, a project I am working on requires a Flash navigation set. To tackle the obvious accessibility issues, I have created a run of the mill XHTML list of links that mirrors the Flash version exactly and hidden it from visual browsers using CSS. This is all fine. I have placed the Flash navigation set at the very bottom of theXHTML and positioned it back to the top of the page using CSS, so no major problems there.

The problem really starts when I realise that the Flash content is overlapping two other elements of the page, namely a site wide search form and a logo which doubles as a link back to the home page and, as you would expect, due to it’s position within the page structure it is on the top of this ‘stack’ meaning I can’t use the search form and I can’t see the logo,let alone click it.

Flash stacked by default

These three elements, for the sake of simplicity, are each contained within a uniquely identified div tag and are positioned absolutely. The obvious answer is to use z-index to move the two divs above the Flash content but (insert Eddie Izzard accent here) it does not work! The reason, as explained by Macromedia

A Flash movie in a layer on a DHTML page containing several layers may display above all the layers, regardless of the stacking order (“z-index”) of those layers.

That’s all a little bit upsetting I must say. Luckily, the same tech notefrom Macromedia goes on to explain how you can use the wmode tag to create a transparent background for your Flash content, thus allowing the layers underneath to show through. Support for this method is now pretty widespread for newer browsers (CHECK THISTECH NOTE FOR BROWSER SUPPORT DETAILS!). I am not overly worried about older browsers because, chances are, I am going to serve up a simplified version for Netscape 4 and his buddies anyway and, as I am using Flash MX specific ActionScript to deliver the content, the user will have to have Player 6 to see the content, so no worries about older versions of the player; such are the decisions you have to make when working with Flash!

Okay, so using Drew’s Flash Satay method my code for the Flash content looks something like this

  1. <object
    type=”application/x-shockwave-flash” data=”/flash/navbar.swf” 
    width=”750″ height=”90″>
  2. <param name=”movie” value=”/flash/navbar.swf” />
  3. <param name=”wmode” value=”transparent”>
  4. </object>

Line three is the key to the whole thing and adding this, as said, I can now see my logo and my search form, I still can’t get to them to use them though!

Flash with transparent background applied

This is where the tech note got a bit fuzzy for me and the reason I am making this post; it says

Newer browsers add the ability to layer Flash content with DHTMLcontent and in some cases the ability to use transparent backgrounds in the Flash content.

This, to me at least, suggests that I shouldn’t have run into the problems I did when using a newer browser, such as Firefox or Internet Explorer 6 (Update: Reading this back maybe the DHTML bit should have been a clue as to why I hit problems, I don’t have the D bit!). The z-indexing should have worked straight out of the box but it didn’t.

So, as I understand it thus far, I would say that, for the moment at least, you must include the wmode tag if you want the ability to ‘layer’ the flash content using CSS alone.

With this done and the appropriate CSS in place (e.g. #id{position:absolute; z-index:1;}. My search form, flash navigation and logo are all nestling happily together at the top of the page and I can go and have a nice glass of wine! hooray!

Flash stacked with transparent background and z-indexing

Exchange Server 2010 default domain set for outlook web access

We wanted to set the default domain for users logging into outlook web access on Exchange server 2010. By doing this, users would not have to enter the domain\username and would be free to enter just the user name. This is much easier for the typical user of email servers, who lets face it have trouble enough remembering their own name!

To do this we performed the following steps.

Go to Program Files\Microsoft\Exchange Server\V14\ClientAccess\Owa\14.0.639.21\scripts\premium

Browse to the 14.0.682.1 folder instead of you have the latest rollup.

Open the flogon.js file in notepad

ctrl + f and find the text clkLgn(){addPerfMarker

after the “clkLgn(){” and before the “addPerfMarker” paste the following. Remember to change DOMAINNAME with your own domain name with no slashes.

NOTE: BE SURE THAT YOU PASTE TO NOTEPAD TO VERIFY ” CHARACTERS ARE CORRECT AND ARE NOT 6699’s BUT ARE CORRECT BLOCKQUOTES.

var myDomain=”DOMAINNAME” + “\\”;if(gbid(“username”).value.indexOf(myDomain)==-1){gbid(“username”).value=myDomain+gbid(“username”).value;}

your end result should resemble something like this

clkLgn{var myDomain=”mysampledomainname” + “\\”;if(gbid(“username”).value.indexOf(myDomain)==-1){gbid(“username”).value=myDomain+gbid(“username”).value;}
addPerfMarker

That was it.

Now when a user types their name and pass, the form will automatically log them in using the default domain.

Exchange Server 2010 Organization Prerequisites Forest Functional Level 2003

Today I was installing exchange server 2010 and found that the organisation pre-requisites failed with the following message.

Organization Prerequisites

Failed

Error:

The forest functional level of the current Active Directory forest is not Windows Server 2003 native or later. To install Exchange Server 2010, the forest functional level must be at least Windows Server 2003 native.

Now I went in a look at the domain functionality level and found that the domain was already set to Windows server 2003, but what I never realised previously was that the forest functionality level is set separately. Instead of right clicking on the domain and selecting raise functionality level you click on the active directory domains and trusts and select raise functionality level in there.

Once this was completed the installation of the exchange server 2010 was able to proceed without the phone message. Just a little tip for exchange server 2010 installation.

Microsoft Word 2007 Crashes – Why?

I have an otherwise stable work environment, yet today I discovered the root cause why I sometimes have to reinstall office 2007 to get MS Word to stop crashing.

The symptoms are that word started and appears to work ok, but I cannot select any text either right click with mouse or left. If I close the program, the application crashes with all the normal bull crap about recovery and bla bla.. end result is that word remains broken.

After much research and mucking around (save you all some time) I found that you have to reinstall. Repair is just a moving progress bar connected to nothing, as I have never seen the repair install process actually do anything useful.

But what I am excited about this time is that I worked out the cause. If MS word is open with a document that is not saved, and the operating system does a reboot after a service patch (or should I say… another services patch) then MS word tries to do some tricky save and restore stuff. When you open your operating system again, word is there, your documents are there, but it is all broken and needing to be reinstalled.

My suggestion. Turn off auto updates, just set it to download. Then make sure that you close word and check it is closed with a task manger (not some crap vista or windows 7 closed where the program remains hung and open but does not bother to tell you), then install updates and reboot (again) if needed.

Guess you can tell I have been doing this for a while…  and I am still surprised that the applications I use get less stable not more!  nuff said..