Notification.asmx

While investigating a stability issue on a customer's CRM 3.0 Server recently, I figured I needed to identify what the calls to notification.asmx were. These calls are made every 30 seconds, and can be identified in the IIS logs as follows:


2008-09-08 11:11:44 192.168.2.107 POST /MSCRMServices/notification.asmx


Each call results in a SQL query to the MSCRM database, like the following:


exec sp_executesql N'Select EventId, EventData, CreatedOn From Notification Where CreatedOn > @CreatedOn', mailto:N datetime', @CreatedOn = 'Aug 14 2008 3:08:02:370PM'


A reasonably detailed check on the internet suggested nobody else knew what notification.asmx was doing, or if they did, they weren't telling.


I won't bore you with the details of how I found it, other than my past experience with CRM 1.2 helped, but in case you're interested, it's used for processing CRM 1.2-style callouts. Unfortunately this had noting to do with the ultimate problem, but at least I now know to eliminate it from my enquiries.

Redeploying callouts by recycling the application pool, rather than IISRESET

When redeploying a callout on CRM 3.0, it is considered necessary to run IISRESET to unload the callout assembly from memory, and hence allow you to copy a new version of the dll. This can have several issues:

1. After an IISRESET, it takes long enough to reload / rerender CRM data to be annoying when frequently testing and redeploying versions of a callout assembly
2. If other users are using CRM, there will be an outage during the IISRESET
3. IISRESET affects all other web applications on the server

To minimise the impact of 1) above, Michael Höhne's local debugging mechanism is an excellent solution in a lot of environments.

However, there are still the occasions when the callout has to be redeployed on to the CRM server. There's not much that can be about 2), although the following suggestion helps a bit.

We can do something about 3). A callout is loaded into the process for the CRM application pool (CrmAppPool), and it is only this application pool that needs to be recycled, rather than the whole of IIS. An application pool can be recycled via the IIS management console, but there is an easy way to script this, as follows:

1. Open a command prompt
2. Change directory to %Windir%\system32
3. Run the following command:

cscript iisapp.vbs /a CrmAppPool /r

This will just recycle the application pool, after which you can copy the new callout assembly

Syntax issues using SQL Execute As and Revert statements

SQL Server 2005 provides a means to switch user context via the EXECUTE AS statement. This can be very useful when querying CRM Filtered views, as they use the SQL user context to determine what data to return and if queried using the CRM service account they return no data. An example of how this can be used in CRM callouts can be found here. I've also used a similar approach when running SSIS packages programmatically from a web application, as ASP .Net impersonation doesn't work properly in SSIS packages.


However, when testing this further, and checking the behaviour of REVERT, I found this only works correctly if you separate your SQL statements with semi-colons. Take the following 2 examples:


Execute as user='crmdom\admin'
select * from filteredaccount
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql)
revert


Of these 2, the former appears to work, but the REVERT statement doesn't get processed, and the latter gives an 'incorrect syntax' error. But, if you add a semi-colon after the statement before REVERT, then they work fine:


Execute as user='crmdom\admin'
select * from filteredaccount;
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql);
revert


Now, that is not what I expected in SQL syntax. I always thought semi-colons were an optional statement separator, but apparently not -though I expect this is a bug rather than by design.


Note, this behaviour happens on build 2047 of SQL 2005, whereas it works without semi-colons on build 1399, but that doesn't stop me using them as a matter of course with impersonation.