Thursday, May 10, 2007

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you’ve wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Regardless, it turns out to be non-trivial to perform this export, particularly if you are unable to generate to a database to retrieve the table names. The trick is to use the reporting feature of Visio, but there are many reports and report options, and you will need one that is table-based to get the data into Excel easily.

Note: If you are unfamiliar with the capabilities of Microsoft Visio as a data modeling tool you may wish to take a look at my Data Modeling in Microsoft Visio Tutorial.

Export Procedure

1. This may seem a little unusual, but if you don't have any comments in any of your tables (which really shouldn’t be the case), you will need to add comments for at least one of your tables. Without this step Visio will not display tables in a grid format in the report.

2. Now select the somewhat obscure "Report" item off of the "Database" menu.

3. Only the "Table Report" provides the ability to layout database tables in a grid. Select it and click Finish.

4. Under “Predefined logical/physical reports" Click the button labeled "Default To: General Report" and change it to "Default To: Database Report." This will remove tables formatted per page from the end of the report.

5. Under the "Attributes" tab select “Deselect All” then select the “Table stats summary” option.

6. Click “Export to RTF,” save the file somewhere, and open it with Microsoft Word.

7. (optional) If you have any new lines in the notes field you may have to replace them with spaces. Just do a search and replace for "^l" and replace with " ".

8. Now you’re ready to copy and paste.

And you’re done! Hopefully this tutorial will make life easier for you next time you need to export table names from Visio to Microsoft Excel.

Friday, May 4, 2007

CAML: Nested Too Deep

I discovered an interesting error recently while working with Microsoft’s Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

“Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,”

So let's back up and describe what CAML is for everyone who isn’t yet experiencing this problem.

CAML is an XML based query language that is similar to, and serves many of the same purposes as SQL and DDL. However, CAML is used primarily to interact with SharePoint lists. You can retrieve data using something like:

<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>hello</Value></Eq></Where>

Which is equilivant to WHERE Title = ’hello’ in a SQL statement. At this point I’m sure you’re asking why exchange 80 characters for 21? I suppose the answer is related to how CAML is primarily used in web services requests. Regardless, the nested too deeply error occurs when you try to insert large amounts of data into a list.

The insert syntax is actually all batch based, so if you have 100 list items to insert into a list, you build them all into one big CAML statement and then send it across the wire with a call to Lists.UpdateListItems(). Your CAML statement will look something like the following:

<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
        <Field Name="Title">Hello<Field>
        <Field Name="Document">5</Field>
   </Method>
   <Method ID="2" Cmd="New">
        <Field Name="Title" >World</Field>
        <Field Name="Document">5</Field>
   </Method>
</Batch>

The problem comes when you want to insert something like 19,642 list items into a list. SharePoint complains with “Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,” and suddenly you’re stuck with an enormous CAML statement that you need to break up into smaller batches. How small? Well, for me 300 worked and 500 didn’t, so I set my batch size to 300.

This means that if you are currently experiencing this problem, then have fun rewriting your code to use smaller batches. And, if you're lucky enough to be reading this before you experience the error, then prepare yourself now for this possibility.

Or, if performance isn’t an issue and you don’t want to or can’t rewrite the code that built your CAML then feel free to use this code to batch up CAML batches into smaller batches.

/// <summary>

/// Breaks a larg CAML query into smaller batches to avoid the error "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

/// </summary>

/// <param name="listService">The SharePoint list service to execute the CAML against.</param>

/// <param name="strListName">The name of the list to execute the CAML against.</param>

/// <param name="elementLargeBatch">The CAML batch list of commands to be broken up.</param>

/// <param name="intBatchSize">The size of batches to use.  If unsure use 300, it seems to work fairly well.</param>

/// <returns>The results of all batched queries.</returns>

public static XmlNode ExecuteLargeQuery(

    Lists listService,

    string strListName,

    XmlElement elementLargeBatch,

    int intBatchSize

    ) {

     

    // calculate useful information

    int intMethodCount = elementLargeBatch.ChildNodes.Count;

    int intBatchCount = (int)Math.Ceiling((double)intMethodCount / (double)intBatchSize);

     

    // prepare xml documents for batches and results

    XmlDocument xmlDocBatch = new XmlDocument();

    XmlDocument xmlDocResults = new XmlDocument();

    XmlElement elementResults = xmlDocResults.CreateElement("Results");

   

    // for each batch

    for (int intCurrentBatch = 0; intCurrentBatch < intBatchCount; intCurrentBatch++) {

        int intMethodStart = intCurrentBatch * intBatchSize;

        int intMethodEnd = Math.Min(intMethodStart + intBatchSize - 1, intMethodCount - 1);

     

        XmlElement elementSmallBatch = ListHelper.CreateBatch(xmlDocBatch);

     

        // for each method in the batch

        for (int intCurrentMethod = intMethodStart; intCurrentMethod <= intMethodEnd; intCurrentMethod++) {

            XmlElement element = (XmlElement)elementLargeBatch.ChildNodes[intCurrentMethod];

            elementSmallBatch.AppendChild(xmlDocBatch.ImportNode(element, true));

        }

     

        // execute the batch

        XmlNode nodeBatchResult = listService.UpdateListItems(strListName, elementSmallBatch);

       

        // add the results of the batch into the results xml document

        foreach (XmlElement elementResult in nodeBatchResult.ChildNodes)

            elementResults.AppendChild(xmlDocResults.ImportNode(elementResult, true));

     

        // clean up

        xmlDocBatch.RemoveAll();

     

    }

     

    return elementResults;

}

Useful MSDN References

Incidentally, these MSDN references might be useful if you’re interested in more info:

Introduction to Collaborative Application Markup Language (CAML)

CAML Reference: Query Schema

CAML Reference: Batch Element