Sarbashish's Code Book

Tips, tricks, and other stuffs on Microsoft Dynamics CRM, Microsoft SQL Server, MySQL, DotNet, Java, JavaScript

  • Categories

  • Archives

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1 other subscriber
  • Meta

  • Author


    Sarbashish Bhattacharjee
    DOB: 23rd July 1972
    LOC: Bangalore, India

Export to Excel – How to prevent Long numbers from scientific Notation

Posted by sarbashish on November 30, 2012

If you have a varchar field in your database which can accommodate more than 12 characters and one of your row has a data which is a pure number for example 12312312312349875 and you export the data in excel and you will see the data displaying as 1.23123E+16.

If you are entering data in excel then you will get around by putting a single quote in the front to change the formatting. See screenshot below:

 

 

 

 

 

 

 

 

 

 

Now to fix the issue in the export to excel routine of your program you need to do cell formatting so that excel does not represent the long number using scientific notation.

Sample Code

<%
Response.Clear
Response.CacheControl = “no-cache”
Response.AddHeader “Pragma”, “no-cache”
Response.Expires = -1
Response.ContentType = “application/vnd.ms-excel”
Dim FileName
FileName = “TestDB Lookup-” & month(now)&”-“&day(now)&”-“&year(now)&”.xls”
Response.AddHeader “Content-Disposition”, “inline;filename=” & FileName
%>
<html xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns=”http://www.w3.org/TR/REC-html40&#8243;;>
<head>
<meta http-equiv=Content-Type content=”text/html; charset=UTF-8″>
<!–[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheet>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
<![endif]–>
</head>
<body>
<table border=”0″>
<tr>
<td>ID</td>
<td>Name</td>
</tr>

<tr>
<td style=”mso-number-format:\@;”>01234567890123456567678788989909000030</td>
<td>Sarbashish B</td>
</tr>
</table>
</body>
</html>

Posted in Classic ASP | Tagged: , , | Leave a Comment »

How to wrap 32 bit dll for 64 bit operating system to work with regsvr32.exe?

Posted by sarbashish on March 15, 2012

If you want to register the 32-bit COM DLL created using VC++ or Visual Basic 6.0 on Windows 2003 64-bit Operating System then you can do it easily with few tweaks. It is slightly tricky and for that you have to follow the steps below without making any changes in the code. It also does not require any compilation to be done and also you do not have to run the IIS in WOW Mode. I have faced this issue few years back and I resolved this issue and it works fine for me.

Scenario:

Let me assume that you have a 3rd party 32-bit COM DLL provided by a vendor. The DLL works fine on 32bit Operating system and the moment you move to a x64 environment it does not work even though you have tried to register it through regsv32.

Also let me assume that the name of the DLL is “ASXUpload.DLL”. I will use this name in the solution so that it becomes a little easy to follow the instructions.

Solution

Please follow the steps below:

1. First of all if you have already registered the DLL in x64 Operating System the unregister the DLL. To do that just type the following in the run command “regsvr32 /u <filename with the path>” something like “regsvr32 /u C:\MyDLL\ASXUpload.DLL”. If you have already unregistered the DLL from the x64 OS then no need to run this step.

2. Also make sure that you have not kept your DLL inside the Windows folder which is normally C:\Windows. For this example I have kept the DLL in the following folder C:\MyDLL.

3. Now we need to add the COM+ Components using Component Services of Microsoft. To start Component Services, go to Control Panel / Administrative Tools/ Component Services. Once inside component Services, drill down into Computers, then My Computer, then COM+ Applications. Then Right-click on COM+ Applications and choose “New” -> “Application”.

4. At “Welcome to the COM Application Install Wizard” screen, click “Next >”.

5. Click on “Create an Empty Application” button.

6. Enter the name. Since my DLL name is ASXUpload.dll so I have typed in the name as“ASXUpload”. When asked “Library or Server”, select“Server”.

7. Click “Next >” button and then choose“This User”.

8. Enter the User or click Browse to select the user. Clicking Browse is safer, to ensure that the correct domain and spelling are used. Enter the password and confirm the password. Warning, be sure to include the domain/username if required. Click on“Finish”. (Note: We recommend “This User”, otherwise, someone must be logged onto the server in order for DLL to run.). In my case I have chosen the domain administrator account. You can also add a Service Account. If you are not sure please consult with your system administrator.

9. Now“Add Application Roles” screen will appear. Do not add anything just click on the“Next >” button.

10. Now“Add Users to Role” screen appear. Do not add anything just click on the“Next >” button.

11. Now you will see that under Component Services -> Computers -> My Computer -> COM+ Application -> you will see the newly added application. In this example the application name would be“ASXUpload”. Now drill down the newly added application“ASXUpload” by clicking the “+” icon and you will see“Components”.

12. Now right-click on “Components” and then choose “New Component”. At “Welcome to the COM Application Install Wizard” screen, click“Next >”.

13. Click on“Install new component(s)” and now select the DLL which you want to register. In the case it would be“C:\MyDLL\ASXUpload.DLL”.

14. Once you select the DLL you will see that it will show you the components found. Click on the“Next >” button to proceed and finally hit the“Finish” button to complete.

15. Now is the tricky part. Right click on the application you have added which you will find under Component Services -> Computers -> My Computer -> COM+ Application. In my case the application name is “ASXUpload”. After you right click on the Application select“Properties”. The application properties window will open up. Click on the“Security” tab. In the Security tab make sure that under“Authorization” section the checkbox “Enforce access checks for this application” is unchecked.
Under “Security Level” section select the radio button“Perform access checks only at the process level. Security property will not be included on the object context. COM+ security call context will not be available.”
Make sure that option “Apply restriction policy” is unchecked.
Set “Impersonate Level” to “Anonymous”

16. If you want to access the DLL from web Application then make sure that you add the IUSR and IWAM account. To do this go to COM+ Application -> Application Name (In this case it will be ASXUpload) -> Roles -> CreateOwner -> Users. Right click on the Users and add the IUSR and IWAM account used by Internet Information Server.

17. Also set the NTFS permission on the folder where you kept the DLL. In this case I have kept the DLL inside the folder C:\MyDLL. Now right click on the folder “MyDLL” and go to the security tab and then add the IUSR and IWAM account.

This is all you need to do and you should be able to consume the DLL.
I have used this technique twice in two different organizations I have worked in the past on the Production Environment and it works without any issues. First I tried this in the year 2005 and then I used it again in 2008.

Let me know if you are facing any issues.

Cheers!!!!!

Posted in COM DLL's | Leave a Comment »

How to resolve “Unable to initialize communication with the Passport server. (Error Code: 80048883)”?

Posted by sarbashish on September 11, 2010

This error message is coming up because the default app pool user does not have permission to initiate the msidcrl40.dll file which called by the IdCrlWrapper.dll

This is an issue which almost all Microsoft CRM Technical consultants will face while trying to communicate to the Microsoft CRM Server from custom developed code in ASP.Net using Microsoft CRM SDK with passport authentication. The situation will arise when you try to move the code from the Development Box to the Production Server.

To resolve this issue you have to follow the steps below:

Solution

Step 1
Make a change in the web.config file. Add the following line

 <identity impersonate=”true” userName=”[LOCAL ADMINISTRATOR USERNAME]” password=”[ADMIN PASSWORD]” />

You can also use a service account like mscrmsrv. In that case your web.config file entry will be as follows:

mscrmsrv” password=”[mscrmsrv PASSWORD]” />

If you are not sure where to add the above line then in that case you can open the IIS Manager. Right click on the website (the one which you are using for your MSCRM Custom App). Click on Properties. It will open the properties window. Click on the ASP.NET tab. Now click on the Edit Configuration button. It will open the ASP.NET Configuration Settings window. Now from this window click on the Application tab. In the application tab under Identity Settings click on Local impersonation. Now put the username and the password. Once you save it will post the entry in the web.config file.

Step 2

Now which ever account you are using for the local impersonation make sure that you now login to the server with the same account and then restart the IIS using IISRESET command. Once done try to run the application. It will not throw the error “Unable to initialize communication with the Passport server”.

 If you reset the IIS from a different logged in user then this problem will showup once again.

You can also find my posts in Microsoft forums.
http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/1ecd316b-34f2-4747-a56d-b0310eb4471a

If you think the above solution has worked for you then you can vote me in :).

Cheers!!!

Posted in Microsoft CRM | Tagged: , | Leave a Comment »

How to create Recurring Service Activities in MS CRM 4.0?

Posted by sarbashish on September 9, 2010

Recurring Service Activities are not supported in Microsoft Dynamics CRM. Most of the clients looks for this feature as this is something which is not available out of the box. There are workarounds with the help of which you can make it happen. In this post I will demonstrate it to you how easily you can achieve it.

Solution
We need to add 2 new fields in the Service Activity entity.
 

Please read the detailed implementation below:

Entity Name: serviceappointment
Add two new Fields

New Field 1

Field Label: Recurring Frequency
Field Name: new_recurringactivity
Requirement Level: No Constraint
Field Type: picklist
Field Values:
1 – Daily
2 – Weekly
3 – Fortnightly
4 – Monthly
5 – Bi-Monthly
6 – Quarterly
7 – Half Yearly
8 – Yearly

New Field 2

Field Label: Recurring End Date
Field Name: new_recurringenddate
Field Type: datetime
Field Format: Date Only
Requirement Level: No Constraint

Purpose of the new fields

Field Name: new_recurringactivity
Purpose: This field will accept recurring frequency from the user.

Field Name: new_recurringenddate
Purpose: This field will accept the date from the user. It will not create any activity beyond the date. Otherwise it will go in a endless loop.

After adding the two new fields the Service Activity form should look like this

CODE SNIPPET 1

// Add this code snippet to the OnLoad event of the Service Activity Form

// Lock the recurring Service Activiy fields once activities have been created
if (crmForm.all.new_recurringactivity.DataValue)
{
crmForm.all.new_recurringactivity.disabled = true;
crmForm.all.new_recurringenddate.disabled = true;
}// Code Ends

CODE SNIPPET 2

// Add this code snippet to the OnSave event of the Service Activity
// Function to format a date to the UTC format required by web services
function DateToUTCFormat(inputDate)
{
var date = inputDate.getDate();
var month = inputDate.getMonth()+1;
var year = inputDate.getYear();
var hours = inputDate.getHours();
var minutes = inputDate.getMinutes();
var ampm = ” AM”;
if (hours > 11)
{
ampm = ” PM”;
hours = hours – 12;
}
if (hours == 0)
{hours = 12;}
if (minutes < 10)
{var time = hours.toString() + “:0” + minutes.toString() + “:00” + ampm;}
else
{var time = hours.toString() + “:” + minutes.toString() + “:00” + ampm;}
var UTCDate = month.toString() + “/” + date.toString() + “/” + year.toString() + ” ” + time;
return UTCDate;
}
if (crmForm.all.new_recurringactivity.disabled == false && crmForm.all.new_recurringactivity.DataValue && crmForm.all.new_recurringactivity.DataValue)
{
var interval = 0;
switch (parseInt(crmForm.all.new_recurringactivity.DataValue))
{
case 1: // Daily (Please check the value you are getting from the CRM Form)
interval = 1;
break;

case 2: // weekly
interval = 7;
break;

case 3: // Fortnightly
interval = 14;
break;

case 4: // Monthly
interval = 30;
break;

case 5: // Bi-Monthly
interval = 60;
break;

case 6: // Quarterly
interval = 90;
break;

case 7: // Half Yearly
interval = 180;
break;

case 8: // Yearly
interval = 365;
break;
}

var recurringEnd = crmForm.all.new_recurringenddate.DataValue;
recurringEnd.setDate(recurringEnd.getDate()+1);
var activityStart = crmForm.all.scheduledstart.DataValue;
var activityEnd = crmForm.all.scheduledend.DataValue;

// Set the first reccuring appointment as per the recurring frequency opted by the user
activityStart.setDate(activityStart.getDate()+interval);
activityEnd.setDate(activityEnd.getDate()+interval);

// Prepare values for the new Service Activity
var subject = crmForm.all.subject.DataValue;
var regardingId = crmForm.all.regardingobjectid.DataValue[0].id;
var customerId = crmForm.all.customers.DataValue[0].id;
var serviceid = crmForm.all.serviceid.DataValue[0].id;
var resourceId = crmForm.all.resources.DataValue[0].id;
var ownerId = crmForm.all.ownerid.DataValue[0].id;
var new_recurringactivity = crmForm.all.new_recurringactivity.DataValue;
// Loop for the number of recurring Service Activities
while (activityStart < recurringEnd)
{
// Prepare the SOAP message.
var startUTC = DateToUTCFormat(activityStart);
var endUTC = DateToUTCFormat(activityEnd);
var recurringEndUTC = DateToUTCFormat(recurringEnd);
var authenticationHeader = GenerateAuthenticationHeader();
var xml = “” +
“xmlns:soap=’http://schemas.xmlsoap.org/soap/envelope/'”+
” xmlns:xsi=’http://www.w3.org/2001/XMLSchema-instance'”+
” xmlns:xsd=’http://www.w3.org/2001/XMLSchema’>”+
authenticationHeader+
“<soap:Body>”+
“xmlns=’http://schemas.microsoft.com/crm/2007/WebServices’>”+
“xsi:type=’serviceappointment’>”+
“<subject>”+subject+”</subject>”+
“<serviceid>”+serviceid+”</serviceid>”+
“”+ownerId+””+
“<customers>”+
“<activityparty>”+
“<partyobjecttypecode>account</partyobjecttypecode>”+
“”+customerId+””+
“<participationtypemask>11</participationtypemask>”+
“</activityparty>”+
“</customers>”+
“<resources>”+
“<activityparty>”+
“<partyobjecttypecode>systemuser</partyobjecttypecode>”+
“”+resourceId+””+
“<participationtypemask>1</participationtypemask>”+
“</activityparty>”+
“</resources>”+
“”+startUTC+””+
“”+endUTC+””+
“”+recurringEndUTC+””+
“<new_recurringactivity>”+new_recurringactivity+”</new_recurringactivity>”+
“</entity>”+
“</Create>”+
“</soap:Body>”+
“</soap:Envelope>”;
// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject(“Msxml2.XMLHTTP”);
xHReq.Open(“POST”, “/mscrmservices/2007/CrmService.asmx”, false);
xHReq.setRequestHeader(“SOAPAction”,”http://schemas.microsoft.com/crm/2007/WebServices/Create“);
xHReq.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
xHReq.setRequestHeader(“Content-Length”, xml.length);
xHReq.send(xml);
// Capture the result
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes(‘//error’).length;
if (errorCount != 0)
{
var msg = resultXml.selectSingleNode(‘//description’).nodeTypedValue;
alert(msg);
}
// Notify user of Service Activity creation
else
{
// alert(“Service Activity created on ” + activityStart);
}
// Increment the next Service Actvity to be created by as per the recurring frequency opted by the user
activityStart.setDate(activityStart.getDate()+interval);
activityEnd.setDate(activityEnd.getDate()+interval);
}

} // Code block Ends

Some More Explanation

Please note in the above code snippet I am taking resources as user. So for that I have used in the code snippet

“<resources>”+
“<activityparty>”+
“<partyobjecttypecode>systemuser</partyobjecttypecode>”+
“”+resourceId+””+
“<participationtypemask>1</participationtypemask>”+
“</activityparty>”+
“</resources>”+

If you are considering equipments as your resources then use

“<partyobjecttypecode>systemuser</partyobjecttypecode>”+

Also note that if you want to accept multiple resources then you need to change the value in the “participationtypemask” node.

Since I am expecting 1 resource so I have kept the value as 1.

You can also find my posts in Microsoft forums.
http://social.microsoft.com/forums/en-US/crmdevelopment/thread/3f020e02-cd86-44b2-9cff-36e6cdafc8d8/
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.crm&tid=fa7512cc-41b9-47a7-bafc-5ea310c07ef8&cat=&lang=&cr=&sloc=&p=1

If you think the above solution has worked for you then you can vote me in 🙂

Cheers!!!

Posted in Microsoft CRM | Tagged: | 2 Comments »