Tuesday, August 20, 2013

Friday, August 16, 2013

"Conversion failed when converting date and/or time from character string." when ORDER BY CASE

Problem
My query looks like this
ORDER BY
CASE @OrderBy
       WHEN 'A' THEN A
       WHEN 'B' THEN B
      etc...
END
DESC

I was getting the "Conversion failed when converting date and/or time from character string."when @OrderBy was set to certain values.

Solution
You need to use a separate case for each possible OrderBy. So I had to turn the query above into

ORDER BY 
    CASE WHEN @OrderBy = 'A' THEN A END DESC,
    CASE WHEN @OrderBy = 'B' THEN B END DESC


Reference
1. http://stackoverflow.com/questions/10002401/conversion-failed-when-converting-date-and-or-time-from-character-string 

Dexterity - Change the Series of global script after it's been created

Problem
I wrote a simple global procedure that calls a stored proc. When i went to execute it from GP i was getting an error message saying

Unhandled script exception:
[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'DYNAMICS.dbo.myStoredProc'.

Solution
The problem is this script has a series of "DYNAMICS", but it's actually created in the company database by my installation script.

I went to change the series of the script, but it appears to be greyed out.

Simple solution in Dexterity
1. Click on the script
2. Explorer > Export to Text File
3. Open the script with any text editor
4. At the top of the script you'll see Core "System"
5. In my case I want to this to be in the company database, so I changed it to Core "Inventory"
6. Save the file
7. Explorer > Import from Text File
8. Find the file you edited
9. Import it

Voila! You bypassed Dexterity's asinine read-only on the Series field!

Monday, August 5, 2013

Unexpected error when deactivating a contact in CRM 2011

Solution
Run this SQL script on your CRM server:
Update AccountBase set Merged = 0 where Merged is Null
Update ContactBase set Merged = 0 where Merged is Null 
 
 
 
 
 
 
Reference
http://social.microsoft.com/Forums/en-US/aa70fc81-c9a7-4879-91f6-896481ae6450/unexpected-error-trying-to-deactivate-accounts-in-crm-2011  
There was an error in this gadget