Telnet commands to Send Email

I can never remember the commands to send an email via Telnet so here they are again mostly for my benefit. Examples of <smtp server> are
localhost
smtp.domain.com

C:\windows>telnet <smtp server> 25
Which should return
Connected to <smtp server>

HELO <smtp server>

MAIL FROM: admin@domain.com

RCPT TO: chuck@disney.com

DATA
Subject: Subject line of email
This text goes in the body of the email
<CR>.<CR>

Which should return
250 2.0.0 ???????? Message accepted for delivery
or
250 2.0.0 ???????? Message queued

IMPORTANT: The MAIL FROM email address should be a valid email address. Not only must the domain.com be the same domain the SMTP server is in, but don't assume that a made up email name will work. I've wasted hours trying to figure out why an email was not sent just because I didn't use a valid "mail from" address.

Should you not be able to connect to the SMTP server using the telnet command it may be because you have a firewall on the server that is blocking all outbound traffic on port 25. Or you've get the wrong SMTP server name.

Invalid handle error in Visual Source Safe

I was getting an "Invalid handle" error message the other day when trying to access Visual Source Safe and it drove me mad for a couple of days until I found a solution. So I thought I would blog it in case you run across the same problem.
I was at a customer site using my laptop which was not connected to the customer's domain. When I plug in the network cable I get issued an IP address from their DHCP server. I then fire up my Virtual Machine with my development environment on it including Visual Source Safe 2005. The virtual machine network connections are configured so that it too is issued an IP address from the customer's DHCP.
Using the logon credentials the customer has issued me, I can connect to servers and file shares on their domain.
Now the Visual Source Safe database was located on one of the customer's servers and I noticed that the network connection was unreliable. At times when I pinged the server I got "request timed out" or "host destination unreachable". The result was when I opened Visual Source Safe sometimes it would work just fine and other times it would give me the "Invalid handle" error. It got to a point where I was unable to connect to Source Safe for several days.
The solution as it turned out was quite simple. I mapped a drive letter to a network share on the VSS server. Note it's important to map a drive letter, just using "Run" to connect to the share was not enough. Now of course if you can't even ping the server then mapping the drive won't work either. But I found that once I was able to map the drive the "Invalid handle" error would go away - at least for the rest of the day. I still find that each day I need to disconnect the drive and then reconnect but it's a small price to pay to have access to VSS for the rest of the day.
So if you come across the same situation I hope this helps you to solve it.

Lynx Text Browser - Downloading Document Error

I was developing an Accessible web site the other day which included downloading of documents (either Word, Excel or PDF). It just contains an anchor tag for the document which will open the document in Acrobat Reader allowing the user to "Save As". It worked as designed in Internet Explorer and FireFox but gave an error when I tried it in the Lynx Text Browser. The error message was:

The exception Integer division by zero.
(0xc0000094) occurred in the application at location 0x00451c24.

There is not a lot of technical help around Lynx and Google didn't offer any meaningful results. But I found that I could download a text file in Lynx without problem so I figured it must be something to do with the inability to recognise the PDF file type.

After some more research I found an obscure attribute for the Anchor tag in HTML. This is the "type" attribute in which you can add the content type. So to the Anchor tag for the document I added

type="application/pdf"

and that did the trick. It appears that Lynx requires this extra hint of the file type to avoid the error shown above. I also added the type attribute for the other file types for good measure and it all works fine now.

Creating a SQL Server Reporting Services Report from a SharePoint List

Although I've found blogs that explain how you can create a SQL Server Reporting Services (SSRS) report from a SharePoint list I've found just as many that say it can't be done. The explanation for the two conflicting views is (IMHO) because there is an epectation that because SharePoint lists are stored in SQL Server that you can query them directly at the database level. That is certainly not the case. If you try accessing the database directly then you deserve all the bad things that will befall you!

The correct answer is yes, you can use SharePoint lists as a datasource but via the SharePoint web services. The SharePoint web services can be found in the _vti_bin directory of each SharePoint web application and the web service you want to interrogate to get at Lists is called lists.asmx. This blog on Code Project explains how to do it quite well.

So here is how you go about it. Start off in VS 2005 and create a new Reporting Services project. Add a Shared Datasource and make it of type XML, then add the url to the web service: e.g. http:///_vti_bin/lists.asmx. You can optionally add the full path to the site e.g. http:///sites//_vti_bin/lists.asmx because this still works although you won't find this path in IISAdmin.
Then create a new report based on this datasource and add the following for the Query string

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue>{DCED6771-E498-4BC5-B44D-BE71C5D7B6C8}</DefaultValue>
</Parameter>
<Parameter Name="viewName">
<DefaultValue>{C63CFF34-647C-4FFA-9E20-DA1B6737380A}</DefaultValue>
</Parameter>
<Parameter Name="rowLimit">
<DefaultValue>9999</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

Although only the listName parameter is mandatory you should always add viewName because otherwise the default view is used and this can be changed by a user and may mess up you report. Also be sure to add rowLimit because the default is 100 rows and you want to be sure you report on all the data.

Note that all the parameter names are case sensitive and you need to use the GUID rather than the name of the list and view. You might get some success with using the name but several other bloggers have reported this doesn't always work. You can find the GUID in SharePoint by simply editing the list or the view and you will see it in the URL of the Internet Explorer address bar. Just replace %2D with a hyphen and the %7B and %7D with curly braces {}.

Once you added this as your query string you should see a list of fields which you can add to your report. Note that SSRS doesn't recognise the data type of the fields and everything returned is treated as a string. Use the CInt and CDate functions (and the other converstion functions) to convert the data into the correct format - particularly if you want to include totals in your reports.

The 'lookup' and 'people and groups' fields are of the format 99#;text where 99 is the ID. To handle this goto Visual Studio, select the Report menu -> Report Properties and select the Code tab and then embed a function to strip off the stuff you don't need. This blog had a good example of the code you need.

Once you've built your report, then deploy it to Reporting Services in the same way but create a datasource in Report Manager using the same approach you did in Visual Studio (connection type: XML, connection string: http:///_vti_bin/lists.asmx). Set the credentials to be Windows Integrated and be sure that users running the report are able to access the SharePoint list.

One final point. If you were hoping to combine data from multiple lists within a site collection (where the list structure is identical) then you will have to write your own web service to do this and then call it from SSRS using the XML connection type. Performance won't be blistering if you have a lot of sites but you can get around this by scheduling the report to run at night.

SharePoint Datasheet View Error - "The selected cells are read-only"

The other day when editing a list in the datasheet view in MOSS 2007 I got "The selected cells are read-only" error. I found a few posts that indicated that not all of a lists data types are supported in datasheet view, but that wasn't my problem because I only had single line text, number and currency data types.

The problem was that I needed folders in my list and although I could edit the datasheet at the root level, I got the read-only error whenever I tried to edit the datasheet within a folder.

On one of my VPC images I had no problems and then I spotted the little Access icon in the top left hand corner of the datasheet. Comparing this to the VPC image where I was getting the error I noticed that the Access icon was missing. On this VPC Office 2007 was installed EXCEPT for Access. So I just installed Access and the error went away.

Please note that the datasheet view does require the Office Web Components to be installed on the CLIENTS for datasheet view to work at all. What I haven't checked yet is whether clients needs Access 2007 in order to edit cells when in a folder of a list. When I find out I'll update this post.

SharePoint EventHandler that creates a CRM 4.0 activity

In a previous post I referrred to linking CRM 4.0 to a SharePoint document library to display documents in an I-Frame. I also wanted to demonstrate how adding a document into a SharePoint library could create a CRM activity (in my case a completed one).

There seems to be very few CRM 4.0 examples on the web and the SDK is not much use. So I hope this makes your life easier although I confess I am blogging it so I can find this code again!

A few limitations of this approach: I've used a flat folder structure in SharePoint for each instance of the CRM entity I'm connecting to. I'm also using the incident id as the folder name because I'm using the service request entity. The GUID folder name is not something you could roll out in production.

I've created the activity as the same person (the administrator) and I've been lazy again and hard-coded the GUID. You'll need to change this line:
activity.ownerid.Value = new Guid("{5D76458D-D728-DE11-99E5-0003FF6875B2}");

You will also need to change the service.url and the credentials. Don't forget to add a web reference to the CRMService.asmx and call it CrmService.

I've put the event handler on the ItemAdding event which is not ideal as you can cancel from adding the document but the event will still be created.

This by the way is how I got the document path into the Letter entity so that I could use it for rendering the document in this post.

namespace CreateCRMActivity
{
public class CreateActivityEventHandler : SPItemEventReceiver
{
public override void ItemAdded(SPItemEventProperties properties)
{
SPWeb webOrig = null;
try
{
webOrig = properties.OpenWeb();
CreateActivity(properties);

}
catch (Exception ex)
{
// record error message
}
finally
{
webOrig.Close();
}
}
private void CreateActivity(SPItemEventProperties properties)
{
SPWeb webOrig = properties.OpenWeb();
string docpath = "";
string entityID = "";
string name = "";
try
{
docpath = properties.AfterUrl;
// docpath of the form http://servername/sites/sitename/Shared%20Documents/AD66458D-D728-DE11-99E5-0003FF6875C2/mydocument.pdf

int start = docpath.IndexOf("Documents/") + 10;
int end = docpath.LastIndexOf("/");
entityID = docpath.Substring(start, end - start);
// e.g. AD66458D-D728-DE11-99E5-0003FF6875C2

name = docpath.Substring(end + 1, docpath.Length - end - 5);
// remove the extension e.g. mydocument

// Set up the CRM Service.
CrmService.CrmAuthenticationToken token = new CrmService.CrmAuthenticationToken();
// You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
token.AuthenticationType = 0;
token.OrganizationName = "MyDemo";

CrmService.CrmService service = new CrmService.CrmService();
service.Url = "http://localhost:5555/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = new System.Net.NetworkCredential("Administrator", "Password1", "Integration"); //CredentialCache.DefaultCredentials;
letter activity = new letter();
// activitypointer activity = new activitypointer();
activity.subject = "Letter - " + name;
activity.regardingobjectid = new Lookup();
activity.regardingobjectid.type = EntityName.incident.ToString();
activity.regardingobjectid.Value = new Guid("{" + entityID + "}");

activity.uklg_linkeddocument = properties.WebUrl + "/" + docpath;

activity.ownerid = new Owner();
activity.ownerid.type = EntityName.systemuser.ToString();
activity.ownerid.Value = new Guid("{5D76458D-D728-DE11-99E5-0003FF6875B2}");
Guid activityguid = service.Create(activity);



// task is created now set it to completed
SetStateLetterRequest Lstate = new SetStateLetterRequest();
Lstate.EntityId = activityguid;
Lstate.LetterState = LetterState.Completed;
Lstate.LetterStatus = 4;

SetStateLetterResponse stateSet = (SetStateLetterResponse)service.Execute(Lstate);
service.Dispose();


}
catch (Exception ex)
{
throw ex;
}
finally
{
webOrig.Close();
}

}


}
}

View SharePoint Documents in an I-Frame in CRM 4.0

Many CRM 4.0 users want to be able to store documents against entities. While attaching documents is one approach a better solution is to use SharePoint as the document store. You don't have to use full blown SharePoint, WSS is perfectly adequate for this solution.
The approach I've taken is to use a SharePoint document library and create folders for each instance of a CRM entity (in my case service requests). You'll need some code in CRM 4.0 that will create a new folder in SharePoint when a service request is created. And you'll need some way of labelling the folder so it's obvious which service request its referring to (the Case Reference appended with the Customer name might be a good solution). I've been lazy and just used the incidentid as the folder name for this example.
I created a 'Correspondence' tab on the Service Request form. I added an I-Frame and the url to my web page. I checked the option to "Pass record object type code and unique identifier as parameters".



Obvioulsy if you click on the link, it opens the document from SharePoint.

The ASP.net page just has a literal on it which will render a table of documents from the relevant document library folder. It passes the incident id onto a web service.
So the Page_Load event code is
string CRMGUID = "";
if (Request.QueryString["id"] != null)
{

CRMGUID = Request.QueryString["id"].ToString();
Service ws = new Service();
XmlDocument xmldom = new XmlDocument();
xmldom = ws.GetSPSDocs(CRMGUID);

Literal1.Text = xmldom.InnerXml;

}

The Web Service code is given below. Note that I am returning a fragment of HTML wrapped up in a top level DIV tag so I can return it as XML.
Also note that this solution assumes that the folders are all in a flat structure underneath the document library. You'll need a more scalable solution if the number of documents will be large. It would be better to use a CAML query to return just the documents you are interested in rather than the approach I use in this example, but I was in a hurry. It also needs to be changed to support other file types than the three I chose.

[WebMethod]
public XmlDocument GetSPSDocs(string CRMGUID) {

SPLists.Lists ws = new SPLists.Lists();
ws.Url = "http://biztalkcrm:8080/sites/complaints";

string sharePointSiteUrl = "http://biztalkcrm:8080/sites/complaints";
string strListName = "SharedDocuments";
string m_UserName = "Administrator";
string m_Domain = "BIZTALKCRM.local";
string m_Pword = "Password1";
XmlDocument xmldom = new XmlDocument();
string html = "";
// {} not used in folder names
CRMGUID = CRMGUID.Replace("{", "");
CRMGUID = CRMGUID.Replace("}", "");


NameTable nt = new NameTable();
XmlNamespaceManager nsmgr = new XmlNamespaceManager(nt);

nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
nsmgr.AddNamespace("z", "#RowsetSchema");
string filename = "";
string fileurl = "";
XmlNodeList results = null;
DateTime lastmodified ;
html += "<div><table width=\"80%\" border=\"1\" style=\"border-collapse: collapse;\" title=\"Document list\">";
html += "<tr><th class=\"tableheader\">Type</th><th class=\"tableheader\">Name</th><th class=\"tableheader\">Last modified</th></tr>";
try
{

XmlNode nodeList = GetListByName(sharePointSiteUrl, strListName, null, m_UserName, m_Domain, m_Pword);
if (nodeList != null)
{
results = nodeList.SelectNodes("//z:row[@ows_FileRef[contains(.,'" + CRMGUID + "')]]", nsmgr);
//results = nodeList.SelectNodes("//z:row", nsmgr);
}
if (results != null)
{
foreach (XmlNode nodeDoc in results)
{
XmlDocument xDoc = new XmlDocument();

xDoc.LoadXml(nodeDoc.OuterXml);

html += "<tr>";
html += "<td>";
if (GetXmlAttribute(xDoc, "//@ows_LinkFilename", nsmgr).IndexOf("pdf") > 0)
{
html += "<img src=\"http://localhost:8080/_layouts/images/pdf.jpg\" />";
}
if (GetXmlAttribute(xDoc, "//@ows_LinkFilename", nsmgr).IndexOf("msg") > 0)
{
html += "<img src=\"http://localhost:8080/_layouts/images/icmsg.gif\" />";
}
if (GetXmlAttribute(xDoc, "//@ows_LinkFilename", nsmgr).IndexOf("doc") > 0)
{
html += "<img src=\"http://localhost:8080/_layouts/images/icdoc.gif\" />";
}
html += "</td>";
filename = GetXmlAttribute(xDoc, "//@ows_LinkFilename", nsmgr);
fileurl = GetXmlAttribute(xDoc, "//@ows_FileRef", nsmgr);
fileurl = fileurl.Substring(fileurl.IndexOf("#") + 1, fileurl.Length - fileurl.IndexOf("#") - 1);
fileurl = "http://biztalkcrm:8080/" + fileurl;
html += "<td>";
html += "<a href='" + fileurl + "'>" + filename + "</a>";
html += "</td>";
lastmodified = Convert.ToDateTime(GetXmlAttribute(xDoc, "//@ows_Modified", nsmgr));
html += "<td>";
html += lastmodified.ToString("dd/MM/yyyy HH:mm");
html += "</td>";
html += "</tr>";
}
}

}

catch (Exception ex)
{

}

html += "</table></div>";
xmldom.LoadXml(html);

return xmldom;
}

The two other procedures are GetListByName and GetXmlAttribute. Please note that you must change the line in GetListByName where I have hard-coded the Guid of the 'All documents' view on my document library.
string viewName = "{FAD1A412-359A-4FF6-BC1E-0A53934791CD}";


public XmlNode GetListByName(string sharePointSiteUrl, string strListName, XmlNode query, string m_UserName, string m_Domain, string m_Pword)
{
try
{
SPLists.Lists lists = new SPLists.Lists();
SPViews.Views views = new SPViews.Views();

lists.Url = sharePointSiteUrl + "/_vti_bin/lists.asmx";
views.Url = sharePointSiteUrl + "/_vti_bin/views.asmx";

System.Net.NetworkCredential cred = new System.Net.NetworkCredential(m_UserName, m_Pword, m_Domain);
lists.Credentials = cred;
views.Credentials = cred;

XmlNode nodeList = null;

string strRealListName = "";

try
{

switch (strListName)
{
case "SharedDocuments":
strRealListName = "Shared Documents";
break;

}
// string query = "’ ";
XmlDocument temp = new XmlDocument();
// search thro all folders
XmlNode queryOptions = temp.CreateNode(XmlNodeType.Element,"QueryOptions","");
queryOptions.InnerXml = "";

// cheat for view name of All documents
string viewName = "{FAD1A412-359A-4FF6-BC1E-0A53934791CD}";
nodeList = lists.GetListItems(strRealListName, viewName, query, null, "",queryOptions, null);
}

catch (Exception ex)
{
// record error message
}
return nodeList;
}
catch (Exception ex)
{
throw ex;
}
}


public string GetXmlAttribute(XmlNode zrowOuterNode, string XPath, XmlNamespaceManager nsmgr)
{

string strInnerText = "";
XmlNode thisNode = zrowOuterNode.SelectSingleNode(XPath, nsmgr);
if (thisNode != null)
{
strInnerText = thisNode.InnerText;
}
return strInnerText;

}