Write Event Log error -> Exception: Unhandled Exception: System.Security.SecurityException: Requested registry access is not allowed.

When trying to write an event log, sometime we hit the error "Requested registry access is not allowed."

It's due to permission on registry entry for event log.

Solution:
1. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog.
2. Right-click Eventlog, and then click Permissions.
3. Add Everyone and give Full Control permission.

Update - Microsoft Certification

Yeah just finished my second CRM exam as MCTS and received my MCP ID & Access Code (been delayed for a week due to Iverson's admin key in my email as "jhotmail.com").

Spent some time to work on my virtual business card. I like the avatar actually, pretty cool where it took in Cherating valentine trip :).


Cool??

2 exam down and 2 more to go.
Targeted to complete the rest within 2month to achieve my MCITP - Developer for Microsoft Dynamics CRM 4.0.

Stay update.........

Updated :
Got my another cert on 29 October 2010.
3 down and 1 more to go...

Share Reassigned Records with Original Owner

In the Settings --> Administration --> System Settings window, right on the General Tab. There is a section marked as "Set whether reassigned records are shared with the original owner". You can then choose whether or not to share reassigned records with the original owner.

What this does is any time you reassign a record it will share the record with the previous owner. Not just the original creator, but every owner after.

Note : This will not undo anything that is already shared, but it will stop the system from sharing anything more if you don't want it to.

Sorting custom entities for security roles in CRM

We are busy with a huge crm implementation and currently have about 100 custom entities.

When we started to look at the security matrix for the system and it became a nightmare to set the privileges on the security roles as CRM does not sort the custom entities alphabetically.

I found a solution where add sorting to custom entities tab in security roles.

Locate the file called edit.aspx located at:
\Program Files\Microsoft Dynamics CRM\CRMWeb\Biz\Roles\edit.aspx

Add the following code in bottom of the page:

<script type="text/javascript">

var tbl = document.getElementById('tab7').children(0).children(0).children(0).children(0).children(0);
var x = [];
for (var i = 0; i < tbl.rows.length; i++)
x[i] = [tbl.rows[i].cells[0].innerHTML,tbl.rows[i].outerHTML];
var str = x[0][1];
for (var i = 1; i < x.length - 1; i++)
    for (var j = i + 1; j < x.length; j++)
    if (x[i][0] > x[j][0]) {//sort by alphabet
    var tmp = x[j];
    x[j] = x[i];
    x[i] = tmp;
}
 
for (var i = 1; i < x.length; i++) {
var rows = x[i][1];

    if ((i % 2) != 0) {//background color for odd number rows
        if (rows.indexOf('<TR class=on>') > -1) {
            rows = rows.replace('<TR class=on>', '<TR>');
        }
    }
    else {//background color for even number rows
        if (rows.indexOf('<TR class=on>') < 0) {             
            if (rows.substring(3, 5) == 'TR') {
                rows = rows.replace(rows.substring(3, 5), 'TR class=on');
            }
        }
    }
    
    str += rows;
}
document.getElementById('tab7').children(0).children(0).children(0).children(0).innerHTML = '<TABLE class=ms-crm-Form-Area onmouseover=SetAltText(); ondblclick=ProcessClick(); onclick=ProcessClick(); cellSpacing=0 cellPadding=2><COL><COL width=70><COL width=70><COL width=70><COL width=70><COL width=70><COL width=70><COL width=70><COL width=70>'+ str + '</table>';
 
</script>

Note:

1. This is unsupported. Please do backup before editing.

Collapsable CRM section

attachCollapsableToSections();

function getElementsByCondition(condition, container) {
    container = container || document;
    var all = container.all || container.getElementsByTagName('*');
    var arr = [];
    for (var k = 0; k < all.length; k++) {
        var elm = all[k];
        if (condition(elm, k)) arr[arr.length] = elm;
    } return arr;
}

function attachCollapsableToSections() { 
    var sections = getElementsByCondition(function(elm) {
        if (elm.className.indexOf("ms-crm-Form-Section") != -1)
            return true;
    }, null); 
    for (var i = 0; i < sections.length; i++) {
        sections[i].innerHTML = '<img  id="' + i + '_imgid" src="/_imgs/navup.gif" alt="Expanded, click to collapse"  style="cursor:hand;"/> <label id="' + i + '_lblid" />' + sections[i].innerHTML;
        sections[i].childNodes[0].attachEvent('onclick', toggleVisibility);
        sections[i].childNodes[2].attachEvent('onclick', toggleVisibility);       
    }
} 
function toggleVisibility(e) { 
    var sectionContainer = e.srcElement.parentNode.parentNode.parentNode;
    var elements = getElementsByCondition(function(elm) {
        if (elm.vAlign == "top") return true;
    }, sectionContainer);
    for (var i = 0; i < elements.length; i++) {
        if (elements[i].style.display == "none") {
            elements[i].style.display = "";
            if (e.srcElement.id.indexOf("_lblid") != -1) {
                var _imgid;
                _imgid = e.srcElement.id.replace(/(^\d+)(.+$)/i, '$1') + "_imgid";
                document.getElementById(_imgid).src = document.getElementById(_imgid).src.replace("navdown", "navup");
            }
            else {
                e.srcElement.src = e.srcElement.src.replace("navdown", "navup");
            }
        } else {
            elements[i].style.display = "none";
            if (e.srcElement.id.indexOf("_lblid") != -1) {
                var _imgid;
                _imgid = e.srcElement.id.replace(/(^\d+)(.+$)/i, '$1') + "_imgid";
                document.getElementById(_imgid).src = document.getElementById(_imgid).src.replace("navup", "navdown");
            }
            else {
                e.srcElement.src = e.srcElement.src.replace("navup", "navdown");
            }
        }
    }
}

LINQ on CRM SDK 4.0.12

1. Download here.
2. Generate linq code for CRM, from a command line – go to the SDK folder/Microsoft.xrm/Tools and run the following command line – replacing MyCrmServer with your actual server name, replace MyCrmOrg with your actual CRM Org and MyEntitiesFolder with the name. Note : This assumes using integrated authentication for on-premise – support is available for many different scenarios – check the docs.

crmsvcutil /connectionString:"Authentication Type=AD; Server=http://servername/crm_orgname; User ID=domain\username; Password=password;" /out:"C:\Entities"

I’m very excited to talk about the new LINQ to CRM libraries available recently. Say you need to retrieve an Account Number, given the Account Name…

Using the traditional webservice methods:

ColumnSet cols = new ColumnSet();
cols.AddColumns(new string[] {"accountnumber"});

QueryByAttribute query = new QueryByAttribute();
query.ColumnSet = cols;
query.EntityName = "account";
query.Attributes = new string[] {"name"};
query.Values = new string[] {"Microsoft Dynamics CRM"};

RetrieveMultipleRequest req = new RetrieveMultipleRequest();
req.Query = query;
req.ReturnDynamicEntities = true;
RetrieveMultipleResponse resp = oService.Execute(req) as RetrieveMultipleResponse;
BusinessEntityCollection becMultipleRecords = resp.BusinessEntityCollection;
if (deSalesOrder.Properties.Contains("accountnumber"))
{
//And So On
//And So Forth
}

Using LINQ to SQL:

var accounts = (from a in context.Accounts
where a.Name.Equals("Microsoft Dynamics CRM")
select a).ToList();
LINQ2CRM.Account acc = (Account)accounts.First();
Response.Write("Account name = " + acc.Name.ToString());

CRM 4.0 & .NET 4.0

For those that have had a chance to play with Visual Studio 2010 RC and .NET 4.0, you may have considered the possibility of writing CRM 4.0 extensions with this new framework. Unfortunately, this is not to be.

So what’s the problem exactly? The big jump is that Microsoft .NET 4.0 actually ships with a new version of the Common Language Runtime (CLR). It’s a bit confusing, but .NET 3.0 and .NET 3.5 actually used the .NET 2.0 CLR. This is because .NET 3.0 and 3.5 were really just a set of extensions to the framework and did not require an actual new runtime. This means that ASP.NET 2.x to 3.5 all used the same .NET 2.0 runtime! As such, Microsoft CRM 4.0 has no problem working with extensions that use the .NET 2.0 CLR, and takes advantage of framework extensions found in .NET 3.0 and 3.5.


So whether you run an in process plug-in or an asynchronous one, if you try to write a plug-in that uses the .NET 4.0 CLR you will get an error that looks something like this:

Microsoft.Crm.CrmException: Assembly cannot be loaded from C:\Program Files\Microsoft Dynamics CRM Server\server\bin\assembly\testing.dll. ---> System.BadImageFormatException: Could not load file or assembly 'file:///C:\Program Files\Microsoft Dynamics CRM Server\server\bin\assembly\testing.dll' or one of its dependencies.



This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
 
For web pages/custom pages,
In theory, if you could get the CRM web site to run under ASP.NET 4.0, this would get around this error. However I am sure plenty of other things would break and this doesn’t help with plug-ins hosted by the Outlook client or the Asynchronous Service.
 
If you want to use .NET 4.0 with CRM 4.0, you are not entirely out of luck. As long as it runs in its own process space, either on the client (like a Silverlight 4.0 control) or on another web site (like an IFRAME or a pop-up dialog from a CRM form), things should work.
 
The good news of course is that Microsoft CRM 5.0 will be written take full advantage of .NET 4.0, WF 4.0 and more.

Hibernate and Sleep with Windows Server 2008 R2 Hyper-V

I just received my new laptop last few weeks with installed Windows Server 2008 R2 64bit .

I have found that as soon as we install the hyper-v role hibernate and sleep are disabled. This is because the once hyper-v service is enabled, it disables the support for hibernate and sleep.


While there is no way to enable the hibernate or sleep feature while the hyper-v service is running, you can control when the service is started by changing Start parameter of the hvboot service :

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\hvboot]
 
The Start property of a service can have the following values:

• 0=Boot
• 1=System
• 2=Auto
• 3=Demand
• 4=Disabled

Set the value to 3, so that you can start the service when you want. 0 is not supported for hvboot.

Reboot the server. Hibernate and sleep should now be enabled for you till the time you start the hvboot service.

However, now if you try to start a virtual server in the hyper-v console you will get an error saying that the hyper-v service is not running and you will have to start it manually using the net start command. To do this, start command prompt under administrator privileges and execute the following command to start the service.

net start hvboot

Now you should be able to start your virtual machines. However, as a side effect hibernate and sleep will be disabled till you restart you machine.

Tweaking with the registry can be dangerous so for those who want a safe alternative, you can use the following command to set the Start property of the hvboot service as well:

sc config hvboot start= demand

The other values that you can use are system, auto, demand and disabled. Boot is not supported.

Note:

1. Space between = and demand. The syntax of service config requires this space.

Connect to CRM Web Service without impersonation

Today I'm doing CRM deployment to another server/organization. I get "The request failed HTTP status 401: Unauthorized"

I scratched my head about 2 hours. Double confirmed x 100 times on my CRM service account's access, permission. There are all fine and nothing wrong with that.

Finally, I found a very intersting KB from Microsoft.
http://support.microsoft.com/kb/948746

Wohh, now I only know the root cause.
"To work around this problem, use Deployment Manager to change the name of the organization so that the display name of the organization does not contain blank spaces."

Alternatively, I found out that, without changing in Deployment Manager, we can remove the blank space of organization name when calling the CRM Web Service. (eg, My Org to MyOrg).

Microsoft’s Product Logo

Microsoft provided official product logo download for us.

Check this out.

http://www.microsoft.com/Presspass/gallery/ms-logos.mspx?FINISH=YES

createdon vs overriddencreatedon

When we create a record in CRM, the created on field always points at the date the record is created. Of course we could go into the database and update the date by using a SQL script. But then again, “it's not supported".

And what we found from CRM SDK. createdon is a Read-only field and can’t be updated and created.

Platform required : No
Application requirement level : Read-only
Valid for create : No
Valid for retrieve : Yes
Valid for update : No

After googled it around, I found a something interesting…


In CRM 4.0, the CRM development team has silently introduced a new feature – overriddencreatedon field. While creating a record, we can set the attribute overriddencreatedon field. The value of this field will be used to set the record's created on attribute.

account agent = new account();

agent.new_agentname = "pq";

agent.overriddencreatedon = CreateCrmDateTime(new DateTime(2004, 10, 19));

services.Create(agent);

A small feature makes big differences.

CRM 4.0 Get the FetchXML queries in Advanced Find

You can just open the Advanced find page and build the query as you like. Then run the query to see if it returns the data as you wish it should. Press F11 to get the address bar and enter this script and press enter.

javascript:alert(resultRender.FetchXml.value);

or

javascript:prompt("my query:", resultRender.FetchXml.value);

Reindex an entire database

The following query is to reindex an entire database.

EXEC [sp_MSforeachtable] @command1="DBCC DBREINDEX('?')"

If you want to receive progress messages while it is running, try this version

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1)

WITH NOWAIT DBCC DBREINDEX('?')"

Click here for reference.

Display Report in IFrame

Recently I read a blog on displaying SSRS report in IFrame of CRM entities. Now, I'm going to share with the workaround..

For this example, we created a custom report that takes in the Account Id as a parameter and displays the report.

1. To add report in report manager, go to report manager URL and upload the report file .*rdl.

2. After that, to find the report URL, go to report server and right click on report's properties to copy the URL.

3. Add a IFrame in Account entity and URL of the IFrame, enter ‘about:blank’.
4. We will now add logic in the form OnLoad event that will create the report URL and append the Account Id for the report to use to display the report.
5. Add the following codes in OnLoad.
   

   var guid = crmForm.ObjectId;
   
   var url = 'http://tbe/ReportServer?%2fTesting_AddReportIntoIFrame&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:Parameters=false&rc:Parameters=false&AccountId=' + guid;
   
   document.getElementById('IFRAME_Testing').src = url;
   
   

6. Now save and close the form and publish the entity.
7. Navigate to an account and you should see the new tab and report render in the IFrame.

Notes:

• Since we want the report in an IFrame, we do not want the report viewer toolbar and parameters to show. Hence the ‘rc:Toolbar=false&rc:Parameters=false”. Since we want the report to render in HTML 4.0, we can specify the format to render the report in by specifying ‘rs:Format=HTML4.0’. 

Google Maps in CRM

One way to customize the Microsoft dynamics CRM 4.0 is to use IFrames from within the customization module in the application. This flexible way of extending the functionality of Dynamics CRM 4.0 open endless stream of enhancements that you can add to your CRM application passing dynamic values from /to CRM as parameters to control the IFrame application behaviour.

Blocks of JavaScript code below show how the Geocoding magic is called:

<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no"/>
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<title>Google Maps JavaScript API v3 Example: Geocoding Simple</title>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript">
    var geocoder;
    var map;
    function initialize() {
        geocoder = new google.maps.Geocoder();
        var latlng = new google.maps.LatLng(-34.397, 150.644);
        var myOptions = {
            zoom: 15,
            center: latlng,
            mapTypeId: google.maps.MapTypeId.ROADMAP
        }
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
    }

    function setGoogleMapDomain(myDiv, domain) {
        var frames = myDiv.getElementsByTagName('iframe');
        if (frames.length > 0) {
            window.frames[frames[0].id].document.domain = domain;
        }
    }

    function codeAddress() {
        var xxx;
        if (document.URL.indexOf('?') == -1)
            xxx = "kuala lumpur, malaysia";
        else
            xxx = document.URL.substring(document.URL.indexOf('?') + 9, document.URL.length);
        xxx = xxx.replace(/%20/gi, ' ');
//        alert(xxx);
        var address = xxx;
        var image = 'new.gif';
        if (geocoder) {
            geocoder.geocode({ 'address': address }, function(results, status) {
                if (status == google.maps.GeocoderStatus.OK) {
                    map.setCenter(results[0].geometry.location);
                    var marker = new google.maps.Marker({
                        map: map,
                        position: results[0].geometry.location,
                        icon: image
                    });
                } else {
                    alert("Geocode was not successful for the following reason: " + status);
                }
            });
        }
    }
</script>
</head>
<body style="margin:0px; padding:0px;" onload="initialize(); codeAddress();">
<div id="map_canvas" style="width:600px; height:400px;"></div>
</body>
</html> 

After that, just right click your .aspx file and view in browser.

Implement into CRM

1. Create a IFrame in CRM page.
2. Set the appropriate properties.

You should see Google Maps in CRM like this:

For more information in using Google Maps API, click this.

Enabling the Default Internet Explorer Context Menu (right click)

Sometimes during development (debugging and troubleshooting) of MSCRM applications and customizations, you might want to use internet explorer features like view source and open in new windows. By default, MSCRM disables all internet explorer context menus. If you right click at any MSCRM form, no menu comes unlike other web applications.
In order to enable the context menus, you need to change the Global.js file.

1. On the Microsoft CRM Web server, navigate to \_common\scripts (typically C:\Inetpub\wwwroot\_common\scripts).
2. Open and Edit the Global.js file.
3. Use your text editor’s Find feature to locate the document.oncontextmenu() function.
4. Comment out the existing code in this function by adding /* and */ as shown in the following code. You can undo this change later by simply removing event.returnValue = true; line and the comment characters.

function document.oncontextmenu()
{
 event.returnValue = true;

 /*
 var s = event.srcElement.tagName;
 (!event.srcElement.disabled &&
 (document.selection.createRange().text.length > 0 ||
    s == “TEXTAREA” ||
 s == “INPUT” && event.srcElement.type == “text”));
 */
}  

Notes :

1. Backup your Global.js file so that you can restore your changes later on.
2. Use this technique at development and not at production environment as this unsupported change might cause unpredictable behavior. Microsoft CRM prevents use of the right-click context menu for the user’s benefit and also to maintain a predictable navigation structure in the application interface.
   

Calling CRM Web Service from SQL Server

Past few days ago, someone asked me "Can we call Web Service from SQL Server?"

So, I simply google "call web service from sql server". wOW! It return me many kinds of samples/articles.

I referred one of the samples + doing some research....

ITS WORKS!!

Creating the CRM Web Service

Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2005, create a new Database Project:

So first, we generate the proxy file for our webservice with the wsdl.exe tool in C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. We have to convince this tool however, to generate code using the ‘old’ asynchronous way (without the Async methods). Only proxy classes generated that way can be loaded with external access privileges. If we would use the ‘new’ proxy classes, we have to use unsafe as security level, which can better be avoided. We can do this by using a configuration file. First create a configuration file containing the following xml:

1:  <wsdlParameters xmlns='http://microsoft.com/webReference/'>

2:    <language>c#</language>

3:    <protocol>Soap</protocol>

4:    <nologo>true</nologo>

5:    <sharetypes>false</sharetypes>

6:    <webReferenceOptions>

7:      <codeGenerationOptions>properties oldAsync</codeGenerationOptions>

8:    </webReferenceOptions>

9:  </wsdlParameters>

Save this file as c:\oldwsdlconfig.xml (change the language element to VB if you are creating a VB.NET project: vb). Now, call wsdl.exe to generate the proxy class:

This will generate a CrmService.cs  file. We will now add this file to our SQL Server project in Visual Studio by right clicking our project, selecting Add -> Existing items, and then browse to the location where the CrmService.cs  file was created. We will see an error if we build our code this way: the generated proxy class uses the webservice dll, which is not yet referenced, so right click the project in the Solution Explorer, choose add reference, and select System.Web.Services from the list.

Next, we have to avoid that our code will do runtime assembly creation for the serialization and deserialization of the objects which travel across this webservice. In order to do that, we right click in our Solution Explorer on the project and select Properties.

Select the Build tab, and at the bottom select for Generate serialization assembly the value On

While we are in the project property window, we can set the permission level property as well: select the database tab, and set the value for permission level to external. In this way, we mark this assembly as an assembly which is ‘safe’ in the sence that it will not do dangerous thread manipulations, platform invokes, …, and hence it is not marked as unsafe. But it is less safe than an assembly marked ‘safe’, because it makes calls over the network, or reads/writes to the hard disk.

1:  CrmService services = new CrmService();

2:  services = ConnectCrmServiceWithoutImpersonation(services, "username", "password", "domain", "http://servername/mscrmservices/2007/CrmService.asmx", "organization");      

3:  account agent = new account();

4:  agent.name = "Test 123";

5:  agent.new_agentname = "Agent Name";

6:   

7:  services.Create(agent);

1:      public static CrmService ConnectCrmServiceWithoutImpersonation(CrmService oService, String NETWORKUSERNAME, String NETWORKPASSWORD, String NETWORKDOMAIN, String crmServiceUrl, String tokenOrganizationName)

2:      {

3:          try

4:          {

5:   

6:              oService.Credentials = new System.Net.NetworkCredential(NETWORKUSERNAME, NETWORKPASSWORD, NETWORKDOMAIN);

7:              oService.Url = crmServiceUrl;

8:              CrmAuthenticationToken token = new CrmAuthenticationToken();

9:              token.OrganizationName = tokenOrganizationName;

10:              oService.CrmAuthenticationTokenValue = token;

11:              oService.PreAuthenticate = true;

12:   

13:   

14:          }

15:          catch (Exception ex)

16:          {

17:              //TODO: Enterprise Library

18:              throw ex;

19:          }

20:          return oService;

21:      }

Next, we can build and deploy this project.

Building the assembly

Before we build our assembly there are a couple of project settings that need modification.
Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence external.

Server and Database Settings

Now that we’ve covered the actual development of the stored procedure we would like to install it on our database server.  This requires us to modify some settings on the server and database.  If you are not the DBA of the server and you are in the luxurious situation that such a person exists, please verify with him/her if you are allowed to modify these settings.

Enabling CLR Integration

By default SQL Server does not allow CLR Integration.  This setting can be easily modified with the following script:

exec sp_configure 'clr enabled', '1';

reconfigure;

Our database we trust

As our stored procedure needs external access permission, we need to create the assembly with external access (as shown in next chapter).  To get this to work we need to convince SQL Server that our database can be trusted: (I’ve used AdventureWorks in this article but this can be any existing database)

ALTER DATABASE AdventureWorks

SET TRUSTWORTHY ON;

Installing the Stored Procedure

We’re almost there, all that remains to be done is to tell SQL Server where our stored procedure can be found and that it actually exists.  Assemblies, just like stored procedures, live in a database (AdventureWorks in our case).  In Management Studio you can see your assemblies in the Object explorer under the Programmability node of your database:

\

Adding the assemblies

Following script adds the two assemblies that we’ve created earlier to your database.  An assembly is given a name, like MyStoredProcedures.  Here you can finally see the EXTERNAL_ACCESS permission that we need for our web service call.

CREATE ASSEMBLY MyStoredProcedures

FROM 'D:\MyStoredProcedures.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

CREATE ASSEMBLY MyXmlSerializers

FROM 'D:\MyStoredProcedures.XmlSerializers.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

Adding our stored procedure

Last step is to create our stored procedure.  We tell SQL Server that it can be found in the MyStoredProcedures assembly, in the class StoredProcedures and that the method is called WeatherSP.  We have one parameter of type string in our method definition, which translates to nvarchar in the stored procedure.  A length of 10 is more than enough for our ZIP code.

CREATE PROCEDURE WeatherSP

AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

GO

Execute the stored procedure to run the web service

EXEC [dbo].WeatherSP

Drawbacks

Some drawbacks of calling CRM Web Service from SQL Server:

1. To get updated CrmService web service in code file .cs/.vb, we must recompile/regenerate the CrmService web service .asmx whenever changes done at CRM server, such as adding entities/fields.
2. Increase of resources usage(calling stored procedure -> crm service). It may slow down the system performance.
   

SQL Server Trigger to Send E-mail

First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.

   1:  --// First, enable SQL SMail

   2:  use master

   3:  go

   4:  sp_configure 'show advanced options',1

   5:  go

   6:  reconfigure with override

   7:  go

   8:  sp_configure 'Database Mail XPs',1

   9:  go

  10:  reconfigure

  11:  go

  12:   

  13:  --//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment

  14:  EXECUTE msdb.dbo.sysmail_add_account_sp

  15:  @account_name = 'DBMailAccount',

  16:  @email_address = 'sqlserver@domain.com',

  17:  @display_name = 'SQL Server Mailer',

  18:  @mailserver_name = 'exchangeServer'

  19:   

  20:  EXECUTE msdb.dbo.sysmail_add_profile_sp

  21:  @profile_name = 'DBMailProfile'

  22:   

  23:  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

  24:  @profile_name = 'DBMailProfile',

  25:  @account_name = 'DBMailAccount',

  26:  @sequence_number = 1 ;

Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:

   1:  CREATE TABLE dbo.inventory (

   2:  item varchar(50),

   3:  price money

   4:  )

   5:  GO

Now that SQL mail and the table are setup, we will create a trigger that does the following:

1. Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
2. Checks for items being entered that have a price of $1000 or more
3. If there is a match, an email is sent using the SQL Mail profile we used above.

   1:  CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS

   2:   

   3:  DECLARE @price money

   4:  DECLARE @item varchar(50)

   5:   

   6:  SET @price  = (SELECT price FROM inserted)

   7:  SET @item = (SELECT item FROM inserted)

   8:   

   9:  IF @price >= 1000

  10:  BEGIN

  11:  DECLARE @msg varchar(500)

  12:  SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'

  13:  --// CHANGE THE VALUE FOR @recipients

  14:  EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'

  15:  END

  16:  GO

The only way to test a trigger is to add actual data, so let's do that here:

insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)

Your email should arrive very quickly.
If it doesn't, check the SQL Server mail log in SQL Management Studio by running

SELECT 'sysmail_sentitems' as TableName, * FROM msdb.dbo.sysmail_sentitems

SELECT 'sysmail_faileditems' as TableName, * FROM msdb.dbo.sysmail_faileditems

SELECT 'sysmail_allitems' as TableName, * FROM msdb.dbo.sysmail_allitems

SELECT 'sysmail_unsentitems' as TableName, * FROM msdb.dbo.sysmail_unsentitems

SELECT 'sysmail_mailattachments' as TableName, * FROM msdb.dbo.sysmail_mailattachments

Notes:

1. We can find the triggered values by selecting from trigger table. Eg. (SELECT price FROM inserted)
2. Trigger only applicable for INSERTED and DELETED.
3. For UPDATED, use this

   1:  create TRIGGER expensiveInventoryMailer ON dbo.a_inventory after UPDATE AS

   2:   

   3:  if (update (price) or update (item))

   4:  BEGIN

   5:  --Your works here

   6:  END

Default multi column sorting

Being able to sort by multiple columns in CRM is quite handy, but unfortunately there are no mechanisms for configuring a default multi-column sort. Until now. The following code will illustrate how to accomplish it:

For grid in main form(public view/associated view in main form)

var stageFrame = document.getElementsByName("stage")[0]; 
var stageFrameDoc = stageFrame.contentWindow.document; 

crmGrid = stageFrameDoc.all.crmGrid; 
crmGrid.all.divGridProps.children["sortColumns"].value = "fieldname:0;fieldname:1"; 
crmGrid.Refresh();

For grid in left nav(public view/associated view)

GetIframe("new_incident_new_entityname");

function GetIframe(loadAreaId) {
    var isNativeEntity = false;
    var navElement = document.getElementById('nav_' + loadAreaId);
    if (navElement == null) {
        navElement = document.getElementById('nav' + loadAreaId);
        isNativeEntity = true;
    }

    if (navElement != null) {
        navElement.onclick = function LoadAreaOverride() {
            // Call the original CRM method to launch the navigation link and create area iFrame 
            if (isNativeEntity) {
                loadArea('area' + loadAreaId);
                Iframe = document.getElementById('area' + loadAreaId + 'Frame');
                //put code to get gridobject here (grid from main form)
            }
            else {
                loadArea(loadAreaId);
                Iframe = document.getElementById(loadAreaId + 'Frame');
                //put code to get gridobject here (grid from left nav)

                function IframeReady() {
                    if (Iframe.readyState != 'complete') {
                        return;
                    }
                    var stageFrameDoc = Iframe.contentWindow.document;
                    crmGrid = stageFrameDoc.all.crmGrid;
                    crmGrid.all.divGridProps.children["sortColumns"].value = "fieldname>:0;fieldname>:0";
                    crmGrid.Refresh();
                }

                Iframe.onreadystatechange = IframeReady;
            }
        }
    }
}

Notes:

1. Change the value for the "sortColumns" property to ":[; :]" where: is the name of the sorted column is 1 (descending) or 0 (ascending).
2. Repeat the bracketed block as necessary, being sure to place a semicolon (;) between each field/sort pair.

There must be NO SPACES. Finally, call a Refresh() on the grid. The downside to this code, is that it does not update the sorting icon on the columns. There's a call being made somewhere, and I think it has to do with analyzing the keypress+click event, that updates the image... whereas my code does not follow that code path.

Get CRM Webservice Soap Exception in detail

This sample to helps to give you a bit more information when you hit a Soap Error when connecting to the CRM Web services.

I think its a great way to bubble up that bit more information about what caused the error to the developer (and any error logging you may be doing as well) & help you work out what has happened, so thought I would share it:

   1:  try

   2:  {

   3:   

   4:      //do something here that tries to use the CRM Webservice

   5:   

   6:  }

   7:   

   8:  catch (SoapException ex)

   9:  {

  10:   

  11:      string errorMessage = ex.Message;

  12:   

  13:      if (ex.Detail != null)

  14:      {

  15:   

  16:          // If there is a InnerText message then we will overwrite with this instead. It can be

  17:   

  18:          // more helpful to explain the error.

  19:   

  20:          errorMessage = ((System.Xml.XmlElement)ex.Detail).InnerText;

  21:   

  22:      }

  23:   

  24:      throw new Exception("Something went wrong when communication with the Web Service. Error: " + errorMessage);

  25:   

  26:  }

1st Day of blogging

wow.. it's my 1st day blogging here..
Googled around to find out how to insert code snippet into the blog..
Here's the workaround:

1.In the blogger,Click on Layout tab ->Edit HTML and put following things Before </head>

<link href='http://syntaxhighlighter.googlecode.com/svn/trunk/Styles/SyntaxHighlighter.css' rel='stylesheet' type='text/css'/>
<script language='javascript' src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shCore.js'/>
<script language='javascript' src='http://syntaxhighlighter.googlecode.com/svn/trunk/Scripts/shBrushCpp.js'/>

2. put following things Before </body>

<script language="javascript">
dp.SyntaxHighlighter.BloggerMode();
dp.SyntaxHighlighter.HighlightAll('code');
</script>


3. encode (HTML Encode) your source code. for this, you can use this.

OR

Copy your code in notepad and replace all < in &lt; etc.

4. Put your updated code between:
<pre name="code" class="Cpp">
//Put your code here
</pre>

Result as below:

function Abc() {
  
}