Monday, March 08, 2010

QueryCell Review and License Giveaway

April 2010 Giveaway for Excel Power Users!

Sam Howley, founder of Oak Focus Software has been kind enough to provide me with some licenses for his Excel Add-In, QueryCell. QueryCell was released in June, 2009 by Oak Focus Software. Try it out with a few free queries, running directly in Excel.

Or open it up to unlimited queries with a license key. Just comment on this post (in English please!) with the most wanted feature you are looking for in Excel and your email address in email [at] email domain format. First 3 get a license for QueryCell.

Beware Sam! Third-party vendors of popular Microsoft plugins have the knack of being bought out by the mother ship, either in the monetary way or The Simpsons way. Good luck.

At first glance, the features from this look like a scaled-down version of PowerPivot, applying SQL instead of DAX.

The Install:

  1. Files are placed in C:\Program Files\QueryCell by default. The build I got seemed to be from March 6, 2010. Quick and simple install.
  2. Open Excel (I have 2003 on the PC I tested)

Notice the QueryCell button adding to the annoying Office Live button that I never use.

  1. Time to fire up my sheet of stock ETFs.

clip_image001

Right-clicking to get rid of the Office Live bar, I notice a new CBarSSQL toolbar. The QueryCell add-in.

Clicking on the QueryCell icon hurts my brain at first. What do I do?

clip_image002

Time to check out the tutorials. Lots of info here. Would have liked to see some sort of MS Wizard interface to setting up a data source, with support for embedded, encrypted ole-db data sources, or perhaps connection strings stored on a server. But this is a developer tool, right? Right?!?

Wrong. This is a tool every power Excel user should have.

Select some cells, Right-Click QC-Add Region As Table. Now I get a data source called A and a list of fields to the right.

clip_image003

Ok, type some SQL. Intellisense! SQL Formatting, something Microsoft is still missing! As a SQL DBA, I miss the CTRL-E shortcut to execute SQL. So I click the blatantly obvious Run SQL button.

Run my query and the Query Results fall into the QueryResults worksheet. Cool!

Add another table. Join the two together. Wow.

clip_image004

Click Mark Rows. Run another query. Multi-rule Conditional formatting! In Excel 2003, no less. Complex, repeatable Auto-Filter!

clip_image005

Tried to add the A.Open column. No go, reserved word. Where's my quoted identifiers or square brackets? QueryCell converted the Open column to COLA. Renaming the column should do the trick but if targeting the financial sector with this, a different way of handling reserved words (or at least OPEN) should be on the feature list. No worries!

Clicked the Refresh Regions button in the top left. Automatically detected data sources!

Somehow I feel like I've just scratched the surface on what I can do here. In any case, this is a tool any Power Excel User should check out, even if they don't know SQL.

Some features I would look forward to seeing:

  • Improved SQL support and templates
  • Access-style Query View designer (for those lazy dba’s)
  • Ability to undock the add-in (multiple monitors)
  • Update statements to Excel, and back to SQL
  • More data sources (Analysis Services cubes, Sharepoint Lists, Web Services)
  • Ability to reset defaults

I can see this tool saving hours of time with complex VLOOKUP formulas, conditional formatting, and cut-paste jobs. For repetitive tasks, generating test data, and automated data refreshes from multiple sources, this tool looks a great helper.

Tuesday, March 02, 2010

VMware and SQL and Lock Pages in Memory - SQL Server with Mr. Denny

 

In other words, use Lock Pages in Memory with SQL/VMWare with great caution.

With the recent release of the ability for the Lock Pages in Memory setting to be used on SQL Server 2005 and 2008 Standard Edition I see more and more people shooting them selves in the foot with this setting when running under VMware.  I see this as becoming more of an issue now that this switch is available for Standard edition as I would assume that most virtualized SQL Server installations are done using SQL Server Standard Editions.

VMware and SQL and Lock Pages in Memory - SQL Server with Mr. Denny