Rob Lieving

IIS6 to IIS7 - Catching Ajax errors

2011-04-11T16:18:00.001-05:00

The way Ajax errors are sent to the browser has changed between IIS6 and IIS7. Errors used to be sent automatically and you could parse the XML on the client. Now, the developer is required to do a little more work because IIS traps the error and stops code execution before reaching the end of the method.

For example, the Ext JS Ajax call takes an object with 5 parameters: method, url, params, success and failure. The last parameter is a callback function that is executed if the server sends back a failure.

The good news is that you have to make a small change to the server-side code (only). Another piece of good news is that the code is generic - the snippet will work with most web methods.

To make it work, create a try... block around the code that may throw an error. (This example is written in C#.) Next, add the following catch block:

catch (Exception e) {

HttpResponse Response = HttpContext.Current.Response;
Response.StatusCode = 500;
Response.Write(e.Message);
HttpContext.Current.ApplicationInstance.CompleteRequest();
Response.End();
}

The code above intercepts the error and returns it to the client. If you already had Ajax code listening for failure, you will not have to change the client side at all.

Note: Special thanks to this blog post by Rick Strahl.

Ext JS - Mixed Collection Extension

2011-03-28T11:09:00.002-05:00

At the top of my wish list for Ext JS is some recursive container search logic. There are times when you need to access a control on a form and can get it via a search term.

In this case, I wanted to look for the control with a tab index of one greater than the current control. Ext.getCmp would make one control dependent on another. This logic enables me to find the next control in the container by searching for by property for the next largest tabIndex property.

This extension will recursively search an Ext JS (v 2.x) mixed collection for the first object that has the property you are looking for. The new method is called 'findByProperty'.

/* Extends the mixed collection object and recursively finds an object */

Ext.override(Ext.util.MixedCollection, {

   findByProperty: function (property, value, defaultValue) {

   var obj = defaultValue,

   notFound = true,

   find = function (itm, idx, len) {

   if (itm.items) {
   this.items.each(find);
   }

   if (itm[property] && itm[property] === value) {
   obj = itm;
   notFound = false;
   }

   return notFound;
   };

   this.each(find);

   return obj;
   }
});

Where:

property is the string property you are looking for
value is the property value
defaultValue is the optional value to be returned if nothing is found

Ext JS: relayEvents

2011-03-21T09:39:00.000-05:00

I have found that the relayEvents method in Ext JS (2.x) is one of the most useful and code-saving techniques one can use. The purpose of the method is to allow one object to listen to the custom events of another object.

I have found that the documentation is not that good, which can make somewhat RelayEvents hard to use. Here is my shorthand to make is easy:

listener.relayEvents(broadcaster, ['custom event a', 'custom event b']);

Where:

listener: The object listening for events. Register the array events in the listeners collection of the object
broadcaster: The object firing the events. To fire the event use the fireEvent('custom event a') method within the broadcaster to fire the event

Override setValue in Ext JS

2011-03-10T11:15:00.001-06:00

I am working with Ext JS 2.x and have the need to format numeric values, such as percentages and comma delimited values.

The best way I know to format the numbers is to override the setValue function of the field. My original plan was to put the formatting in the 'change' event, but calling the setValue in the code does not call this event.

The way to override setValue method is easy, but not obvious. The directions are as follows:

[Namespace].form.[FieldName] = Ext.extend(Ext.form.Field {

   constructor: function (config) {

       /// constructor logic here
       [Namespace].form.[FieldName].superclass.constructor.apply(this, arguments);

   },

   setValue: function (val) {

       /// format logic here for val argument
   return [Namespace].form.[FieldName].superclass.SetValue.call(this, val);

   }

});

Fixing IIS Error 401.2 - Unauthorized Access

2011-02-28T14:09:00.000-06:00

I have developed several applications in Ext JS and recently noticed that all my server calls for a grid made two calls to the server. The first call was a fail, but the second call succeeded. This happens so quickly that the user is not even aware of the issue, but I wanted to track it down anyway.

I examined the HTTP traffic using Fiddler, and found this error in the header of the failed call:

You are not authorized to view this page
You do not have permission to view this directory or page using the credentials that you supplied because your Web browser is sending a WWW-Authenticate header field that the Web server is not configured to accept.
---

Please try the following:
Contact the Web site administrator if you believe you should be able to view this directory or page.

Click the Refresh button to try again with different credentials.

HTTP Error 401.2 - Unauthorized: Access is denied due to server configuration.
Internet Information Services (IIS)
---

Technical Information (for support personnel)
Go to Microsoft Product Support Services and perform a title search for the words HTTP and 401.

Open IIS Help, which is accessible in IIS Manager (inetmgr),
and search for topics titled About Security, Authentication, and About Custom Error Messages.

This problem was happening in IIS6 on Win2K3, but I actually observed it happening on II7 on Win2K8 R2. I found a clue on this website, which recommended changing the order of IIS's method for authentication. Unfortunately for me, this post talked about a UI fix that does not appear to be valid for my version of IIS.

Changing the order of authentication is the answer however, although it needs to be done directly in the ApplicationHost.config file. The fix for this issue is as follows:

Find the ApplicationHost.config file in C:\Windows\System32\inetserv\config.
Copy file to new folder.
Open config file in Notepad. Search for NTLM. You will find it in the windowsAuthentication/providers section of the file. The value "Negotiate" will be listed ABOVE NTLM
Copy the entire XML line so that NTLM is the first on the list of providers.
Restart the World Wide Web Publishing service. Once that is done, the issue goes away.

Simulate Report Alerts in Crystal Reports 2008 Server

2010-10-20T08:54:00.002-05:00

Working with Crystal Reports can be tricky. Anyone who publishes the reports on a BOE Server or Crystal Reports 2008 server has to relearn some of the aspects of the reports to create similar functionality to the desktop application.

Recently I was asked to implement an alerting scheme in a crystal report that was published to the server. Since online reports do not support alerts the same way desktop reports do, this is a tricky problem.

The way we decided to do it was to put the alert in the report header. We then tested for the condition that would trigger an alert. If the condition was triggered, we made the report header visible. Otherwise, we hid the report header.

This is accomplished with three formulas:

The first formula declares the test variable and initializes the value to false.
The second formula is placed in the details section of the report. This formula tests for the condition and, if triggered, sets the test variable to true. This formula uses the Crystal WhileReadingRecords function.
The final formula evaluates the test variable. If true, the header is made visible (with a warning message), otherwise it is hidden. This function uses the Crystal EvaluateAfter function.

Here's exactly how it works.

Create a new formula called Declare Globals. The code in this formula is as follows. Place this formula in the header and make it invisible. T is the test variable, and the test is initialized to false.

@Declare Globals code - placed in header

global booleanvar t; // t is for test

t:= false;

Next, create another formula called Test Records. In this example, the test is done in SQL, but it could be done in the report. The only result of the test is to set the test variable to true if the test is triggered. This code should be placed in the details section of the report and again made invisible.

@Test Records code - placed in details

global booleanvar t; // reference the variable declared in the header

whilereadingrecords;

if ({tblData.bad_data} = -1) then t := true;

Finally, go into Section Expert on the report and choose Report Header > Common tab > Suppress (No Drill Down). Un-check Suppress and click the formula editor button next to Suppress. Evaluate t and show/hide the report header based on the value of the test variable. The report header should have the warning in it in big red letters to alert the user.

Code placed in the Report Header

global booleanvar t; // reference the variable declared in the header

evaluateafter({@Test Records});

not t;

SQL Server Error 15023: 'User already exists in current database'

2010-10-06T09:35:00.000-05:00

I received this error when copying a database from one server to another and then tried to bring up a web application. The system user that came over with the database was orphaned from the existing login.

The solution is relatively easy (for one login). Use the sp_change_users_login with the 'update_one' option. This will directly map the user to the SQL login.

exec sp_change_users_login 'update_one', '[your database user]', '[the SQL login]'

Thanks to Pinal Dave's article on this subject.

Solving the ASP.Net Double Hop problem

2010-08-27T15:35:00.000-05:00

This article is in debt to Andrew Hay's excellent writeup on how he solved his double-hop problem.

The double-hop problem occurs when a central application calls a web-service on a second server, but the credentials are not passed to the second server, causing the web-service call to fail. This is because IIS does not pass on the credentials to the second machine.

When this happens you will see the error:

System.Net.WebException: The request failed with HTTP status 401: Unauthorized at...

The worst part of this error is that the web service will work from your machine, but not when you move it to the server for testing.

The double-hop will most likely happen if you use the CredentialCache.DefaultNetworkCredentials or CredentialCache.DefaultCredentials to authenticate the service call.

Problem Solved: System.Net.NetworkCredential

The fix is relatively simple:

Create a new System.Net.NetworkCredential that uses a service account. Use this to authenticate the service call.
Add the service account to the users group on the target server where the web service is located.

The pseudo code looks like this:

Dataset ds = new DataSet("Grid");

using(MyService ws = new MyService) {

char[] delim = ";".ToCharArray();

string[] creds = ConfiigurationManager.AppSettings["ServiceAcct"].Split(delim);

ws.Credentials = new System.Net.NetworkCredential(creds[0], creds[1], creds[2]);

ds = ws.getDataSet(arg1, arg2);

}

return ds;

The Web.Config would have an application setting of "ServiceAccount" with the value "User;Password;Domain".

Follow up with adding the service account to the Users Group on the machine that hosts the web service. This should solve the

ASP.Net Data meets Ext JS: Ext.net – Store and GroupingStore

2010-03-17T21:13:00.013-05:00

Download the source code for Ext.net here.

Overview

Ext JS is my front-end platform of choice for all my ASP.Net applications. The reasons are simple – Ext JS gives the developer more control, supports better coding practices, and takes better advantage of the IIS platform than native ASP development options. But how could that be?

First, there is the question of control. ASP controls are not open source. It’s hard for the developer to understand what is going on under the covers without extensive study. When will the page refresh? When will the control talk to the server? How can I give the control a different look? Why is that Microsoft object emitting CSS? Many of these questions are difficult for most people to understand and take valuable time to understand.

Ext JS controls are open source and have a rich, well documented event model. You can read the code directly if you need to. Ext JS controls don’t make unexpected calls to the server or emit unexpected code The developer controls how data is piped from the sever to the screen, which makes it easier to control events and identify performance bottlenecks.

Simply put, the developer has complete control over everything that call that happens on the client. There are not many mysteries. And aspects that annoy you can be fixed at programming time by looking at the source code.

Secondly, I have always been bothered by the mixed metaphor of ASP.Net development. What’s on the browser? What’s on the server? Do I code this control with HTML or C#? For all the remarkable work Microsoft did to make the process seem like coding a Visual Basic application (it is truly amazing), the actual results can be bizarre and fragmented. .Net languages appear on the page with HTML, which then might have additional code capturing events somewhere else. The page object model is complex, and ASP developers have to learn when and how to place each control on the page to

Ext JS controls are coded entirely in JavaScript and the separation between browser and server is clearly defined. There is no half-HTML/half C# (or VB.Net) control that lives partially in code-behind and partially on the page. There is no reason to carefully wedge control-code between page events to get everything working correctly.

This complete separation tends to drive business logic off the page and back onto the server where it belongs. My personal preference is to keep the server code and UI code as separate as possible. Ext JS enables the developer to keep the server-side code simple and clean. Simply write your UI in JavaScript and expose the web services for the page. This approach also simplifies testing because the developer can easily identify and test the logic used in each page.

Finally, Ext JS makes it possible to leverage the absolute strength of IIS, which is delivering web services. It has always struck me as strange that ASP.Net controls do not explicitly consume services. In an effort to make the communication seamless, Microsoft actually minimizes the strengths of exposing SOAP services. This, in turn, leads to code-bloat, because the developer has to write custom code to deliver data to ASP.Net controls and then additional code to deliver data to other applications.

Despite all these advantages, communication between Ext JS and ASP web services can be tedious. Ext JS was written by people who are clearly oriented towards JSON data delivery. And why not? It’s lightweight and native to JavaScript. There is the XML reader, but the average .Net/Ext JS developer still has to write tedious (and needless) code to parse the data into stores.

But IIS (.Net) is an XML-oriented language. It does not serve JSON without specialized server-side code and some hacking. What if you work in a corporate environment that does not allow non-standard software to be installed on the server? Shouldn’t the goal of the programmer be to stick to the standards and also leverage what each piece of software does best?

Ext.net.Store and Ext.net.GroupingStore

Ext JS.net started with simple questions. Why should I write JavaScript code to describe my data structures, when an ASP.Net web services already delivers that data to my page? Would my code be less coupled if I worried more about the UI and less about the structure of the data coming to my page?
The result is Ext.net.Store and Ext.net.GroupingStore. The goals of this project are as follows:

Reduce boilerplate code – on the server and the page
Increase web service reuse/simplify server-side code
Simplify data transport and consumption

Table names and data types have already been written and defined on the server – why write them again?

How does Ext.net work?

The Ext.Net stores intercept the XML returned from a web-service call and build the data set before the store is completely initialized. This means that the data definition that is defined on the server and delivered in the web service automatically becomes the data definition in the store.
In order to take advantage of the store, the programmer simply exposes an ADO DataSet as the return data type. Ext.Net stores consumes the meta-data, builds a table definition and then populates it with the service data.

On the server, create a standard data set. The names of the tables do not matter.

IIS Server Side Code - C#

[WebMethod]
public DataSet TableData()
{
      DataSet ds = new DataSet("ds");
      string cnStr = ConfigurationManager.ConnectionStrings["local"].ToString();
      using (SqlConnection cn = new SqlConnection(cnStr))
      {
          string strSQL = "SELECT * FROM tblDataVals";
          using (SqlDataAdapter da = new SqlDataAdapter(strSQL, cn))
          {
              da.Fill(ds, "data");
          }
          strSQL = "SELECT COUNT(*) FROM tblDataVals";
          using (SqlDataAdapter da = new SqlDataAdapter(strSQL, cn))
          {
              da.Fill(ds, "count");
          }
          cn.Close();
      }
      return ds;
}

The client-side JavaScript is just as simple. In your HTML add a reference to Ext.net.Store. Make sure the reference appears after the references to ExtJs code.

Declare a store by using Ext.net.Store or Ext.net.GroupingStore wherever you would declare a store. All of the config parameters are the same, with very few differences.

Ext.net.Store and Ext.net.GroupingStore will create a reader automatically, so you do not have to declare an XMLReader or any record definitions. On large grids, this will save considerable code.

There is one extra parameter to add to the grid – net. The net object has 3 fields:

dataTable (string): The name of the table that contains the data. This will be translated into the store data.
dataTableId (string): The name of the field that contains the id of the data table.
totalRecords (string): The name of the table that contains the data count. This parameter is optional.

Below is a sample store declaration. Note that there is no record definition because it is created at run time.
A big benefit to this is that the service can change and the grid will keep on working as it did before. For large grids, you also save re-coding record definitions.

Sample Ext.net.Store declaration in JavaScript

var s = new Ext.net.Store({

net: {
     dataTable: 'data',
     dataTableId: 'id',
     totalRecords: 'count'
},
remoteSort: true,
sortInfo: { sort: 'fname', dir: 'ASC' },
proxy: new Ext.data.HttpProxy({
     url: 'EmployeeServices.asmx/GetEmployees',
     method: 'GET'
}),
storeId: 'empStore'

});

The source code for Ext.net.Store and Ext.net.GroupingStore can be downloaded for free from http://extjsdata.codeplex.com/. The code has been tested with Firefox 3.5+ and IE7+. It was designed to be compatible with ExtJs Framework 2.2, but also works with 3.1.

Bookmarklet: Add URL to Remember the Milk from the iPhone

2010-03-16T13:55:00.001-05:00

I use Google Reader on all my mobile devices (currently an iPhone) and frequently find myself wanting to switch to the desktop to finish a story. Google Reader has great desktop integration with other services to save articles, but is very limited on the iPhone. Google Reader mobile allows you to email a link to yourself, but the whole process is painful and I get enough email.

I am also a heavy user of Remember the Milk as my task management system. Wouldn't it be great to be able to add URL's directly from the iPhone? Enter the RMilk iPhone Bookmarklet.

javascript:(function(){
var a = [
'name=' + document.title,
'url=' + location.href,
'due=today',
'priority=4',
'tags=iPhone'
];
location.href = "http://m.rememberthemilk.com/add?" + a.join("&");
})();

The Bookmaklet above adds the URL into the mobile Remember the Milk site. The following values are populated:

name: The name of the page you are viewing will be the task name.
url: The URL is the page you are viewing.
due: The due date can be any legal value that Remember the Milk can validate as a date.
priority: Legal priority values are 1, 2, 3, and 4 (none).
tags: Any tag (or tags) you would like to use to populate your task.

Additionally, there are two other options worth noting:

estimate: Any valid time estimate can be used
list: The integer list id that applies to your list. If you want the URL to go to a specific list, go to http://m.rememberthemilk.com/add. View the page source and find the list of values that contains your list. Use the value attribute from the option tag as your list value.

To add this bookmarklet to your iPhone, do the following:

Copy the JavaScript Bookmarklet above into Notepad.
Edit the values in the array as to how you would like to populate Remember The Milk. Add estimate and list to the array if you would like those values populated.
Copy the new JavaScript into an email and email it to your iPhone. Copy the JavaScript from the email.
Bookmark any page in Safari. Edit the bookmark by giving it a new name and pasting the JavaScript into the URL textbox.

This post is heavily indebted to Steve Ollis, The IT Juggler.

Google Chrome Is Now My Default Browser

2010-02-19T11:02:00.003-06:00

Google Chrome has been updated for the Mac, which means I can use it on all platforms now (Mac / Linux / Windows). It has become my default browser and is now the Swiss Army Knife of my computer.

Following is my list of 'must have' extensions for Chrome:

Delicious Tools - Bookmarking for myself, my blog and my friends
Google Mail Checker Plus - Alerts me when I get emails from either of my 2 Google Apps accounts
LastPass - Secure password management on all devices
Google Voice (by Google) - SMS and voice mail alerts. Plus it converts all phone numbers to dialable links
A Bit Better Remember The Milk - Rearranges the page to a more logical (for me) layout
RSS Subscription Extension (By Google) - Enables RSS discovery in Chrome. Why isn't this included right out of the box?

One side effect of Google awesomeness is that Fluid (on my Mac) just became much less important. If you haven't tried Google Chrome, or you haven't seen the extensions, I recommend taking a look.

C#: Add history to an ACT! Group (and contacts)

2010-02-12T16:22:00.016-06:00

My current project requires interaction with ACT!, both retrieving and setting data.
Working with the ACT! framework is an interesting experience. The code is very verbose and requires some expertise to understand. And it took me a while to realize that the best way to retrieve data is through the database, while using the framework to perform the updates.
The following method writes an email history to the database. It is useful for when you need to record automated processes to groups in the database.
The method takes a GUID as an argument, primarily because there are no unique naming constraints in the group list.

using Act.Framework;

using Act.Framework.Contacts;

using Act.Framework.Groups;

using Act.Framework.Histories;

using Act.Framework.Lookups;

using Act.Shared.Collections;

// simplified connection for multiple methods

privateActFramework actFramework() {

    string cn = ConfigurationManager.ConnectionStrings["actDb"].ConnectionString;

    string login = ConfigurationManager.AppSettings["actLogin"].ToString();

    string pwd = ConfigurationManager.AppSettings["actPwd"].ToString();

ActFramework f = newActFramework();

f.LogOn(cn, login, pwd);

    return f;

// adds email history to the database

public void AddEmailGroupHistory(string GUID, string subject, string body) {

    // create a disposable connection to the database

    using(ActFramework f = actFramework()) {

    // create a new group GUID array with one element

Guid[] gGuid = new Guid[] { new Guid(GUID) };

    // get a group list - the list will contain one group

GroupList gList = f.Groups.GetGroupsByID(null, gGuid);

    // get a contact list from the first group in the list

ContactList cList = gList[0].GetContacts(null);

    // creates an all-zero GUID

Guid activityClearedId = Guid.Empty;

    // creates a system type of Email sent

HistoryType hType = new HistoryType(SystemHistoryType.EmailSent);

    // the history is public

    bool isPrivate = false;

    // start and emd (duration) are equal

DateTime startEnd = DateTime.Now;

    // Create a new history object

History h = f.Histories.CreateHistory(cList, gList, activityClearedId,

hType, isPrivate, startEnd, startEnd, subject, body);

    // commit the history to the database

h.Update();

ExtJS Helper Functions - SelectionModel Modifications

2010-02-02T09:40:00.001-06:00

One of the application patterns I implement in ExtJS is to have ViewPort containing a grid with records for a user to edit. The user double-clicks a record on the grid, which opens a window to edit the information. When complete, the user then closes the window and the grid refreshes.

The issue is that after the grid refreshes, the user loses her place on the grid as the selection goes back to the first record.

Additionally, the RowSelectionModel() only implements selection by index, which means that if there are concurrency checks, you cannot automatically go back to the index you were on or guarantee the new index will be correct.

These methods attempt to correct these shortcomings. The first extension implements a selectById method for the RowSelectionModel object.

The second extension implements a SelectAfterLoad method on the GridPanel (and all of it's descending objects). When closing the form, take the id from the record you are using and pass it into the selectAfterLoad method. The method will kick off a single event to find the id in the current loaded record set. If the id of the record is not found, the default 'selectFirstRow()' method is called.

// extend the RowSelectionModel
Ext.override(Ext.grid.RowSelectionModel, {

// enables the selection model to select by record id
selectById: function (id) {

var sm = this, // hook to the selection model
   s = sm.grid.store, // reference to the store
   f = false,   // found (initialized as false)
   idx = 0;   // record index

   // if store has no records, return control
   if (s.getCount() === 0) {
   return;
   }

   // iterate through each record, looking for the id
   s.each(function (r) {
   // if the record id matches the passed id
   if (r.id.toString() === id.toString()) {
   // found is true
     f = true;
   // select the row
     sm.selectRow(idx, false);
   // return false to exit
     return !f;
   }

   // iterate the index
   idx = idx + 1;
   // return true to continue iteration
   return !f;
   });
   // if not false, exit
   if (!f) {
   // select the first row
   sm.selectFirstRow();
   }
   }
});

// selects by record id after the grid reloads
Ext.override(Ext.grid.GridPanel, {

   // selects row by id after store loads
   selectAfterLoad: function (id) {

   // get the grid selection model
   var sm = this.getSelectionModel();

   // select record by passed id argument after store loads
   this.store.on({
     'load': {
   fn: function (t, recs, opts) {
   sm.selectById(id);
   },
   single: true
   }
   });
   }
});

ExtJS Helper Functions - GetValue / SetValue

2010-01-20T18:35:00.008-06:00

This is my first (of several planned) functions to increase the functionality of the ExtJs functionality.

These methods extend the container object and make it easier to set and retrieve values from the controls within the container. A great benefit is that the search will go into all the nested containers as well.

// Shortcut for adding methods to the prototype
Function.prototype.method = function (name, func) {
this.prototype[name] = func;
return this;
};

// Works on all containers (windows, formformpanels, etc.) and will
// even search subcontainers. The control must have an identifiable id
// and a setValue method.
// In this method c is the control id and v is the value to be set.
Ext.Container.method('setValue', function (c, v) {
this.findById(c).setValue(v);
});

// Works on all containers as above. The control must have an identifiable
// id and a getValue method.
// In this method, c is the control id of the value to be retrieved.
Ext.Container.method('getValue', function (c) {
return this.findById(c).getValue();
});

Using these functions on the container saves a tremendous amount of code devoted to looking up and setting values. Just create a variable for the container and then use the getValue retrieve the value or setValue to set the value.

For instance, if you have a window object named 'win' and a textbox with an id of 'txtName', you can merely use:

win.setValue('txtName', 'Michael');

Doing so will set the TextBox value to Michael, even if it is deeply nested in a form panel and several columns.

Links:
http://extjs.com

How to Turn Your iPod Touch into a SIP Phone with Nimbuzz and SIPGate

2010-01-01T15:46:00.001-06:00

Many sites on the web that show how to make and receive phone calls on an iPod touch. But to anyone who has attempted it, you will quickly find that implementation is not fast or easy. The following shows how to make your iPod Touch into a SIP enabled phone using SIPGate. Note that these instructions will work with any phone that can load Nimbuzz (such as Nokia, Blackberry and Android) phones.

Get Nimbuzz software and SIPGate account
1. Get Nimbuzz. If you have an iPod touch, get it from the App Store. Otherwise, go to the Nimbuzz site and follow the download and installation and account setup instructions.

2. Get a SIPGate account at the site of your choice. SIPGate is a German company, so European customers can get European numbers. US phone numbers are available at http://www.sipgate.us/. At this time, only California numbers are available. SIPGate offers free accounts for one person (as of the time of this writing).

Configure Nimbuzz
Nimbuzz offers SIPGate as one of the native SIP integration options. In my testing, this native integration does not work. To make matters worse, the required SIP settings are hidden deep within SIPGate. Here's how to configure Nimbuzz with SIPGate to make it work. Please note, the instructions as follow are for the iPod Touch. The UI for other phones may differ slightly or significantly.

1. Open Nimbuzz, choose Call (telephone icon) at the ribbon at the bottom of the screen.

2. Nimbuzz Out is the default provider. You will see Nimbuzz Out $0.00 > in the bar at the top of the screen. Push the > to see the Nimbuzz call settings screen. You will see NimbuzzOut, SkypeOut and SIP as options. Push SIP to see a list of SIP/VoIP providers.

3. Go to the bottom of the list and select Other VoIP Account. You will see Sipgate.DE/Sipgate.US as options - do not select either of these as they do not work.

4. Get your SIP username and password from the SIPGate website. Note: Your SIPGate VoIP user name and password are not the same user name and password you use to log into the website.

Push Settings at the top-right corner of the SIPGate screen.
Click the left-most phone and choose SIP Credentials from the menu.
The SIP-ID is your user name and SIP-Password your password for connecting Nimbuzz to SIPGate. The Domain and Proxy are sipgate.com (unless otherwise noted).

Once you have followed these steps, you should be able to make and receive calls from your Nimbuzz application without any problems.

Implementing PGP File Encryption in ASP.Net/C# Using GnuPG

2009-10-02T12:12:00.008-05:00

I was recently asked to implement PGP encryption for a file before sending it to a vendor. In performing my research, I found GnuPG to be the easiest solution to understand as well as posing the least amount of risk for implementation. The great thing about GnuPG is that you can download it and the instructions are easy to follow for encrypting files.

Unfortunately, I also found are some challenges during the implementation. The existing documentation on the web is also confusing and is old. This post is an attempt to update the existing documentation to 2009, provide a working coded example, and give a quick and easy roadmap for implementing PGP in an ASP.Net project.

Step 1 – Download and install GnuPG (GPG)

The main GnuPG page is http://www.gnupg.org. The Windows download can be found here. The version I refer to in this post is GnuPG 1.4.10b.

Installing the GnuPG software is easy as it uses a standard installer.

Note: Be careful when installing the application on the server. Choose a Windows account that is generic to installations. There is a mistake in the installation process where important application data is installed in the Application Data directory of the account performing the installation.

You can find the files by installing GnuPG on your local machine and then looking in C:\Documents and Settings\[%Windows User Name%]\Application Data\GnuPG. Several key files are installed there and you will need to reference this directory from the ASP.Net code. This is the biggest ‘gotcha’ for the entire process.

Update (2011-01-12): For 64-bit servers, the directory is C:\Users\[user name]\AppData\Roaming\gnupg

Step 2 – Learn GPG

An excellent introduction to GPG has been written by Brendan Kidwell and can be found here.
You will find excellent instructions for installation and use in Windows and Linux. Pay special attention to the GPG Cheat Sheet and make sure you successfully encrypt and decrypt files from the command line before proceeding with coding.
You will need to install any public keys needed for encryption before running the code, as well as understanding how to retrieve the name of the name of the key (using –list-keys) before running the C# below.

Step 3 – Implement the PGP Object

Following is the code that I use to encrypt files. It is heavily indebted to an article at the Code Project on decrypting files by Kurt Mang.

using System;
using System.Diagnostics;
using System.IO;
using System.Security;

/// <summary>
/// File encryption wrapper.  Executes GNU PGP as described here:
/// http://www.glump.net/howto/gpg_intro
/// And downloaded from here:
/// http://www.gnupg.org/download/index.en.html#auto-ref-2
/// </summary>
public class PGP
{
/// <summary>
/// Path to the PGP software
/// </summary>
string _pgpPath = @"C:\Program Files\GNU\GnuPG\gpg.exe";

/// <summary>
/// The path to the PGP file can be changed (if needed) 
/// or executed
/// </summary>
public string PgpPath {
get { return _pgpPath; }
set { _pgpPath = value; }
}
/// <summary>
/// The home directory argument in GnuPG
/// </summary>
string _homeDir;
/// <summary>
/// The location of the PubRing and SecRing files
/// </summary>
public string HomeDirectory {
get { return _homeDir; }
set { _homeDir = value; }
}
/// <summary>
/// Public constructor stores the home directory argument
/// </summary>
public PGP(string homeDirectory) {
HomeDirectory = homeDirectory;
}

/// <summary>
/// Encrypts the file
/// </summary>
/// <param name="keyName">Name of the encryption file</param>
/// <param name="fileFrom">Source file to be encrypted</param>
/// <param name="fileTo">Destination file (after encryption)</param>
public bool Encrypt(string keyName, string fileFrom, string fileTo) {

/// File info
FileInfo fi = new FileInfo(fileFrom);
if(!fi.Exists) {
throw new Exception("Missing file.  Cannot find the file to encrypt.");
}

/// Cannot encrypt a file if it already exists
if(File.Exists(fileTo)) {
throw new Exception("Cannot encrypt file.  File already exists");
}

/// Confirm the existence of the PGP software
if(!File.Exists(PgpPath)) {
throw new Exception("Cannot find PGP software.");
}

/// Turn off all windows for the process
ProcessStartInfo s = new ProcessStartInfo("cmd.exe");
s.CreateNoWindow = true;
s.UseShellExecute = false;
s.RedirectStandardInput = true;
s.RedirectStandardOutput = true;
s.RedirectStandardError = true;
s.WorkingDirectory = fi.DirectoryName;

/// Execute the process and wait for it to exit.  
/// NOTE: IF THE PROCESS CRASHES, it will freeze
bool processExited = false;

using(Process p = Process.Start(s)) {
/// Build the encryption arguments
string recipient = " -r \"" + keyName + "\"";
string output = " -o \"" + fileTo + "\"";
string encrypt = " -e \"" + fileFrom + "\"";
string homedir = " --homedir \"" + HomeDirectory + "\"";
string cmd = "\"" + PgpPath + "\"" + homedir + recipient + output + encrypt;

p.StandardInput.WriteLine(cmd);
p.StandardInput.Flush();
p.StandardInput.Close();
processExited = p.WaitForExit(3500);
p.Close();
}
return processExited;
}

}

A couple of things should be noted about this object:

The constructor takes a HomeDirectory argument. This is the Application Settings directory where the GPG application files have been installed.
Obviously the Windows account that runs the ASP.Net code needs to have access to the executable as well as the ability to run executables and create files.
The code checks for the existence of the GPG executable, the source file and the destination file. The reason is that the application can hang without throwing an exception if anything goes wrong.

Some useful SQL Date functions

2009-07-08T16:34:00.004-05:00

I have been working with dates lately and have come up with a few useful SQL Date functions lately. I find myself recreating these functions on all the projects, so I have saved below in the form of a script.

-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns the number of days in a month for the passed date/time
-- ============================================================================
CREATE FUNCTION [dbo].[fncDaysInMonth]
(
@date AS DATETIME
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(mm, DATEDIFF(mm, -1, @date), -1))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns a date from 3 integers
-- ============================================================================
CREATE FUNCTION dbo.fncDate
(
@Year INT,
@Month INT,
@Day INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, @Day - 1, DATEADD(mm, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Calculates the last day of the month
-- ============================================================================
CREATE FUNCTION [dbo].[fncLastDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, -DAY(DATEADD(m,1,@dt)), DATEADD(m,1,@dt))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Returns the first day of the month for a given date
-- ============================================================================
CREATE FUNCTION [dbo].[fncFirstDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, -DAY(@dt) + 1, @dt)
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-02-17
-- Description: Returns a smalldatetime with the time removed
-- ============================================================================
CREATE FUNCTION [dbo].[fncRemoveTime]
(
@dt AS DATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN CAST(DATEADD(month,((YEAR(@dt)-1900)*12)+MONTH(@dt)-1,DAY(@dt)-1) AS SMALLDATETIME)
END
GO

Debugging Print Statement

2009-07-07T11:40:00.002-05:00

I am doing some Stored Procedure testing and trying to push performance as much as possible. One thing that helps is the ability to time how long individual statements take to execute. The print statement has to display the time down to the second or millisecond.

A useful print statement looks like this:

PRINT '[string value]: ' + CONVERT(varchar,GETDATE(),109)

How to Deploy and Test An SSIS Package

2009-06-24T12:49:00.001-05:00

When working with SSIS, it is not immediately obvious how to deploy a package. Following are my short notes on deploying an SSIS package*.

Deploy the Package

While in the Package designer, choose Project > [Package Name] Properties. The Configuration manager dialog will appear.
Choose Deployment Utility from the tree.
Change the CreateDeploymentUtility option from False to True. Note the DeploymentOutputPath variable. Push OK to close the dialog.
Open the Solution Explorer and right-click on the .dtsx file and choose Properties. Copy the Full Path variable and use it to find the bin\Deployment folder.
Locate the [Package Name].SSISDeploymentManifest file. Double-click on the file and follow the steps outlined by the wizard to deploy the package.

Test the deployed Package

Open MSFT SQL Server Management Studio and choose Connect > Integration Services from the UI. Choose the Server and connect.
The packages will be saved under the MSDB folder. Right-click on the package to run it.

---

* To re-deploy a package, follow steps 1-5 again.

ASP.Net as Crystal Reports data source

2009-05-18T17:38:00.001-05:00

There are not many good articles on using an ASP.Net web source as a data source. This post attempts to enumerate the settings and make the process straightforward.

Before starting, I want to make a couple of recommendations:

In my experience, embedded schemas do not work. That means that working with native types (such as DataSets) that have embedded schemas won’t work well.
Since embedded schemas don’t work, use a simple XMLDocument object as your return value.
You will need to write your own schema for service. Again – keep the return XML simple.

The instructions for creating an Crystal Reports XML Data Source are as follows*:

Open the database expert. Choose Create New Connection / XML.
XML (data source) type and location: Choose enter in your service in the URL HTTP(S) XML URL textbox. Check the box for Specify Schema File. Click ‘Next’. There are a couple of items to note here.
- Crystal gives an option for choosing Use Web Service Data Source. I cannot get this to work – don’t choose it.
- The URL for the service is the .asmx path/service name. For instance, if your service is getGrid on the web-apps server, the path is http://web-apps/services.asmx/getGrid. Adjust the path as needed to your .asmx file.
HTTP Authentication for XML file: This screen deals with security. I typically work inside a corporate firewall without security, so I leave these fields blank. If necessary, fill in the User Id and Password.
Schema file type and location: I put my schema files on the server, but usually test with a local XSD file. Choose HTTP and the path, if the file is on your server. Otherwise, if the file is local, choose Use Local Schema and point the path to the local schema.
HTTP Parameters: Click the Add Property … button to add your parameters. The Value can be left blank, but the Property must be filled in with the parameter names. Service parameters are case sensitive, so make sure that the case matches.
Clicking Finish will take you back to the data screen. You will be given an options for the XPaths to the data in your source. If successful, you will then have data from a web service in your report.

---

* Tested on CR XI R2/CR 2008

Regular Expression List

2009-04-21T08:53:00.004-05:00

In an earlier post, I showed some regular expressions I had found for manipulating strings in a PDF project.

The following is a list of other expressions I have developed for use on other projects. I plan to update this list as I create expressions.

Regular Expressions

Expression (C#):
@"[$.\d+.$]+$"

Purpose:
I used this expression to rename spreadsheets. This expression finds ([digits]) at the end of a string. Further code parses and increments the number inside the parentheses.

Return equation

2009-03-09T17:40:00.008-05:00

The following equation calculates a return for 1 period:

((Previous Return + 100) * (Current Return / 100)) + Previous Return

Where:

Previous Return is the previous period return
Current Return is the current period return

Find the row counts for all the tables in a SQL Server 2005 database

2009-02-24T08:18:00.009-06:00

Have you ever needed to investigate an application database and quickly determine where the records are?
The following script returns a rowset that contains all the table names of a SQL Server 2005 database along with the row counts.

-- declare the variables

DECLARE @i INT, -- integer holder

@table_name VARCHAR(50), -- table name

@sql NVARCHAR(800) -- dynamic sql

-- create the temp table. Initialize count column to -1

CREATE TABLE #t (

[name] NVARCHAR(128),

rows CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

SELECT @table_name = [name]

FROM sysobjects

WHERE xtype = 'U'

-- Initialize i to run at least once

SET @i = 1

-- loop while rows are still being selected

WHILE (@i > 0)

BEGIN

-- create the dynamic sql that updates the row counts

-- for each table

SET @sql = 'INSERT #t ([name], rows, reserved, data, index_size, unused)

EXEC sp_spaceused ['+ @table_name + ']'

-- execute the dynamic sql

EXEC sp_executesql @sql

-- find out the name of the next table

SELECT @table_name = [name]

FROM sysobjects

WHERE xtype = 'U'

AND [name] NOT IN

(

SELECT [name]

FROM #t

)

-- stop looking if no rows are selected

SET @i = @@ROWCOUNT

END

-- return the results

SELECT *

FROM #t

ORDER BY reserved DESC

DROP TABLE #t

Script to change object owners

2009-02-17T16:00:00.003-06:00

I have recently started working with SQL Server 2005, and the first thing I noticed is that I cannot automatically set the owner of a script at creation time. SQL Server 2005 automatically adds my user name as the object owner.

The following script, adapted from Scott Forsyth's blog, batches up the changes. Change the name of the old owner (@old) and the new owner (@new) and it will change the ownership for all the database objects.

Script to update SQL Server Object Ownership

DECLARE @old nvarchar(30), @new nvarchar(30), @obj nvarchar(50), @x int;

SET @old = 'domain\user-name';

SET @new = 'dbo'SELECT @x = count(*);

FROM INFORMATION_SCHEMA.ROUTINES a

WHERE

a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

AND a.SPECIFIC_SCHEMA = @old;

while(@x > 0)

BEGIN

SELECT @obj = [SPECIFIC_NAME]

FROM INFORMATION_SCHEMA.ROUTINES a

WHERE

a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

AND a.SPECIFIC_SCHEMA = @old;

SET @x = @@ROWCOUNT - 1;

EXEC sp_changeobjectowner @obj, @new;

END

Direct Link to a new Google Spreadsheet

2008-12-02T09:55:00.009-06:00

One of the advantages to having web-based documents is that you can open them from any desktop in multiple browsers.

I use Google Apps for my online office suite, but find that Zoho is a strong competitor for many reasons. One thing I like about Zoho is that they publish a URL that allows you to open a 'scratch' worksheet. Zoho does not require an account for this spreadsheet. If you have a Zoho account, you have the option of saving it when done. Otherwise, you can quickly do spreadsheet calculations, export it, save it, or close it when you are done.

But what about Google Apps? Do they publish a similar URL? It turns out they do, but it is hidden. After trial and error (and watching my browser carefully), I was able to come up with the URL for a new Google Apps spreadsheet. (Note: This URL requires a Google Apps account - which is free.)

The URL is as follows:

http://spreadsheets.google.com/a/[Google Apps Url]/ccc?new&hl=en

Where [Google Apps URL] is the URL to your Google Apps. Just post it on your iGoogle bookmarks widget and you can start launching new spreadsheets right from your homepage.