Changing SQL dB from Single User Mode

Recently, a CRM SQL Server 2005 dB was turned into Single User mode. This means that only one user may connect to the dB at a time, and if a service is connected, then you can't connect.

It took some research and elbow grease, but this is how I changed it back to multi-user mode.
(I am using 'database_MSCRM' as my database name for my examples.)

I ran this SQL query to find the active connection to the dB that is not my own:
select spid from master..sysprocesses where dbid = db_id('database_MSCRM') and spid <> @@spid

I got the ID, which was 52. Then I ran this query

Kill 52
ALTER DATABASE database_MSCRM SET MULTI_USER

That killed the connection, then changed the setting to multi-user. I tried to run them separately, but the service was reconnecting so quickly that the Alter command wasn't valid.
I decided to run them together, and it worked great!

Good luck,
Mark

reference: http://msdn2.microsoft.com/en-us/library/aa933230.aspx
reference: http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx

CRM 4.0: Trouble with redeploying existing organization

So this week was very interesting. We had a client who wanted to go from the hosted Dynamics CRM 3.0 to an on-premise Dynamics CRM 4.0 Professional edition.

Since they wanted to keep their hosted 3.0 system functional, I built out a new server with:

• Windows Server 2003, w/ SP2
• SQL Server 2000, w/ SP4
• All windows updates, including .NET FMWK 3.0

We redeployed CRM 3.0 to the new server, then upgraded the SQL installation from 2000, SP4 to 2005, SP2. We ran the SQL update script to change the buildversion of the CRM database to comply with CRM 3.0 On-Premise. The SQL script is listed below:

select *from buildversion
update buildversion set buildnumber = 5300
update buildversion set revision = 0
update buildversion set minsupportedclient = '3.0.5300.0'

After you run the SQL script to upgrade the database, it will be able to appear in the CRM 3.0 On-Premise installation (when you connect to an existing installation).

After installing CRM 3.0, we upgraded to CRM 4.0 and backed up the database to transfer to the client's new CRM 4.0 environment.

Redeployment for CRM 4.0 has been designed to be system admin friendly.

• You install CRM 4.0 on the new server
• Rrestore the existing CRM 4.0 Org_MSCRM database to SQL 2005
• Access the MMC snap-in Deployment Manager
• Import Organization (and you are done.)

I walked through all of the steps on my own test server before attempting it on the client's environment. My test worked perfectly smooth.

The actual execution wasn't quite so picture-perfect. Yeah...we ran into different issues while trying to restore the database to the new SQL Server.

One of the errors we received was: Error 3154: The backup set holds a backup of a database other than the existing database.

After doing some research, I found that it was a semi-common problem in SQL and that you had to use a REPLACE clause when executing this statement. Since I couldn't execute the restore through the GUID, I decided to try to execute the restore manually through a SQL script. This is the script that I ended up writing. (oh the client wanted the sql database files to exist on a different harddrive, hence the MOVE clause):

Query:
RESTORE DATABASE CRMName_MSCRM
FROM DISK = 'E:\InstallDBs\CRMName_MSCRM-Full Database Backup.bak'
WITH REPLACE,
MOVE CRMName_MSCRM' TO 'E:\sqldata\CRMName_MSCRM.mdf',
MOVE CRMName_MSCRM_log' TO 'E:\sqldata\CRMName_MSCRM_log.ldf'

Results:
Processed 59816 pages for database CRMName_MSCRM', file CRMName_MSCRM' on file 1.
Processed 4 pages for database CRMName_MSCRM', file CRMName_MSCRM_log' on file 1.
Processed 17 pages for database CRMName_MSCRM', file 'sysft_ftcat_documentindex_eacc0dc2b9b94e2c98d072e33598dfe6' on file 1.
RESTORE DATABASE successfully processed 59837 pages in 32.453 seconds (15.104 MB/sec).

Using the above script, it appeared to Restore correctly. The importing of the organization failed in a later step. Since that didn't work, we had to figure something else out.

The solution:

I mentioned that I had restored this database to my own test server. I stopped all of the SQL Services that were running, physically copied the mdf and ldf (SQL database and log) from my test server to the client server. The mdf and ldf files were ATTACHED to the client's SQL Server and then we executed the Import Organization against that attached database. The import took about 10 minutes, and WORKED! (That was the most nerve-wracking progress bar I have ever watched.) I think we could have also used a copy of the mdf and ldf from the upgraded database (from 2000 to 2005). So if you can't restore your database into the target SQL Server, you may want to try our workaround:

• Install CRM 4.0 on target CRM Server
• Stop all SQL services on upgraded CRM/SQL Server (source)
• Copy MDF and LDF files from source to target server
• Attach MDF and LDF files to target SQL Server
• Use Deployment Manager to Import Organization

Good luck in your upgrades! We worked until almost 3AM in order to land upon this solution ...hopefully your chances are improved by this post!

CRM 4.0 Trial License Keys

Since our Gold Partner CRM 4.0 development key hadn't come in, I needed a key to test some install and upgrades for 4.0. I found these trial keys on the Microsoft site and wanted to pass it along.

Workgroup: Offers the same rich feature set as Microsoft Dynamics CRM 4.0 Professional Server. Includes and supports a maximum of 5 named User licenses. Use the following 90-day license key to unlock Microsoft Dynamics CRM 4.0 Workgroup Server: V8QCJ-74RXV-FF27W-TY3V2-92MJY

Professional (Pro) Server: Offers a rich feature set, and supports single tenant deployment. Use the following 90-day license key to unlock Microsoft Dynamics CRM 4.0 Professional Server: P6MYC-RPKCC-QWWCM-6MTJ3-RT3DY

Enterprise Server: Offers a rich feature set, and supports multi-tenant deployments. Use the following 90-day license key to unlock Microsoft Dynamics CRM 4.0 Enterprise Server: QCKXD-BDH8R-G74H8-RDYHX-R4K4T

*IMPORTANT! You may use the Microsoft Dynamics CRM 4.0 editions with the respective License Keys for a maximum of 90 days. Each edition may be converted to a full use version by applying a commercial license key in the Microsoft CRM License Manager. A re-install of the software is not required. You will need to convert the license key to a commercial license before the 90-day period ends, as the 90 day period cannot be extended, and Users will not be warned of the impending expiration. Should expiration occur, access can be regained by applying a commercial license key.

Dynamics CRM 4.0 SDK

This is taken directly from a MS Readme, but I thought it was useful. When I sink my teeth into the SDK, I will post my thoughts on it.

Microsoft Dynamics CRM SDK
The Microsoft Dynamics CRM SDK is available for download. This download includes sample code and an installer for viewing the documentation in Microsoft Visual Studio 2005. The Microsoft Dynamics CRM SDK is for developers, system customizers, and report writers. It contains the following sections:

Server Programming Guide.
A guide for developers who write server-side code, custom business logic, plug-ins, integration modules, custom workflow modules, and more. This guide has an architectural overview of Microsoft Dynamics CRM, the entity model, security model, Web services, and sample code.

Client Programming Guide.
A guide for developers who want to customize the Web application or Microsoft Dynamics CRM for Outlook. This guide includes forms scripting, integration of custom Web pages and other UI, and sample code.

Report Writers Guide.
A guide for developers who write reports for Microsoft Dynamics CRM using Microsoft SQL Server Reporting Services Report Designer.

Note: The Microsoft Dynamics CRM SDK is available only in English and Japanese.

CRM 4.0 Language Packs

For those of you who need to help setup CRM for users needing more than just English, here are the CRM 4.0 Userpack and Readme links:

Microsoft Dynamics CRM 4.0 Language Pack...

Microsoft Dynamics CRM 4.0 Language Pack Readme...

CRM 4.0: E-mail Router download

I was doing a complete test upgrade this week and it was successful. As you are aware, you can not upgrade the CRM 3.0 E-mail router, but instead have to uninstall the previous version before installing the 4.0.

After uninstalling the CRM 3.0 E-mail router, I tried to follow the instructions to install the CRM 4.0 router. I looked for the files in the "Exchange" folder like the asked, but could not find this "Exchange" folder. I could not locate the files to begin the install!

I searched Microsoft's download site and found this link to download the E-mail router separately. Still not sure why it wouldn’t be included where it should be, but if you can't find it, here is the link.

Download details:
Microsoft Dynamics CRM 4.0 E-mail Router (On-Premise and Microsft Dynamics CRM 4.0 Service Provider Editions)...

Microsoft Dynamics CRM 4.0: How to configure the on-premise E-mail Router in different deployment scenarios...

Microsoft Dynamics CRM 4.0 E-mail Router Readme (On-Premise and Microsoft Dynamics CRM 4.0 Service Provider Editions)...

CRM 4.0 Activity Synchronization

The Microsoft Dynamics CRM 4.0 Installing Guide for the new features in CRM of Outlook reads: "Activity tracking and synchronization improvements are included. Microsoft Office Outlook tasks can now be mapped to Microsoft Dynamics CRM letters, faxes, tasks, and phone calls."

I always thought it was strange that CRM 3.0 Activities of TASK and APPOINTMENT were the only items that mapped into Outlook. In my experience, the synchronization of CRM Tasks to Outlook has never been that useful, since Outlook only will remind you about your tasklist once a day. If you would like to be reminded of something to do during the day, then you should set up the CRM Appointment, since it will synchronize with your Outlook calendar and therefore remind you at the appropriate time.

Even though the other activities can now be mapped to your Outlook Tasks, I think using Appointments is still the way to go for functionality purposes.

What's new in CRM 4.0

I know this has been done on a lot of blogs, but here is my recap on the new features of version 4.0:

• Multi-tenancy (Host multiple organizations in a single deployment)
• Differentiated Server Roles (Application vs Platform Server)
• 4.0 Connector for SQL Reporting Services (eliminated Kerberos douple-hop authentication)
• 4.0 Language Pack (user language can differ from base language)
• Multicurrency (supports multiple currencies)
• Improved Data Management (migrate to all entities, save mappings, de-dupe)
• Improved Customization features
• Workflow (located in GUI)
• 4.0 E-mail Router (not limited to Exchange Server; POP3)
• MS Dynamics CRM for Outlook (Diagnostics Wizard; Performance improvements)

Visit the Microsoft website for all of the features.

Basic Information on Dynamics CRM 4.0

Dynamics CRM 4.0 Editions:

• Workgroup (Limited to 5 or fewer users; single organization; single computer running MS Dynamics CRM Server)
• Professional – No user limit; limited to a single organization; can be installed on more than one computer in the same deployment
• Enterprise – no user limit; support from multiple organizations, multiple server instances, role-based service installations (lets you increase performance by installing component services on different computers)

Licensing – all deployments operate by using a single license key (contains Dynamics CRM version, server license and the CALS)

CAL Types (Client Access License Types)

• Read Only – view records and data, cannot modify records or data (uses 1 CAL)
• Administrative – can modify records in data that is located in the settings area only (uses 0 CAL)
• Full – full functionality and are only limited by specified security roles and privileges (uses 1 CAL)
  
  

Auto-fill Opportunity's topic attribute

This might seem really simple, but sometimes it is really useful.

A lot of people don't know what to write in the TOPIC field of an opportunity record. If you don't write something, then the record appears to be unnamed to the user.

You can auto-fill the topic several ways:
1. You can have all Opportunities have a default name:
//OnLoad Code
if (crmForm.all.name.DataValue==null)
{
crmForm.all.name.DataValue="Opportunity";
}

2. You can set the CustomerID name into the TOPIC field:
//This will name the Opportunity after the client.
//OnChange for the CustomerID attribute
crmForm.all.name.DataValue = crmForm.all.customerid.DataValue[0].name;

Using User's Business Unit

This is something that I have done before, and it utilized Michael Höhne's code again. I made a modification to the User/Datetime Stamp that I previously posted.

In this code, I use the XML call to get the current user's business unit, and use this information to set a picklist. This "defaults" the picklist for the user, based on what his/her business unit normally picks.

Practical uses: This was requested by a client, to ensure that Opportunities were typed according to their business unit, so that the pipeline would be trackable by what business unit the user was in at the time.


(Sorry for the screenshot: Again, I don't know the best way to post XML without it rendering directly in the webpage.)

FAQ on using the Bulk Import Tool

Here is a good link off of the MSFT CRM site about some FAQ on using the Bulk Import Tool in CRM.
http://www.microsoft.com/dynamics/crm/using/troubleshooting/tsbulkimport.mspx

This is also a good paper to view:
http://www.consultcrm.co.uk/documents/knowledge/Troubleshooting%20bulk%20import.pdf

Datetime User Stamp

I modified some of Michael Höhne's genius to compile an excellent datetime/user stamp.

Example Scenario: Checkbox on the Opportunity form called "Reviewed" When a user checks the bit that it is reviewed, you can kick off this action to automatically capture the current user's name and the datetime in other fields.

I normally make the new_text and new_date fields disabled and then use OnSave code to ForceSubmit those fields. This ensures that those fields can't be tampered with.

You can do other modifications to this, such as: disabled the checkbox once it is checked. This ensures that it can't be rechecked and have the datetime/user information changed in the future. If you do this, then you should place some JS in the OnLoad to check for that bit's value. If it is true, then disable it, so that it won't be accessible again.

Note: when doing this, it ties your hands if someone accidently checks the box and then saves the form. Remember, just be logical. :)

Please comment and be sure to check out Michael's site.

I couldn't figure out how to get the code formatted cleanly on the webpage without the tags rendering. If you have a better solution than "xmp" (it just isn't friendly). Please let me know and I will repost it.

Thanks.

Non-Sequential Dynamic Picklist

I know I haven't posted to my blog in a long time because I have been busy with work.

Here is something that I finished up today.

I needed to create a set of dynamic picklists that could be non-sequential. The SDK gives a solution for a sequential based picklist, but that doesn't take in account for additional values in the future. I found this post by Greg Owens' post here and have edited and rearchitected parts of it. Since he helped me out, I have decided to post it to help you out!

Feel free to post comments. Thank you again Greg!

//****************************************************************
//**Non-Sequential Dynamic Picklist
//**------------------------
//**Developers: Greg Owens (Initial structure)
//** Mark Nagao (Rearchitecture)
//**Date: December 17, 2007
//**Purpose: Dynamic SubPicklist of non-sequential values
//**MSCRM 3.0: OnChange Event Code
//****************************************************************
//****************************************************************
//**Assign your main and sub picklist
var oPicklist= crmForm.all.new_mainpicklist;
var oSubPicklist = crmForm.all.new_subpicklist;

//**End of Assignment editing
//****************************************************************

//Saving and Resetting Original Picklist Values
if(!oSubPicklist.originalPicklistValues)
{
oSubPicklist.originalPicklistValues = oSubPicklist.Options;
}
else
{
oSubPicklist.Options = oSubPicklist.originalPicklistValues;
}

//****************************************************************
//**Add Arrays and specify values to display in SubPicklist

var oArray1 = new Array(0,1,3,4);
var oArray2 = new Array(0,2,5,6);


//**End of ARRAY Editing
//****************************************************************

//Check that Main Picklist value is not Null
if(oPicklist.DataValue != null)
{
switch(oPicklist.SelectedText)
{

//****************************************************************
//**Edit Case: Add a case for each Array

case "Array1":
filterPicklist(oArray1);
break;

case "Array2":
filterPicklist(oArray2);
break;


//**End of CASE editing
//****************************************************************

}
}

//Function to Filter SubPicklist
function filterPicklist(oDesiredOptions)
{

//Create temporary array to hold selected Values
var oTempArray = new Array();
//FOR loop to cycle through all SubPicklist Values
for (var i=oSubPicklist.length;i >= 0;i--)
{
//FOR loop to cycle through desired SubPicklist Values
for (var j=oDesiredOptions.length;j >= 0;j--)
{
//Compare i(SubPicklist Value) and j(Desired Value)
if (i == oDesiredOptions[j])
{
oTempArray[i] = true;
oDesiredOptions.splice(j,1);
}
}
}
//FOR loop to cycle through all SubPicklist Values
for (var i=oSubPicklist.length;i >= 0;i--)
{
//Compare with Temporay Array
if(oTempArray[i] != true)
{
//Remove Value from list if not TRUE in Temporary Array
oSubPicklist.remove(i)
}
}
}

//****************************************************************
//**Notes:
// - Place code in the OnChange of the Main Picklist
// - In the array, you must have two values, so if there is
// only one value, then include the 0 (null) value
//****************************************************************
//**End of Non-Sequential Dynamic Picklist Code
//****************************************************************

MS CRM 3.0.5 SDK

A very helpful resource is the Software Developer's Kit (SDK) from MS. Here is a link to the latest version that was released on 6/26/06:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&DisplayLang=en

The SDK has samples of code for advanced functionality-all with syntax that has the blessing from Microsoft...which is always good because of the guarantee of compatability with the next version.

Running .Net Framework 1.1x and 2.0

I recently came across this problem of needing both .Net Frameworks on a crm server. When installing sql server 2005, .net 2.x is installed on the server. In order to "grandfather" the 1.1x on the server, you need to install the .net 1.1x framework before the sql server 2005.

Here are the links for .Net Framework 1.14

Microsoft .NET Framework Version 1.1 Redistributable Package
http://www.microsoft.com/downloads/details.aspx?FamilyID=262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en

Microsoft .NET Framework 1.1 Service Pack 1
http://www.microsoft.com/downloads/details.aspx?FamilyID=a8f5654f-088e-40b2-bbdb-a83353618b38&DisplayLang=en

JavaScript Math Functions

Since CRM 3.0 uses JS natively in the OnLoad and OnSave events, I have decided to post several key math functions in JS.

Also remember to use parentheses around the condition when writing an if statement, such as:
if (condition)
{
action;
}

//General math calculations
+ //Addition
- //Subtraction
/ //Division
* //Multiplication
% //Modulus (remainder after division)
++ //Increment by 1
-- //Decrement by 1

//Higher math calculations
MATH.ABS(X) //Absolute value of X
MATH.ACOS(X) //Arc Cosine of X
MATH.ASIN(X) //Arc Sine of X
MATH.ATAN(X) //Arc Tangent of X
MATH.ATAN2(X,Y) //Arc Tangent of X/Y
MATH.CEIL(X) //Integer closest to X and not less than X
MATH.COS(X) //Cosine of X
MATH.E //Euler's constant
MATH.EXP(X) //Exponent of X
MATH.FLOOR(X) //Integer closest to X and not greater than X
MATH.FLOOR(MATH.RANDOM()*10) //Returns random value between 0 and 9
MATH.LN10 //Natural Log of 10
MATH.LN2 //Natural Log of 2
MATH.LOG(X) //Log of X base E
MATH.LOG10E //Base-10 Log of E
MATH.LOG2E //Base-2 Log of E
MATH.MAX(X,Y) //Returns the maximum value of X or Y
MATH.MIN(X,Y) //Returns the minimum value of X or Y
MATH.PI //Pi
MATH.POW(X,Y) //X to the power of Y
MATH.RANDOM() //Returns random value between 0 and 1
MATH.ROUND(2.8) //This would equal 3
MATH.SIN(X) //Sine of X
MATH.SQRT(1_2) //Square Root of 1/2
MATH.SQRT(2) //Square Root of 2
MATH.TAN(X) //Tangent of X

//Operators
X=Y //X equals Y
X+=Y //X equals X plus Y
X-=Y //X equals X minus Y
X*=Y //X equals X multiplied by Y
X/=Y //X equals X divided by Y
X%=Y //X equals X modulus by Y
== //Equivalent
=== //Exact equivalent
!= //Not equal to
> //Greater than
< //Less than
>= //Greater than or equal to
<= //Less than or equal to
&& //And
//Or
! //Not

There are a lot of other powerful functions or more complex ways of putting these above listed functions together, but this should get you started.