02 December 2008

Direct Link to a new Google Spreadsheet

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.

19 August 2008

Installing Business Objects Web Services

We recently installed the Business Objects Enterprise at this client to get web access to crystal reports.  One of the excellent features (of many) of this software is the ability to incorporate the web application into other applications. 

Reports are exposed in two ways:

  • The Enterprise Server can launch reports via URL, which is an easy way to launch reports directly from inside any application.  The disadvantage is that the URLs are static and have to be coded every time the BOE repository is updated.  For example, if a new report is added for an application, the new URL has to be added to the list and possibly coded back into the application.  The advantage to URL access is that it's simple to implement and works with no further server modification.
  • Business Objects publish an extensive web services API.  However, the instructions for installation that ship with BOE can be pretty confusing and daunting.  In the end, I found the instructions incomplete and difficult to follow.  I think installation complexity is the biggest implementation impediment.  However, if one can get through the installation, the improved functionality is well worth the pain.

The installation steps I followed can be found by clicking here.  (The directions here are for XI R2/Windows Server 2003.)

Start by installing Business Objects and making sure Tomcat is installed.  In our case, Tomcat was installed by default.

The BOE web services installation instructions are found by opening the Dev Library and navigating to Web Services SDK > Web Services Server Components > Administrator Guide > Installing Business Objects web services.  There are several steps worth reviewing in this document, but I have found a simpler way as follows.

Then do the following: 

  1. Ensure that the Java SDK is installed.  On the server, open the command prompt and type java -fullversion.  If Java is installed, the version is returned.  If not, follow the instructions for downloading the Java SDK.
  2. Locate the dswsbobje.war file.  This is the file that creates the web services.  The file automatically installed in following directory on Windows servers:   [install_dir]\BusinessObjects Enterprise 11.5\Web Services\en\
  3. Deploy the .war file.  Copy the .war file into the [install]\Tomcat\webapps directory.  If a directory with the same name of the .war file already exists (in this case dswsbobje), rename it to 'zdswsbobje' as the installation will not overwrite the existing files.  Restart the Tomcat service, either through the Tomcat management console or the Business Objects Central Configuration Manager (CMC).  Tomcat will create a directory in the webapps folder with the same name as the .war file.
  4. Confirm the installation.  Before testing the service, run access the happydsws.jsp page to make sure all the services installed correctly.   The page will display a list of found components and any missing components.  The page can be found at http://[server name]:[port]/dswsbobje/happydsws.jsp.
  5. Access the services.  If the services are working properly, they can be accessed at http://[server name]:[port]/services.

29 July 2008

Executing a stored procedure in Charles River (CRD) via the Workflow Monitor

My current client has a requirement for updating the Charles River blotter intra-day with a dup/contra indicator. The process for implementing this is easy but not straightforward. The purpose of this post is to enumerate the steps for registering a procedure with the monitor and make some guidelines for a successful implementation.

Step 1: Write the stored procedure

The method for implementing a dynamic procedure to run with a workflow starts with a successful stored procedure. The procedure should have the following attributes:

  • Cache as much data as possible in table variables. My rule of thumb is if I need to scan a table twice, I cache the table. I take care to only retrieve the rows and columns necessary for local processing.
  • Take care to only update the rows necessary. This may seem obvious but extra testing will go a long way to keep table locks to a minimum and performance up.
  • The last statement in the query should return a count(*) statement of the elements that may be updated by your procedure. This number will appear in the Workflow Monitor screen.
  • Choose a blotter field that is not currently being used for processing. For our query, we chose the USR_CLASS_CD field as the field to display our indicator.

Step 2: Register the Stored Query

Open the Charles River System Administration application. Choose Setup > Stored Queries from the menus. Name the stored query and register it with the proper Data window.

In the query field execute the query by typing exec proc_name where proc_name is the name of the procedure to be executed.

Make sure the proper groups can see the procedure and that the description is properly populated.

Step 3: Register the Workflow Monitor

Create a Workflow Monitor in the Charles River Administration from Setup > Workflow Monitor Setup. Create your worklfow monitor.

We added (no click) to the monitor name to indicate to our users that clicking on the monitor would not retrieve any records.

The SQL Query field is populated the same as in the Stored Query field, exec proc_name where proc_name is the name of the procedure to be executed.

The privilege filter should be set to 'NONE'. The value message fields should be left blank.

Set the refresh frequency to the maximum time that allows for traders to work and still see timely information. Remember that Workflow Monitors are client-driven. That is, they run at the refresh frequency of each client. As a result, the greater the number of users, the greater you can (theoretically) set your refresh frequency.

Step 4: Create a shadow Workflow Monitor (optional step)

For our process, we created a view that returns the same order records updated by our procedure. This allows the users to quickly drill down to the updated records.

If you follow the steps above and pay close attention to performance, the procedure should execute automatically and give your users a dynamic blotter.

24 July 2008

Find a column in SQL Server 2005

SELECT tbl.[name]
FROM sys.columns col, sys.tables tbl
WHERE col.object_id = tbl.object_id
AND col.name = '[column name]'

03 June 2008

Removing the Date from SQL DateTime

This solution is short and efficient:


05 May 2008

MSOXMLED & Office Documents

I recently deployed an email application that mailed out Excel files in XML from an ASP.Net server to all clients that had trades for the day. One issue we found was that some of the internal people had trouble opening the file directly, while others did not.

This issue was traced to the 'Open' verb and a bad file setting. In essence, Windows does not know how to open Excel XML unless the File Type Setting is correct. Below are the instructions for correcting the issue.

  1. Open the File Settings from any folder by opening an Explorer window and choosing Tools > Folder Options > File Types (tab).
  2. Navigate to the XML Document type. Click on the 'Advanced' button.
  3. Highlight the 'open' entry in the Actions box and click Edit.
  4. The box should have the following settings:
File Types Settings
  • Action: open
  • Application used to perform action: "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSOXMLED.EXE" /verb open "%1"
  • Use DDE: (checked)
  • DDE Message: <blank>
  • Application: MSOXMLED
  • DDE Application Not Running: <blank>
  • Topic: System
  • Click 'Ok'.

    I also have the 'Confirm open after download' option checked on the file type screen, but am not sure if this makes a difference or not.

    Following these instructions should get you back to opening Office XML.

    01 April 2008

    Resolve ASP Error: Could not create a Disk Cache Sub-directory for the Application Pool

    This is a permission problem. Give IIS_WPG and NETWORK SERVICE permission to access the following directories:

    %systemroot%\System32\Inetsrv\ASP Compiled Templates*
    %systemroot%\IIS Temporary Compressed Files

    * Compiled templates not available on the server I am working with. So I gave permission to the Inetsrv account.

    Addendum to Connection String encryption

    On our production servers, we found that people without administrative rights on the server threw an error because the machine could not decrypt the RSA keys.

    The solution we found worked was to grant the SYSTEM account access to the crypto key. This is accomplished via the following command line:

    C:\WINNT\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pa "nameOfKey" "SYSTEM"

    I also found that in order for me to encrypt the key, my account (or group account) had to have access to the Crypto keys. Just because I was local administrator on the box did not give me access.

    Microsoft makes it difficult (but not impossible) to add the access. The method is to grant your account to C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys. Right-click on all the keys and attempt to view the security tab. Some of the keys will throw an error and tell you to close and re-open the security. When you re-open the security tab, Windows will allow you access to the key.

    26 March 2008

    Put simply: Encrypting ASP.Net Web.config Connection Strings

    I want to take the time to quickly explain what I have learned about ASP.Net / web.config element encryption. In this case, I wanted to deploy a web application to 4 servers and individually encrypt the connection strings for each application.

    The problem was that while it worked well on the first server, the second server either refused to encrypt the connection string or the application would throw an error and crash before delivering the page.

    Prepare the Application Server

    Grant web access to the application folder so that the encryption engine can get access to the file. To do that, go onto the web server and find the physical directory where the application resides.

    Right-click on the folder and choose Properties. Choose the radio option 'Share this folder'. The Edit Alias form will appear. Leave the default values (Access Permissions: Read & Application permissions: Scripts). Push ok and apply to save the settings.

    Prepare the Connection String

    The next step is to create your web.config and add connection information.

    Connection String Example

    <remove name="LocalSqlServer" />
    <add name="Cn_Name" connectionString="Data Source=Server_Name;Initial Catalog=Database_Name;User Id=User_Id;Password=Password;" />

    Add configProtectedData Element

    The connectionStrings element is a descendant of the configuration element. In the configuration element, you will need to add the configProtectedData / providers / add elements and make a reference the System.Configuration.RsaProtectedConfiugrationProvider. Be sure that the PublicKeyToken is accurate. You can match it to the token in the Assembly directory.

    The compiler (in Visual Studio 2005 at least) does not recognize the keyContainerName or useMachineContainer elements. They get underlined by VS, but the errors are ignored. Don't be a purist - just add them.

    ConfigProtectedData Example

    <add name="Name_of_Provider"
    System.Configuration, Version=, Culture=neutral,
    useMachineContainer="true" />

    You may see some documentation saying to add a xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0 attribute to the configuration element. In my experience, this just complicates the final encryption process, so I do not recommend it. On the other hand, it removes the compiler issues noted above. But you are on your own for figuring out the final command line arguments, because you will have to reference the namespace in your commands - something I don't have time to think about or figure out.

    Find Out the ASP / IIS Service Account

    You then need to find out which service account IIS is running under. I did this by turning impersonation off and then creating a simple ASPX page to find the identity. Execute that page and record the value returned on the page.

    ASP Code to find IIS Service Account
    <%@ Page Language="C#" %>
    <% Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name); %>

    Again - if you have impersonation set to true in your web.config, turn it off, otherwise it will just return the impersonated account, which won't help you when executing the encryption command lines.

    The Encryption Command Lines

    Once you have performed all these steps, you are ready to create your batch file to encrypt the connectionStrings section of the web.config. Just copy these lines into

    C:\WINNT\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pc "Name_of_key" -exp
    C:\WINNT\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pa "Name_of_key" "[ASP Identity]"
    C:\WINNT\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pe "connectionStrings" -app "/Application_Name" -prov "Name_Of_Provider"


    • Name_of_key is from the web.config above.
    • ASP Identity is returned from the sample ASPX page. In my case it was "NT AUTHORITY\NETWORK SERVICE"
    • Application_Name is the name of the ASP.Net application where the web.config resides.
    • Name_Of_Provider is the provider name from the web.config above.


    06 February 2008

    Ext JS - One Service Several Stores

    One Service - One Store
    The ExtJS framework makes loading data from an ASP.Net XML Service a snap. The pattern for retrieving data from the server is as follows:
    • Create a service (server side)
    • Define a record structure
    • Define a record reader
    • Define a server proxy (Http) to deliver data to the reader
    • Define a data store and load the data
    Create the web service that serves up your data. Since I am using ASP.Net, the service returns a dataset as an XMLDocument. But the data can also be served back as JSON or put directly onto the page.

    Create a Ext.Record definition for parsing the data. An example is listed below. The record is going to be displayed in a combo box.

    // record object definition
    var xmlRec = new xd.Record.create([
    {name: 'entid', type: 'string'},
    {name: 'entnm', type: 'string'}

    Create an Ext.Reader object. Ext JS provides XML and JSON readers. A sample XmlReader is listed below.

    // xml reader for the entity dropdown
    var xmlReader = new xd.XmlReader(
    record: 'entity',
    id: 'entid'

    Create an Ext.HttpProxy object. For ASP.Net I have found that the URL and method attributes both have to be initialized.

    Inside tip: When using ASP.Net 2.0 you will have to enable POST on your IIS server via the web.config or this won't work. See this Microsoft KB on how to enable POST on IIS.

    // proxy for the entity connection
    var proxyEntity = new xd.HttpProxy({
    url: 'services.asmx/combInit',
    method: 'POST'

    Create an Ext.Store object for delivery to your consumer.
    // create the store
    var str = new xd.Store({
    proxy: proxyEntity,
    reader: xmlReader

    One Service - Multiple Stores
    .Net makes it easy to share connections using ADO.Net and then persist the data in a DataSet on the server for delivery to the client. Imagine if you could load all the 'static' data fields on your page from one service in one call. If you look at the abstraction pattern outlined above, the answer is not obvious - but Ext JS makes it easy.

    The pattern for using one service for deliver to several stores is only slightly different.
    • Register a function on the 'load' event for the first store. This function will load the data into the other stores.
    • Deliver data to the first store as above. Make sure to invoke the load() method.
    • Create your new record and reader definitions.
    • Retain a variable that points to the original data. The example here is for XML.
    • Use a Memory Proxy to load the existing data into a new store(s).
    Registration for events is relatively simple. In the example below, I create a new combo object and pass the XMLData into the constructor of the object. This xmlData contains all the data for all the combos on my form.

    store.on('load',function(t, rec, opt){
    var sct1Cb = new Sector1Combo(entCb.xmlReader.xmlData);
    ....(load other data)

    Creating the record definition and XMLReader object was covered above so I will skip it here. The important thing is to replace the HttpProxy with a MemoryProxy, using the XML data in the constructor.
    var str = new xd.Store({
    proxy: new Ext.data.MemoryProxy(XmlDocument),
    reader: xmlReader

    Using this second technique, you only have to make one web service to load your combo boxes. On the server side, that means you only have to connect one time to the database for your static data. It also cuts down considerably on the code you have to write and maintain.

    24 January 2008

    Remote ExtJS Grid Sorting in ASP.Net

    Remote Grid sorting in ExtJS is relatively straightforward, but there is one "gotcha" to look out for.

    Steps for Implementing Remote Sorting
    The steps for implementing remote sorting are as follows:
    • Create your web service that returns a record set. Give it two parameters that define the field name and sort direction. The value passed back will be 'ASC' or 'DESC'. Just use that in your call to the database for implementing the order in your select statement.
    • In your store, add the 'remoteSort: true' to the constructor.
    • Call the setDefaultSort(field name, direction) on some time before the load.
    The Gotcha
    I have quoted the ExtJS code below from the Ext.data.Store documentation. Make sure that the actual parameters of the web service are 'sort' (for field name) and 'dir' (for ASC/DESC). If you do not name the parameters the same as what is listed below, the remote sorting will not work.

    remoteSort : boolean
    True if sorting is to be handled by requesting the Proxy to provide a refreshed version of the data object in sorted ...
    True if sorting is to be handled by requesting the Proxy to provide a refreshed version of the data object in sorted order, as opposed to sorting the Record cache in place (defaults to false).

    If remote sorting is specified, then clicking on a column header causes the current page to be requested from the server with the addition of the following two parameters:

    • sort : String

      The name (as specified in the Record's Field definition) of the field to sort on.

    • dir : String

      The direction of the sort, "ASC" or "DESC" (case-sensitive).

    Overall, the grid is coming along nicely. Eventually, the code makes sense. But getting ASP.Net to work with ExtJS is proving to be a challenge.

    18 January 2008

    In the trenches with the ExtJS Grid and ASP.Net

    Yesterday I successfully got ExtJS to retrieve data via an ASP.Net web service and wanted to outline the steps here for future reference.

    Before getting started, I want to mention that my client runs ASP.Net 2.0 and have only consented to using the standard ASP.Net Ajax download from Microsoft on their servers. No other server software is allowed for this project.

    Special thanks to amackay11 and his blog posting on how to consume XML. That posting is invaluable.

    Server Side - C# / ASMX / ASP.net

    The server side is relatively straightforward. There are a few requirements for coding the web services.

    The ASMX code-behind service classes should have the WebService (added automatically by Visual Studio) and WebServiceBinding attributes,with the BasicProfile1_1 as the conforming WSI Profile.

    [WebService(Namespace = http://tempuri.org/)]

    The ASMX methods need to be coded with the [WebMethod] attribute (again, this is standard service coding).

    public XmlDocument getEmployees() {...}

    One thing to notice is that the return value is an XmlDocument. .Net makes it easy to return fully formatted XmlDocuments. I recommend using DataSets for return data structures because it is easy to add tables and combine all the data calls into one message from the server to the client.

    public XmlDocument getEmployees() {
    // Create a new data set. The name of the dataset will be the
    // opening and closing tag of the return document
    DataSet ds = new DataSet("EmployeeSet");
    string cmd = "SELECT * FROM tblEmployee";
    SqlDataAdapter da = new SqlDataAdapter(cmd, cn());
    // Fill the employee table
    da.Fill(ds, "Employee");
    //Total count from the table for paging purposes
    DataTable count = new GetRecordCount("tblEmployee");
    // Add the new table to the data set
    // Create and return the data set
    XmlDocument doc = new XmlDocument();
    return doc;

    The previous sample code demonstrates how easy C# makes it to publish the XML for client consumption. An ADO.Net dataset is a nice flexible container and if your page has multiple data consumers on the page, the DataSet.Tables.Add() method makes it easy to throw one more structure into the mix before sending data to the client.

    The previous code will then return an XML document.

    <?xml version="1.0" encoding="utf-8"?>

    Notice the straightforward data structure and how the record count is appended neatly to the end of the XML document.

    Client Side / JavaScript / HTML / Ext JS

    The client side can be tricky to the uninitiated, especially the JavaScript. ExtJs requires two JavaScript includes and one CSS include in the header. You will also need to include your JavaScript file.

    <head runat="server">
    <title>Employee Edit Grid</title>
    <script src="ext-2.0/adapter/ext/ext-base.js" type="text/javascript"></script>
    <script src="ext-2.0/ext-all.js" type="text/javascript"></script>
    <script src="JavaScript/Employee.js"></script>
    <link rel="stylesheet" type="text/css" href="ext-2.0/resources/css/ext-all.css" />

    One disadvantage of using XML is that verbosity of the server/client message. That being said, one advantage of not using JSON in an ASPX is that it requires 2 additional JavaScript includes (which are generated at run time), which makes the compiler complain (in Visual Studio 2005 at least). I mention that here because the includes can sometimes create headaches for the new developer.

    The pattern for developing a grid in Ext JS is as follows:

    • Create a column model object which defines how the columns will be formatted and rendered in the grid
    • Create a record object which defines the data structure
    • Create an XMLReader object to consume the XML Web Service. The XML reader uses the record object to create the final record structure.
    • Create an HttpProxy object to connect to the data source.
    • Create the DataStore object that delivers the data to the data grid. The data store uses an HttpProxy (or some other connection) as well as an XMLReader (or some other DataReader) object as arguments.
    • Create the grid which takes a DataStore and ColumnModel as constructor arguments.

    Although the steps are fairly complicated, I am amazed at how little JavaScript is actually required to get data to the page.

    The biggest 'gotcha' of the entire process is configuring the HttpProxy object. The main question I had was, how do you consume the service?

    Again, .Net makes this easy. Point the URL to the ASMX file / services. The proxy takes a parameters object if the service takes arguments.

    I successfully configured a proxy object as listed in the example below.

    var proxy = new Ext.data.HttpProxy({
    url: 'Services.asmx/getEmployees',
    method: 'POST'

    10 January 2008

    Struggling with ExtJS and ASP.Net

    I am struggling with ExtJS and ASP.Net 2.0 working together. Today I spent the entire day trying to get a simple grid working. After working most of the day, I finally got it to work. Somehow the joy was gone...hoping to work more with this tool

    Following is the methods I used to get a simple unformatted grid up and working.

    Due to corporate constraints at my current employer, I am forced to use XML and cannot take advantage of the latest JSON technology. That meant that the grid data source has got to be XML.

    Exposing XML in ASP.Net is not well documented and not well understood. In fact, I even came across a Jesse Liberty posting asking how to do something similar (I think). (Hat's off to Jesse Liberty - he writes great books - but I have no idea what he is talking about in that post.)

    Creating a raw XML page from an ASPX file turns out to not be difficult. Write code to do the following in the Page_Load event:
    • Create a DataSet
    • Connect to the database and fill a table in the dataset via a DataAdapter
    • Create an XmlTextWriter and populate the constructor with the Response.OutputStream and Encoding.UTF8 arguments
    • Call the XmlTextWriter.WriteStartDocument() method
    • Use the data table.WriteXML method, using the XMLTextWriter as the single argument
    • Call the XmlTextWriter.WriteEndDocument() method
    The markup also requires two tasks:
    • Clear the page of all markup except for the page directive at the top
    • Add the ContentType attribute to the directive, specifying "text/xml"
    The above was done for testing purposes. I think the recommended method for exposing XML is via a Web Service. ExtJS's data reader will (theoretically) read almost XML source. I can definitely vouch that they use a complex XML example on their website for consuming XML.

    ExtJS Sample code
    The JavaScript code in the example leaves a lot to be desired. You want to look at the one here. The difference is that the second example uses the Ext.data.HttpProxy object. This turns out to be a non-trivial difference. Your code will not be able to read the data without it.

    Finally, do yourself a favor and create a record object to pass the data when using the reader to read the data structure. I'm not sure what they are doing in the example - but hopefully I'll learn. Creating a record object will make your life easier.