oneidía

My new favorite Greasemonkey Script

2011-02-25T11:40:00.001-07:00

Block experts-exchange from all Google search results!

No Experts Exchange UserScript

Now thanks to the magic of Geasemonkey there's more Experts-Exchange cluttering your searches.

Since I use the HTTPS-Everywhere Firefox Add-on from the Electronic Frontier Foundation, I had to add a line to the script to also block Experts-Exchange on https://encrypted.google.com.

Here's the full script with my edit:

// ==UserScript==
// @name           No Experts Exchange
// @namespace      JRice
// @description    We HATES them, Exchange, dirty Expertsses!
// @include        http://www.google.com/*
// @include        https://encrypted.google.com/*
// ==/UserScript==

var count = 0;
list = document.getElementsByTagName('a');
for (i=0; i

gis.stackexchange.com

2010-10-21T08:04:00.000-06:00

The best programming site on the net, now has a dedicated GIS section at gis.stackexchange.com. Not much else to say here.

Simple HTTP Redirect with Querystring in IIS7

2010-09-20T17:00:00.005-06:00

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects. It looks like it'll be as easy Apache finally. That is until you try to redirect a querystring. Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content.

Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content
Click HTTP Redirect under IIS in the IIS management console.
In the HTTP Redirect Dialog:

Check Redirect requests to this destination
Enter your new path ending with $V$Q. e.g. http://mydomain.com$V$Q
Counter-intuitively check Redirect all request to exact destination (instead of relative destination)
Choose the appropriate Status Code (Permanent or Temporary)

Apply Changes and Test

SEO is officially dead

2010-09-15T22:07:00.003-06:00

Yes, I know we're all tired of hearing that some technology is dead. Wired was wrong about the web dying in 1997 and 2010. Truth be told Wired isn't exactly good at identifying mortality. Phone calls aren't dead, the desktop isn't dead, cyberspace isn't dead, drm isn't dead, wifi isn't dead, guitar hero isn't dead and DRM isn't dead, yet (though Zuckerman is trying his best). Well, they were right about the floppy disk.

Wait. That wasn't my point.

My point was that SEO is *actually* dead.

Here's why.

Several minutes ago I google "fckeditor background marinelli". I find a solution that works. Using it I figure out a better way. I blog it. I hit the back button to review the search results again. My blog entry (I just typed) is result six.

http://lmgtfy.com/?q=fckeditor+background+marinelli

SEO is dead. Long live content. Thank you Google Instant.

Theming fckeditor to work with Marinelli Drupal Theme

2010-09-15T21:47:00.001-06:00

Marinelli, a nearly flawless Drupal theme has one major flaw. It is not optimized to work with the fckeditor WYSIWYG editor. There are others as well.

Some googling located some good tips for configuring modules/fckeditor/fckeditor.config.js to work with Marinelli on http://drupal.fckeditor.net/tricks. This solution is good, but it doesn't work well in multi-site situation or if more than one theme is enabled.

Here is a simpler solution:

Go to admin/settings/fckeditor and select "edit" next to the profile you want to fix.
Make sure under "Style and template files" your Editor CSS is set to "Use theme CSS"

Under "Advanced Options", find "Custom javascript configuration" and paste the following:


FCKConfig.BodyId = "primary";
FCKConfig.BodyClass = "singlepage";
FCKConfig.EditorAreaStyles = "body{background:#FFFFFF;text-align:left;font-size:0.8em}";

Spank "Save"

If you are running a multi-site installation as I am this is really the way to go as I share my fckeditor module via sites/all/modules.

Another option is if you want to go all kind of whacky with your javascripting is to create a separate fckeditor.config.js in your theme directory and choose "Yes" under "Advanced Options" > "Load fckeditor.config.js from theme path."

Using Drupal Feeds Module to Import Content from MS Access

2010-09-13T01:14:00.004-06:00

Feeds works brilliantly, but there is a couple weird gotchas when working from MS Access to Drupal that are worth documenting:

Seems self-explanatory, but since you are importing as .txt or .csv make sure you remove all carriage returns. In MS Access this involves writing an update query and doing a replace on all Chr(10) and Chr(13). e.g. Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")
Although CCK Date fields import perfectly as MS Access dates, published dates do not. This is because the published field in Drupal is a Unix Timestamp which is a quite different than Access dates. The easiest way I know to fix this is to export your data to Excel and then use the Excel integer date value to convert to a Unix Timestamp.

We've all seen this right? That really long integer you see when you misformat a date?

Anyway, to fix you need to first account for the difference between the start dates of the two time systems and then account for the fact that excel is decimal days and unix is seconds (or something like that).

Formula:
UnixDate = (A2 - 25569) * 86400.

Where:
25569 = Difference in days between 1900(Unix) and 1970(Excel)
86400 = Number of seconds in a day
Source: webconsultingengineering.com
Exporting from MS Access can also cause you problems with Memo fields. Strangely, if you use Text Qualifiers your exported Memo fields will be truncated to 512 characters. Don't do that.
If Importing Taxonomy fields to Drupal, you'll need to edit your Taxonomies at least for now to Free Text. You can set them back later, but at least for me, even if the Term existed it wouldn't insert unless I set it to Free Text. Your mileage may vary.
Apostrophes will cause you nightmares. They terminate strings early. Replace them with right apostrophes or quotes.
Real HTML required. If you're like me and you've stored all your <HTML> as <HTML> in your ASP and .Net applications to get around Microsoft injection filters you'll have to undo all that silliness.

And I think that's it. If I remember any more from this evening I'll edit this post. Good times.

Finding and Replacing Special Characters in MS Access

2010-09-13T00:51:00.001-06:00

Most special characters like Tabs and Line Break and Carriage Returns can be found and replaced in MS Access by using the character codes. But you can't use the find and replace dialog.

Instead, author an update query and enter the following to remove tabs:
Replace([myfield],Chr(9),"")

If removing line breaks you need to replace both the line break and carriage return character:
Replace(Replace([myfield],Chr(10),""),Chr(13),"")

If editing for HTML you'd edit this to the following for HTML line breaks:
Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")

For other characters, look up the ascii character code. (use the numbers in the "Dec" column in the tables)

MS Access Truncating Exports of Memo Fields at 512 Characters

2010-09-13T00:43:00.001-06:00

Recently I was surprised to find that exports of Memo fields to csv and txt truncate at 512 characters.

Thankfully, there is an easy, albeit poorly documented hack. In the export wizard set "Text Qualifier" to "{none}"

Microsoft.Jet.OLEDB.4.0 on a 64bit OS with IIS7

2010-09-10T08:10:00.002-06:00

When attempting to run Microsoft Jet Connection to MS Access Databases on a 64bit Machine you will get the following unhelpful error:
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Googling will point you on a couple crazy paths involving recompiling your application in 32 bit mode. The solution isn't that complicated if you are using IIS7.

In IIS7 right-click on your application pool and choose "Advanced Settings"

In the dialog that opens, set "Enable 32-bit Applications" to "True".

There I fixed it.

Working with Views Templates in Drupal

2010-03-29T00:23:00.018-06:00

Working with Views can be a little confusing for anyone new to Drupal. Don't get me wrong, Views is an absolutely brilliant piece of programming (thanks merlinofchaos!), but there are so many options that finding the correct toggle can be daunting. And no matter how brilliant, eventually you'll discover you've outgrown all available options in rewriting and variable usage and you need to figure out how to modify values via php. The answer? Views templates. And Views helps you out there too!

And How

For this example we are going to focus on one specific problem: creating a list of image thumbnails.

In our hypothetical we have extended a node type via cck to include an image upload called [field_image_value] that we display when viewing the full node. When each node is created a thumbnail is auto-created via the IMCE Plugin on upload. We've given them a suffix of _thumbnail such that our-image-filename.jpg becomes thumbnail our-image-filename_thumbnail.jpg.

For our block output we want something like this for each node retrieved:

Node Title

Node Teaser, limited to two hundred characters...

Sounds easy right? Actually, yes it is, as long as you know the correct steps.

Step 1: Construct View

Construct your view so that you have [field_image_value], [title] and [teaser] as fields. For [field_image_value] and [title] check the last option, Link this field to its node.

Open [field_image_value], which contains the path to the large image and check Rewrite the output of this field and type the following:

Add an argument to limit your view results to just the node type you have created with an image cck field. Add a block display and save your view.

Now if you add your block to a page you should see we everything you want except all the images are fullsize. Time for view templates.

Step 2: Determine view name

Return to your view and under Basic Settings, click Theme: Information

Below, you'll see all the possible template file names you could use to theme your view. In this example, we are interested in theming a specific field when it appears inside of a specific view (not every instance) so we'll want to look under our [field_image_value] for the correct template which should follow this format: views-view-field--[Our_View_Name]--[field_image_value].tpl.php

So, for Our_View_Name the correct filename would be:

views-view-field--Our-View-Name--field-image-value.tpl.php

Step 3: Make your template

So we're not starting from scratch copy /modules/views/theme/views-view-field.tpl.php to /themes/yourtheme/views-view-field--Our-View-Name--field-image-value.tpl.php

Now, if you revisit your view, open Basic Settings > Theme: Information, and click the button at the bottom, Rescan Template Files you'll see that your new template file appears in bold.

Opening this template you'll see it's pretty basic. Out of the box, we're just passing a variable along, but Merlin has provided some documentation to assist with more complicated theming:


<?php
// $Id: views-view-field.tpl.php,v 1.1 2008/05/16 22:22:32 merlinofchaos Exp $
 /**
  * This template is used to print a single field in a view. It is not
  * actually used in default Views, as this is registered as a theme
  * function which has better performance. For single overrides, the
  * template is perfectly okay.
  *
  * Variables available:
  * - $view: The view object
  * - $field: The field handler object that can process the input
  * - $row: The raw SQL result that can be used
  * - $output: The processed output that will normally be used.
  *
  * When fetching output from the $row, this construct should be used:
  * $data = $row->{$field->field_alias}
  *
  * The above will guarantee that you'll always get the correct data,
  * regardless of any changes in the aliasing that might happen if
  * the view is modified.
  */
?>
<?php print $output; ?>

Our situation is pretty straight-forward, we'll just add a tiny bit of php to replace ".jpg" with "_thumb.jpg" for our [field_image_value].


<?php print str_replace(".jpg", "_thumbnail.jpg", $output);?>

Save your template, and flush the Theme Registry cache. You're done!

If you revisit the page displaying your block, you'll see the thumbnails are now displayed.

But what if I don't?

Here are a few gotchas and possible solutions:

If the image displays as an error then there is likely a typo in your template
If the original image displays and not the thumbnail make sure you have rescanned your templates and try flushing all your caches. Also, check that your browser isn't caching, if using in most browsers you can use CTRL-R to do a complete refresh
If your administration theme is different than your production theme, then your templates will not reflect the changes inside of the views previews

Making Code Samples Readable Online

2010-01-07T12:18:00.003-07:00

After lazily just pasting code in <pre> blocks for years, this morning I finally got around to researching a code highlighter like you see on almost all the programming websites.

Thanks to SyntaxHighlighter adding this feature was embarrassingly easy. It is self-contained javascript that works on a pre or script tag decorated with a html class calling a brush alias, e.g.

<pre class="brush: sql">
   INSERT INTO MyWebsite (pre, script)
      VALUES (complete, awesomeness)
</pre>

Oh, but it's not just SQL, no there's a whole easel of brushes included:

Brush name	Brush aliases	File name
ActionScript3	as3, actionscript3	shBrushAS3.js
Bash/shell	bash, shell	shBrushBash.js
C#	c-sharp, csharp	shBrushCSharp.js
C++	cpp, c	shBrushCpp.js
CSS	css	shBrushCss.js
Delphi	delphi, pas, pascal	shBrushDelphi.js
Diff	diff, patch	shBrushDiff.js
Groovy	groovy	shBrushGroovy.js
JavaScript	js, jscript, javascript	shBrushJScript.js
Java	java	shBrushJava.js
JavaFX	jfx, javafx	shBrushJavaFX.js
Perl	perl, pl	shBrushPerl.js
PHP	php	shBrushPhp.js
Plain Text	plain, text	shBrushPlain.js
PowerShell	ps, powershell	shBrushPowerShell.js
Python	py, python	shBrushPython.js
Ruby	rails, ror, ruby	shBrushRuby.js
Scala	scala	shBrushScala.js
SQL	sql	shBrushSql.js
Visual Basic	vb, vbnet	shBrushVb.js
XML	xml, xhtml, xslt, html, xhtml	shBrushXml.js

Steps to install Syntax Highlighter

Go to http://alexgorbatchev.com/wiki/SyntaxHighlighter and download the source code
Upload the source code to somewhere accessible on the internet, note the url
Follow the installation instructions for blogger available here http://geektalkin.blogspot.com/2009/11/embed-code-syntax-highlighting-in-blog.html (general installation and usage here http://alexgorbatchev.com/wiki/SyntaxHighlighter:Usage).
Was it good for you? Send Alex a beer.

Thanks Alex! I sent you enough to cover a pizza.

SQL Server User-Defined Data Types Not Visible to MS Access on Linked Table Refresh

2009-11-19T09:47:00.001-07:00

Had one of those, "am I on crazy pills?!" problems the past few weeks. In an MS Access database I had a whole slew of SQL Server Linked Tables that included User-Defined Data Types(UDT). The UDTs were mostly auditing fields that referenced defaults to pull in the system.user and current datetime.

Everything worked great.

That was until I added a field and asked a user (with very elevated privileges) to refresh the linked tables. They did, but the audit fields referencing the user-defined types disappeared.

At first, I thought it was some silliness in their file-based DSN, but even using my connection (with Windows-based security... so it pointed to their account) the audit fields remained hidden.

To make the whole story more perverse, if I used the same connection logged in as me (I am a dbo) the tables refreshed with the audit fields and they could also see them.

After much googling I finally stumbled upon this post and this msdn article which indicate that user-defined types have permissions including: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION and REVOKE.

To set permissions to allow anyone to view a user-defined datatype, use the following

grant execute on TYPE::[dbo].[user_data_type] to public;

Or the appropriate user or role.

References:

"Microsoft SQL User Defined Types". My Real Life Work, Charles Solar. 2009-08-21.
"User-Defined Table Types", SQL Server Books Online (MSDN). Sep. 30, 2009.

Clearing SQL Server Log Files (ldf)

2009-10-21T16:06:00.002-06:00

Often I'm asked by collaborating agencies and organizations for a copy of our databases. True, I could do an export and give them just the tables, but I'm just making work for them. The easiest solution would be to export the SQL Server database with all the data types, rules, triggers, views and stored procedures.

What's the problem? Log files on my databases are at least several hundred megabytes if not gigabytes. One option is it simply send them the mdf and ask them to attach the mdf and in SQL Server 2008 if you delete the ldf reference in the attach dialog it will auto-create one. If you want to include the ldf to avoid the conversation or have other reasons to reduce the log file it gets a little more complicated because if transaction logging is enabled the simple dropdowns won't do squat.

Compacting a log file for database YOURDB:

Start a new query

Enter the following:

-- Set the database to manipulate
Use YOURDB;
go
-- Truncate the log by changing the
-- database recovery model to SIMPLE
alter database YOURDB
Set recovery simple;
go
-- Compress the log file using shrinkfile 
--([filename w/o extension], [megabytes for new size])
--    OR  
--([filename w/o extension], EMPTYFILE)  - to empty log file
dbcc shrinkfile (YOURDB_Log, 1);
go
-- Reset recovery mode to full transactions.
alter database YOURDB
Set recovery full;
Go

Done!

Installing the Citrix 11 ICA Client XenApp on Ubuntu 9.04 Jaunty Jackalope

2009-10-19T23:38:00.000-06:00

If you have root access:

Install required libmotif3 package from the Ubuntu repositories
Download the en.linuxx86.tar.gz file from Citrix website
Extract the contents of the archive
Open Applications > Accessories > Terminal
Type "sudo bash setupwfc" and enter the root password
Choose "1" to Install Citrix Receiver for Linux 11.0
Accept default install location (/usr/lib/ICAClient)
"y" to Proceed with installation
"1" to Accept License Agreement
Agree to remaining defaults (y) and press 3 to quit installer

Running Citrix now you may get the following error:
You have not chosen to trust "Name of SSL Certificate", the issuer of the server's security certificate (SSL error 61)

To fix this copy all the .crt files from /usr/share/ca-certificates/mozilla to /usr/lib/ICAClient/keystore/cacerts the easiest way to do this is to open a terminal window, browse to /user/lib/ICAClient/keystore/cacerts and enter the following:
sudo cp /usr/share/ca-certificates/mozilla/*.crt .

If you only have User Access:

Install required libmotif3 package from the Ubuntu repositories
Download the en.linuxx86.tar.gz file from Citrix website
Extract the contents of the archive
Double-click on setupwfc and choose Run in Terminal
Choose "1" to Install Citrix Receiver for Linux 11.0
Accept default install location (/home/yourusername/ICAClient/linuxx86)
"y" to Proceed with installation
"1" to Accept License Agreement
Agree to remaining defaults (y) and press 3 to quit installer

Running Citrix now you may get the following error:
You have not chosen to trust "Name of SSL Certificate", the issuer of the server's security certificate (SSL error 61)

To fix this copy all the .crt files from /usr/share/ca-certificates/mozilla to /home/yourusername/ICAClient/linuxx86/keystore/cacerts

These directions modified from nearly perfect, but difficult to follow (for me) directions found at http://techxplorer.com/2008/11/07/installing-the-citrix-presentation-server-client-on-ubuntu/

Repairing Broken Data Sources

2009-10-07T17:29:00.000-06:00

What do you do when you open your map and you have a layer that looks like this? What does it mean?

The red exclamation point is a visual indicator that something is incorrect in the layer's data source. To fix it you'll need to know where the data lives and tell the layer in the map this information. You're giving direction to poor lost layer so it can find itself, you're a metaphysical geographer!

Repairing a data source:

Right-click on the layer, from the contextual menu choose Properties
In the Properties dialog box choose the Source tab
Click Set Data Source
Browse to the location of the data layer, select and click Add
OK out of the Properties dialog

If all went well your data is now displayed.

But what if it isn't one layer? What if you have an entire map of mis-pathed layers?

Here's where ArcCatalog comes to the rescue:

Locate the map in ArcCatalog
Right-click and select Set Data Source(s)
Highlight one of the Data Layers that is in error and click Replace...
The find box should load with the erroneous path of the layer you select, if not enter it
In the replace box enter the correct path and click Ok
Repeat as necessary

But what if they are SDE Layers? This whole batch thing is no help at all.
Yes, this is where it gets nasty. Basically, you're left out in the cold. You can either fix them one at time, or you're going to need a little script my coworker developed I'll share in my next post.

See you in the server logs,
Brent

When 1+2+3 <> 3+2+1 = Stretching Vista Desktops

2009-09-16T16:32:00.000-06:00

I've had the same background on my machine for almost a year now. A south atlas (south up), pacific-centered, night image of our lovely planet that wonderfully confused those that hovered over my shoulder and was an appropriate homage to my cartography profession. It was time for a change and it seemed simple enough. Found a good image and right clicked, set as desktop background. Suddenly my image wasn't stretched across two screens but instead duplicated on each screen. Even switching back to my old image produced the same result. No longer was South America inverted on the left screen and Africa in the right. Now the image was small and duplicated. What the heck?

Way too many google searches later I finally learned that right-clicking on the image and then setting properties in Vista has different results than first opening desktop properties and then browsing to the image and setting the properties.

Search Results to Text File in Windows Vista

2009-09-11T12:30:00.000-06:00

Do a standard search in Windows Explorer.

In this case I want .mxd's modified after March 22, 2009 so in the search box enter “*.mxd date:>3/22/2009”

When search is done highlight all results (CTRL-A)
Hold down SHIFT, hover over results and right-click choosing “Copy as Path”
Paste results in Notepad

Source: http://www.winhelponline.com/blog/print-search-results-in-windows-vista/

The Case IsNull Now

2009-06-15T18:32:00.001-06:00

In SQL Server views I've been using CASE statements to look for nulls for years. I've just nullified this practice.

For the following Employees Table:

EmployeeID	LastName	FirstName	Nickname
39	Carter	James	Jimmy
40	Reagan	Ronald
41	Bush	George
42	Clinton	William	Bill

Would be used to create the View:

EmployeeID	LastName	Handle
39	Carter	Jimmy
40	Reagan	Ronald
41	Bush	George
42	Clinton	Bill

This is the CASE:

SELECT EmployeeID
, LastName
, CASE Nickname
WHEN Null THEN FirstName
ELSE Nickname
END As Handle
FROM Employees

ISNULLified by:

SELECT EmployeeID
, LastName
, ISNULL(Nickname, FirstName) AS Handle
FROM Employees

Forming a New Reality

2009-05-03T00:46:00.000-06:00

This post is part of an ongoing series

There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.

Download the MS Access 2007 Project used in this post.

Now that we have our relationships all set up creating usable edit forms in MS Access 2007 is ludicrously easy.

Creating an Edit Form in MS Access in Four Satisfying Steps:

Open Beer, Open Access Project
Highlight the table for which you want to create a form (let's start with Cities)
Click the Create Tab, Select Form
Press Save, Sip Beer

I'm not kidding. It is that easy. Here's my form (sips beer)

Repeat four steps for ZipCodes:

Wow! We are amazing. What else is there to do? (sips beer)

And that's the problem here, we're just sitting around sipping beer. This isn't real data. Well, it is real data, but it's a tiny little subset. Seven cities, seven zipcodes and nine relationships is not the real world. The real world has complexity.

In the real world Atlanta has 113 zip codes. In the real world the USPS recognizes twelve distinct Atlantas. And there's two in Georgia. Were you thinking Fulton or Dekalb county?

So let's get some real data. Since bafflingly the USPS doesn't give zip code data away I had to do some searching for a zipcode database. I found a text file at PopularData.com that's "a few years old" but will work for this demo. Kudos. Thanks for making free what should be free, can we get a Wikipedia project going on this? Quick everyone go visit PopularData.com and click some ads... and send hate mail to the Postmaster General.

Our new text file when imported to MS Access looks like this (snippet of all 42,741 of them):

Running a quick summation query...

SELECT ZIP_CODES.City, ZIP_CODES.State, ZIP_CODES.County, Count(ZIP_CODES.ZipCode) AS CountOfZipCode
FROM ZIP_CODES
GROUP BY ZIP_CODES.City, ZIP_CODES.State, ZIP_CODES.County;

We find we have 30,244 unique cities... that is provided my assumption that there are not two towns in the same state and county with the same name is correct. Well, it that isn't correct it's their own fault (sips beer).

Now that we have this data, I feel a need to normalize it first. Since, y'all are old hats at this I'll go do the normalizing and just report back the database diagram. Hold on...

... We're back!

Sorry, that took a little longer than I expected, I had to go out to batchgeocode.com and fix 153 locations without latitudes and longitudes. As you can see I made a few changes. The text file we started with was fabulous for reporting, but not maintainable for data editing and relationships, so we had to do a little moving around. Don't worry, it won't be the last time. For the moment though I'm pretty happy with it. Now let's try those forms again.

First we need to setup some new lookups in the table designs for the new relationships.

In the Cities table add a lookup for CountyID
In the County table add a lookup for StateID
In the Cities_ZipCode table add a lookup for TypeID

With that done it's time to lay back and let MS Access do the work. You remember the steps:

Open Beer, Open Access Project
Highlight the table for which you want to create a form (let's start with Cities)
Click the Create Tab, Select Form
Press Save, Sip Beer

Beautiful, we have a new Cities form with our brand new design behind it. Time go polish off the rest of that six-pack. Or maybe not.

You were feeling pretty good about yourself until you realized that almost every state in the union has a Washington County. There's 31 of them to be exact.

I'd call this form less than useful:

There's five Ewing counties! With no idea of which is in which state. I think we can do better.

Let's make a query that displays both County and State. Then let's switch our lookup to view this query. This won't be enough to save this 3,230 record drop-down, but it is a good practice for more moderate lists under a couple hundred records.

First, make a new query titled VU_Counties. You can use the following SQL or match what I have in design view:


SELECT Counties.CountyID, [County] & " (" & [State] & ")" AS Name
FROM States INNER JOIN Counties ON States.StateID = Counties.StateID
ORDER BY [County] & " (" & [State] & ")";

Now go back to the Lookup for the Counties table and switch the data source to your new query, VU_Counties.

Time for our four favorite steps!

Open Beer, Open Access Project
Highlight the table for which you want to create a form (let's start with Cities)
Click the Create Tab, Select Form
Press Save, Sip Beer

Well, that's better. It'll probably work for a while. But I have three more beers in this six pack so I might as well keep "improving" this form (does the sprinkler fading into the robot).

I see two main problems with this form:

You can't find the data you want to edit with 30,243 cities to choose from
Once you find your city, editing the county is still a nightmare

STEP 1: Filtering your data

The Hack
MS Access has the beautiful filter tools built in. To jump to filter by any column in a table on the Home tab look for the Sort & Filter fieldset. Click Advanced and then choose Filter by Form. This will give you a table or form that mirrors the data your were just viewing. Enter parameters to filter the data, and then press the Toggle Filter button in the Sort & Filter fieldset.

Some examples:

Entering CENTERVILLE in the City filed (in the table or form) will limit the display to the cities with this name that the USPS recognizes.
Entering LIKE "*(HI)" in the Counties field will filter the results to cities in Hawaii

For Real People
While those options are great, I've discovered that the average user does is not pleased working with filters. We have to give them something more obvious. This google thing is pretty popular, let's try to model our approach on that.

Open your Cities form and switch to Design View
Select the Textbox tool and drag a box in your header for users to type in the city they want to find.
Open the Property Sheet by right-clicking and choosing Properties or selecting the Property Sheet button in the far right of the Design tab. Change the name of your textbox to txtSearch.
Now add a button next to your textbox. Name it cmdSearch.
In the Properties Sheet for cmdSearch, switch to the Event tab and for the On Click event change the drop-down to [Event Procedure].
Now double-click the ... button next to [Event Procedure]. This will open the VBA Editor. Welcome to programming! Your cursor will fall inside what is called a subroutine. This subroutine executes when the button you just created is clicked. We want the button to requery the dataset behind our form based on what is in the textbox. You want your screen to look like this:
Option Compare Database Private Sub cmdSearch_Click() Me.RecordSource = "SELECT * FROM [Cities] WHERE [City] LIKE '*" & txtSearch.Value & "*';" Me.Requery End Sub
Save and close the VBA Editor

Congrats you've built in search functionality. Lookout Sergey and Larry!

STEP 2: Making Editing Bearable

I still think our County drop-down could use some work. I propose we add a State dropdown above it that filters the list by State to ease picking a county and remove the problem with duplicates.

Like we added the textbox and buttons in the last step, switch to design view and drag a combobox onto the form. This will start a wizard, follow along.

Accept the default values choice I want to look up the values in a table or query. and click Next

States

Add StateID and State and click Next
Choose to sort the State column Ascending and click Next
Accept the default values to hide the key column and click Next
Accept the default to Remember the Value for Later Use and click Next
Change the name to State and click Finish

Now select the combobox and use the arrow keys to move it above the Counties field.

Finally, we need to add some action.

We need to add to our VU_Counties query the StateID. Open the query in Design View and drag and drop StateID from the States table to the query results at the bottom. Close and save
In Design View of your Cities form select the State dropdown and open the Property Sheet.
In the After Update Event, select [Event Procedure] and click ...
In the VBA Editor add the following code:
Private Sub State_AfterUpdate() Me.CountyID.RowSource = "SELECT [CountyID], [County] FROM [VU_Counties] WHERE ([StateID] = " & cmboState.Value & ") ORDER BY [County];" Me.CountyID.Requery End Sub
Save and close the VBA Editor

Test your code. This is far more usable. I still see room for improvement, but at least your users won't lynch you. And if you were following along it's time for beer number six.

Download the MS Access 2007 Project used in this post.

Spreading the Sheets and Letting Relations In

2009-05-01T09:01:00.000-06:00

This post is part of an ongoing series

There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.

In my last post I went off on relational database design patterns. Now let's try implementing these patterns in MS Access.
Download the MS Access 2007 Project used in this post.

In the old days we made spreadsheets. And they got ugly really quick. Let's work with our city-zipcode analogy from last time.

We started with what is basically a spreadsheet in MS Access:

We outgrew this quickly when we added Meridian:

I didn't mention this in my last post, but I see this all too often. Basic database design cue, if you're numbering your columns your design is wrong.

Why? Boise has 29 zipcodes. New York has 161. Washington, DC has 278. Do you really want 278 columns for storing zipcodes?

So you try something else. You create what really is the right pattern, but it lacks referential integrity. If you could spell and type perfectly it would be ideal.

Ok, the answer is "in view" or "in query" for an MS Access world. This is what we want to come up with, it just isn't maintainable in the real world. Take Coeur d'Alene, Idaho for example. In our main database at work there is 34 ways to spell Coeur d'Alene because someone left town a textfield with no zip code validation. What we need to do is break this table into a discrete entity. We only have to type Coeur d'Alene once and numerical dyslexics like me only have to type zip codes once. We throw a table in the middle that is not human-readable that ties the primary keys (unique autonumbers that mean nothing) into the middle table to tie together this many-to-many relationship. Ideally, humans don't even view this table, it sits in the background and stores the relationships human define using forms looking at real world data (city names and zipcodes in this case).

Design View

Datasheet View

* There's some cool things going on here. Since zip codes in New England start with leading zeros, I've made zipcodes a text field. Uniqueness is being enforced through an Index that doesn't allow duplicates and an Input Mask is requiring all zipcodes to be five digits. With this setup both "123" and "ababa" would not be allowed. Additionally we couldn't add "83714" twice. This is being enforced at the table level so all forms, queries and other programs (ArcGIS) that may use this table will have to comply with these validation rules.

Now we relate these together. In MS Access 2007, go to the Database Tools tab and select Relationships. Click Show Table to add your tables and then drag and drop your relationships.

Your tables should already have autonumbers set up as Primary Keys for Cities.CityID, ZipCodes.ZipCodeID and Cities_ZipCodes.ID. When you make the relationships choose the following checkboxes:

Select Enforce Referential Integrity - This will ensure that you do not add a CityID or ZipCodeID to Cities_ZipCodes that does not exist in the main Cities or ZipCodes table.
Do not select Cascade Update Related Records. We we do not need to cascade updates because we are following best practices and not allowing our primary keys to mean anything in the real world. If you were to use zipcode for example as your primary key you would have to set this, because invariably zipcodes will change and your primary keys will have to change. When this is your model bad things will happen. Not right away. After you have thousands of records. Don't let your primary keys mean anything. Choose long integers or guids. Period.
Cascade Delete Related Records - This will delete related records in Cities_ZipCodes when you delete a City or ZipCode from the main Cities or ZipCodes table. This will prevent orphaned records in Cities_ZipCodes. Having recommended this, I will add that this is a dangerous setting for scenarios where inexperienced users have the ability to delete records as the entire history for a record could be deleted, e.g. the 278 zipcodes for New York City if a user were to delete the one record for New York. Make sure your security settings match your users. And consider not really deleting anything, just adding a flag that makes data appear to disappear for most users.

Save and close. Now with one query we can recreate the table dynamically that we had above without 34 misspelling of Coeur d'Alene.

In MS Access 2007 Select the Create Tab and click Query Design. Now use Show Table again to add your three tables. Auto-magically they will relate to each other using the rules you just added in the Relationship Diagram. All that's left is to double-click (or drag) Cities.City and ZipCodes.ZipCode and they will drop into the results area.

You can also swing over to the SQL by clicking an icon in the lower right. When you're a big boy you may come here first, but we all start GUI (it's a fact ask a mid-wife).

SELECT Cities.City, ZipCodes.ZipCode
FROM ZipCodes INNER JOIN (Cities INNER JOIN Cities_ZipCodes ON Cities.CityID = Cities_ZipCodes.CityID) ON ZipCodes.ZipCodeID = Cities_ZipCodes.ZipCodeID
ORDER BY Cities.City, ZipCodes.ZipCode;

Now run the query and you will get the same table we had in Figure 3. So we got reporting down. Now how do we swing data entry.

We'll start with a table hack. In MS Access you can bastardize a table to act like a mini-form. This is a good place to start.

Open Cities_ZipCodes and switch to design view. We're going to add lookups for those two foreign keys, CityID and ZipCodeID so they are human readable.

Select CityID and in the lower panel click the Lookup tab. Add the following settings:

Property	Value	What is going on here?
Display Control	Combobox	This will display a combobox pick list instead of a textbox
Row Source Type	Table/Query	This is the data source type we are using to fill the pick list
Row Source	Cities	Use the cities table to fill the list
Bound Column	1	We saying that the field CityID matches the first field in the Cities table
Column Count	2	Pass across the first two fields: CityID and City
Column Heads	No	Don't display column headings, only really useful if you are bringing back a slew of fields such as City, County and State
Column Widths	0";1"	By settings CityID to 0" I am hiding it and displaying City 1" wide, but since it is the only one it will stretch to however wide the column ends up being. Note this is in inches, semi-colon delimited.

Repeat these settings for ZipCodeID, Save and open the table.

Surprise! We have a human-readable spreadsheet... but every value is validated for referential integrity!

Now before we move to forms we have one more table perversion that MS Access allows: Subdatasheets. This method, combined with the look-ups we just authored will let us manage many-to-many relationships as if they were convenient hierarchies in table view.

Open the Cities table in datasheet (table) view. If you're using MS Access 2007 you'll see some + symbols auto-magically added themselves to your datasheet. Click one and the related records in Cities_ZipCodes will be displayed.

Now, for various reasons, for example you added a second relationship to cities (maybe a states table) this won't appear magically. No worries. Follow these steps:
Select the Home tab, browse across the Records Fieldset and you'll see a small More button. Click this and select SubDataSheet. Find the table you wish to relate, if you have the Cities table open select Cities_ZipCodes and identify the parent and child fields that match, in this case CityID.

This turned into a long post. In the next installment I'll add some forms to manage our postal codes.

Download the MS Access 2007 Project used in this post.

Design for Exceptions

2009-04-30T21:59:00.000-06:00

This post is part of an ongoing series

There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.

One of the biggest mistakes I run into every day is databases and spreadsheets which weren't designed for the exceptions. I get it, most of your data fits in your model. But then you run into data that doesn't fit your world view and you call me. And that's when things get ugly.

We need an example.

Let's use ZipCodes. We all know how these work right? Each city has a zip code. Here's some examples from here in Southwest Idaho:

City	ZipCode
Eagle	83616
Kuna	83634
Idaho City	83631

That wasn't so hard was it? Our spreadsheet is done!

Not so fast cowboy. What do we do with Meridian? It has three Zips: 83642, 83646, 83680.

You've seen this before though and you got it taken care of don't you? That's hierarchical!

Cities
CityID	City
1	Eagle
2	Kuna
3	Idaho City
4	Meridian

(I'll add the City in parentheses for clarity.)

ZipCodes
ZipCodeID	ZipCode	CityID
1	83616	1 (Eagle)
2	83634	2 (Kuna)
3	83631	3 (Idaho City)
4	83642	4 (Meridian)
5	83646	4 (Meridian)
6	83680	4 (Meridian)

You mad dawg! You got it. But wait, the USPS is kinkier than that. 83714 is valid in Garden City, Boise and Hidden Springs! USPS Postal Code Lookup

Now what?! Welcome to the real world where everything is miscellaneous. Hierarchies only exist in human relationships and these days with a 50%+ divorce rate that isn't even true. Nice family bush you got there.

So what to do? You got a many-to-many on your hands. It's funny how common this pattern is... I'd call it more the rule than the default, yet somehow we always want to split things into distinct categories. I think it is a relic of the filing cabinet age that'll disappear in the few years. Google won, Yahoo lost the hierarchy war. Ontology is overrated and this is the pattern behind the free tagging web and real relationships in the real world. It's time for desktop databases to do a better job of handling these relationships. But I'll leave that for another post. For now you need a new pattern. Three tables:

Cities
CityID	City
1	Eagle
2	Kuna
3	Idaho City
4	Meridian
5	Boise
6	Garden City
7	Hidden Springs

ZipCodes
ZipCodeID	ZipCode
1	83616
2	83634
3	83631
4	83642
5	83646
6	83680
7	83714

(I've added the City name/Zip Code in parenthesis for clarity.)

Cities_By_ZipCode
ID	CityID	ZipCode	()
1	1	1	(Eagle-83616)
2	2	2	(Kuna-83634)
3	3	3	(Idaho City-83631)
4	4	4	(Meridian-83642)
5	4	5	(Meridian-83646)
6	4	6	(Meridian-83680)
7	5	7	(Boise-83714)
8	6	7	(Garden City-83714)
9	7	7	(Hidden Springs-83714)

(In reality Boise has 29 Zip Codes and Garden City has two, but let's keep it a little simpler for this explanation)

I picked Zip Codes because they are a human constructed order that's outgrown it's own hierarchy. We could have just of easily used the classic teachers to students model and then dropped in co-teachers (my Dad had that job once), or tried to decide if a tomato is a fruit or veggie, or whether hot burns like fire or jalapeño... the world is full of them. If you look deep enough it's hierarchical relationships that are really uncommon.

In my next post, Spreading the Sheets and Letting Relations In, I'll give some examples of how to work with many-to-many relationships in MS Access.

UPDATE: Above I tangentially mentioned Clay Shirky's talk Ontology is Overrated and it's worth more than that. Like all of Shirky's talks it's brilliant, but this one is one of his best. Five years old and still a great read.

An Inquiry into IN Query

2009-04-29T17:52:00.000-06:00

I had to select a ton of records today for a project in ArcGIS. You know the normal method, hellish SQL repetition: ID = 1 OR ID = 5 OR ID = 6 OR ID = 12 OR ID = 14 OR ID = 27 OR ID = 41 OR ID = 43

The past few months I've been working a lot in SQL Server and this experience made this repetition seem like too much work. So I got lazy and I made an inquiry into IN query: ID IN (1,5,6,12,14,27,41,43)

And it just worked. There's no documentation in the main help file that even mentions it. If you do open the ArcGIS Help and search on "SQL Reference" nearly half way down you find a section on Subqueries. Here they explain that IN queries are supported in geodatabases and EXISTS is supported as well! Who knew? How was I supposed to know this if I hadn't gotten lazy?

Here's what you'll find in the ArcGIS Help File:

Subqueries
NOTE: Coverages, shapefiles, and other non-geodatabase file-based data sources do not support subqueries. Subqueries done on a versioned ArcSDE feature class which has been registered without the option to move edits to base will not return features stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

A subquery is a query nested within another query. It can be used to apply predicate or aggregate functions or to compare data with values stored in another table. This can be done with the IN or ANY keywords. For example, this query would select only the countries that are not also listed in the table indep_countries:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

This query would return the features with a GDP2006 greater than the GDP2005 of any of the features contained in countries:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

Subquery support in file geodatabases is limited to the following:

IN predicate. For example:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

Scalar subqueries with comparison operators. A scalar subquery returns a single value. For example:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For file geodatabases, the set functions AVG, COUNT, MIN, MAX, and SUM can only be used within scalar subqueries.

EXISTS predicate. For example:

EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')

Operator Description

[NOT] EXISTS Returns TRUE if the subquery returns at least one record; otherwise, it returns FALSE. For example, this expression returns TRUE if the OBJECTID field contains a value of 50:

EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)

EXISTS is supported in file, personal, and ArcSDE geodatabases only.

[NOT] IN Selects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names:

"STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida')

For file, personal, and ArcSDE geodatabases, this operator can also be applied to a subquery:

"STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000)

Operator	Description
[NOT] EXISTS	Returns TRUE if the subquery returns at least one record; otherwise, it returns FALSE. For example, this expression returns TRUE if the OBJECTID field contains a value of 50: `EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)` EXISTS is supported in file, personal, and ArcSDE geodatabases only.
[NOT] IN	Selects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names: `"STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida')` For file, personal, and ArcSDE geodatabases, this operator can also be applied to a subquery: `"STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000)`

Remix IN with some external SQL that EXISTS here in ArcGIS One-to-Many Labeling and you might confuse ArcSDE for an RDMS. Just don't try look for anything meaningful in a RELATE.

Making Macro Security Warnings Disappear

2009-04-24T16:11:00.000-06:00

You know those annoying Microsoft Office Macro Security Warnings? There is a way to make them disappear, at least for you.

Here's the Steps.

Find the Microsoft Office Tools folder generally under Start > All Programs > Microsoft Office > Microsoft Office Tools
Select Digital Certificate for VBA Projects (if you don't have such a link see this article)
Fill out the form and name it "Whatever makes you happy"
Open your Microsoft Office Application and Open Visual Basic Editor
Navigate to the Macro that is causing you Warnings
Go to Tools > Digital Signature and click Choose
Highlight the Certificate you just created, e.g. "Whatever makes you happy"
Restart the Microsoft Office Application
This time when you get the security warning choose, "Always Trust this Publisher"
No more messages!

Attachment Reminder - and more for MS Outlook

2009-04-23T16:00:00.004-06:00

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message.

I finally decided to do something about it.

Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you.

What's left for you to do?

In MS Outlook go to Tools > Macros > Visual Basic Editor
Under the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSession
Double-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas)
Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer.
Go back to Visual Basic Editor and paste the Code into ThisOutlookSesson
Save and Close

UPDATE Apr 24, 2009 3:56PM:

In Outlook 2000 to 2003, choose Tools | Macro | Security and set security to Medium. In Outlook 2007, the macro security settings are in the Tools | Trust Center dialog. Set macro security to Warn on all macros.
Restart Outlook.
When Outlook re-opens you will see a security warning dialog. Choose "Enable Macros"
There is a hack around this we'll leave that for another post

Now just try to be rude. Open a new email message and don't bother with a subject. Press send.

Add "Check out this attachment it'll change your world" to the body and spank send.

Ain't that all kinds of slick? It also will remind you if of missing recipients, blank bodies, signatureless messages, large attachments and too many attachments.

Thanks Jimmy you made my day.

VBA Code

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
'
' code to manipulate emails after hitting 'Send', but just before they are
' actually sent
'

If TypeName(Item) = "MailItem" Then

' we only want to work on messages, not contacts/notes etc

Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim Msg As Outlook.MailItem
Dim sRecip As Outlook.Recipient

Set olApp = Application
Set objNS = olApp.GetNamespace("MAPI")
' set object reference to item passed byval so we can manipulate

Set Msg = Item

' test for missing recipients

If Msg.Recipients.Count = 0 Then
Cancel = True
MsgBox "There are no recipients." & vbCr & _
vbCr & "Please select a recipient and re-send your message.", vbCritical
Msg.Display
GoTo ErrorHandle
End If

' test for invalid subject lines & empty body
Select Case Msg.Subject
Case "", "Re:", "RE:", "FW:"
Cancel = True
MsgBox "You are sending a message without a subject." & vbCr & _
vbCr & "Please correct before sending.", vbCritical
Msg.Display
GoTo ErrorHandle
End Select

If Len(Msg.Body) < 2 Then
Cancel = True
MsgBox "You are sending a message without any body text." & vbCr & _
vbCr & "Please correct before sending.", vbCritical
Msg.Display
GoTo ErrorHandle
End If

' check for too many attachments (or too large), it's rude

If Msg.Attachments.Count > 2 Then
If MsgBox("You are sending more than 2 attachments." & _
"Some people might consider this rude. Continue?", _
vbYesNo + vbInformation) = vbNo Then
Cancel = True
Msg.Display
GoTo ErrorHandle
End If
End If

If (Msg.Attachments.Count > 0) And (Msg.Size > 100000) Then
If MsgBox("Your email is pretty big, do you want to stop " & _
"and zip the attachment(s)?", vbYesNo + vbExclamation) _
= vbYes Then
Cancel = True
Msg.Display
GoTo ErrorHandle
End If
End If

' check for missing attachments

If InStr(LCase(Msg.Body), "attach") And (Msg.Attachments.Count = 0) Then
If MsgBox("An attachment was mentioned, but there is no " & _
"attachment to this email. Send anyway?" _
, vbYesNo + vbExclamation + vbDefaultButton1) = vbNo Then
Cancel = True
Msg.Display
GoTo ErrorHandle
End If
End If

' check for missing signature, it's rude, but allow send anyway

If InStr(Msg.Body, "ENTER PART OF YOUR SIGNATURE HERE") = 0 Then
If MsgBox("You forgot your signature!" & vbCr & _
"Do you want to add it first?", vbYesNo _
+ vbExclamation) = vbYes Then
Cancel = True
Msg.Display
GoTo ErrorHandle
End If
End If

End If

ErrorHandle:
Set Msg = Nothing
Set objNS = Nothing
Set objFolder = Nothing
Set olApp = Nothing

End Sub

Folder Contents to File (Vista contextual menu)

2009-04-20T12:39:00.000-06:00

http://www.winhelponline.com/articles/206/1/How-to-add-the-Print-Directory-feature-for-file-system-folders-in-Windows-Vista.html

HowTo Remove Annoying Advertisements and Take Back Your Browser

2009-04-13T22:11:00.000-06:00

I finally got completely fed up with Yahoo Mail today. Consistently the ads would stall the browser and slow down response times, never mind just being annoying.

I've known about Greasemonkey for years but never had the incentive until today to see if I could nuke these annoyances.

It was embarrassingly easy.

Step 1: Get a real browser. Download Firefox.
Step 2: Download and install the Greasemonkey Add-on.
Step 3: Install the Yahoo Mail Cleaner script for Greasemonkey.

Viola! No more ads. And better yet, this isn't a black box, it's ludicrously simple javascript:


// ==UserScript==
// @name           Yahoo Mail cleaner
// @namespace      http://userscripts.org/yahoomailcleaner
// @description    Removes ads from Yahoo Mail (AJAX)
// @include        http://*.mail.yahoo.com/dc/launch*
// ==/UserScript==

if (window.wrappedJSObject.kPartner) {
    window.wrappedJSObject.kPartner.bucket = 0;
}

One if block. It's embarrassing that I lived with ads that long.

But why stop there.

I don't really like ads in facebook either.
There's a script for that: Remove Facebook Ads

Hate how a google image search lands you in iframe hell?
There's a script for that: Google Image Relinker Mod

Think video sites shouldn't burn your eyeballs out?
There's a script for that, too: YouTube Comfort in Black

There's even a script for all of you freaks who think the old facebook was better, though I'm not following you here, so you'll have to go find that one yourself. And accept a little change into your life in the process.

Saving changes not permited in SQL 2008

2009-03-26T15:12:00.000-06:00

There's a nasty little checkbox selected by default in SQL Server Management Studio 2008 that prevents table changes from being committed. It's not permissions, it is an installation and user specific checkbox hidden under tools > options > designers. Uncheck "Prevent saving changes that require table re-creation".

Source and more info: http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

Restoring Orphaned SQL Server 2008 Users

2009-03-16T15:55:00.000-06:00

We keep moving databases across servers and reinstalling SQL Server at my office. Almost everything transitions perfectly except SQL Server Users. Unless the user already exists in the Master Security table, the database user is left orphaned inside the database without the ability to perform any useful function. Like you right now, but we're gonna change that.

Here is an example, note how there is a User Name but no Login Name.
User Name but no Login Name

Step 1: Recreate the User in Master
First, if they don't already exist, is to recreate the user in the master database by browsing to the root level Security folder in SQL Server Management Studio, choose Logins and right-click create user.

You tried to check the database database under the "User Mapping" page didn't you?
Create failed for User 'UserName'.

That was a good idea hotshot, but it ain't gonna work for you. Just create the user and Ok your way out of there. Time for Step 2.

Step 2: Remap User to Database
We need to run a system stored procedure I found on MSDN, it is pretty straightforward, unfortunately it must be performed for each database.
NOTE: There's got to be a better way, and supposedly this method is deprecated, but this is the only thing I could get to work in my instance, so if you got something better please share.


USE DATABASENAME;
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'UserPassword';
GO

Where:
DATABASENAME = The name of your database
UserName = SQL Server Login to repair
UserPassword = SQL Server Login's password to repair

Full documentation on MSDN for sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378.aspx

Converting a string (dd/mm/yyyy) to a date in MS Access

2009-02-11T09:38:00.000-07:00

For the best result in an MS Access query split the string into segments and use the DateSerial function:


GMTDate: IIf([GMT_Date]="",Null,DateSerial(Mid([GMT_Date],7,4),Mid([GMT_Date],4,2),Mid([GMT_Date],1,2)))

where dates are written in the format dd/mm/yyyy (23/12/2008).

When combining date and time fields from string use DateSerial + TimeSerial, e.g.


GMTDateTime: DateSerial(2008, 9, 23) + TimeSerial(10, 39, 1))

Linking to external databases and servers in SQL Server

2009-01-02T12:26:00.000-07:00

In order to select tables in a stored procedure, view or trigger in an external database you must use the fully qualified name in the select statement.

For example, to select from a table in a separate database on the same SQL Server instance you might write:


SELECT     AuthorID, Author
FROM         BooksDb.dbo.PIC_Author

You can also select cross-databases by adding the server name:


SELECT     AuthorID, Author
FROM         Server2.BooksDb.dbo.PIC_Author

Until this morning the SQL Statement above had always worked for me, but this morning using a new server (running SQL2008) I received the following error message:


---------------------------
Microsoft SQL Server Management Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT AuthorID, Author FROM Server2.BooksDb.dbo.PIC_Author
Error Source: .Net SqlClient Data Provider
Error Message: Could not find server 'Server2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
---------------------------
OK   Help

I’d never seen this error before and found this article (http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx) explaining how to use sp_addlinkedserver.

So I executed the following on the SQL Server 2008 box:


USE master
GO
EXEC sp_addlinkedserver
   'Server2'
GO

And afterward the former SQL expression “SELECT AuthorID, Author FROM Server2.BooksDb.dbo…” executed successfully.

Translating Coordinates

2008-12-03T13:54:00.000-07:00

Of all the GIS questions I field, coordinate system related questions are the most frequent. The following is typical:

I got a shape file of land parcels from Bonneville Co. and am trying to overlay that onto the County NAIP imagery. For some reason, when I have the same coordinate system and datum assigned to all layers, the NAIP and shape files are projecting at different scales and not even coming close to lining up. I’ve tried everything in my limited arsenal. If you have any idea of what’s going on, please let me know.

The problem here is not a lack of understanding of the software (or grammar), but misunderstanding concepts behind assigning and translating a coordinate systems.

Translate This
I've found language translation to be the most accurate analogy for making sense of coordinate systems in ArcGIS.

Suppose you're traveling on a ferry in the Baltic Sea (why not?). A frazzled tourist is running from person to person animatedly asking a question. Fortunately, you have a web browser on your phone and you call up Google Translate to assist. You are two dropdown listboxes away from understanding:

First, you must correctly identify what language they are speaking.
Second, you must indicate what language to which you want the information translated (most likely English)

You hand your phone over and they pound quickly into the keys:

missa ovat wc

You've selected English as the output for your benefit, but the original language requires a guess, so you randomly start trying local tongues.

Swedish suggests Ovat has bad aim...

Ms. Latvia haz Water Closet...

Ah, the Finn has to pee!

Ah, but how do we ask the Dutch crew?

Projections in ArcGIS present exactly the same problem. ArcGIS needs to “know” what coordinate system you want everything to be in, and it needs to know what coordinate system the different layers are in to begin with, so it can successfully translate. In some situations (generally where the datum differs) it is a best practice to permanently re-project the data to match the rest of your data. The most insidious errors occur when you incorrectly guess one or more coordinate systems and receive gibberish in response.

IdaHos to the Rescue
So what to do when layers don't play well with others? Out here we bring on the IdaHos. Or "Projection Finder" as I call it professionally. This is a layer I made that is projected in IDTM83 and displays where fifteen common projections fall to use in identifying projections.

How to use this layer:

Open a new map and add the Project Finder layer first. This is important as starting a new map and adding a layer sets the default projection of the data frame to match the first layer added.
For the layer you want to determine a projection for, first make sure it has no projection defined. There are two ways you can do this: in Windows Explorer delete the corresponding .prj file or in ArcCatalog right-click on the layer, select Properties, select the XY Coordinate System Tab and spank the Clear button.
Once the coordinate system is nuked, add the layer to the map.
Zoom to the layer to to see in which projected Idaho it falls.
Now you can go to Toolbox > Data Management Tools > Projections and Transformations > Define Projection and do just that.
Once it is defined, remove it and re-add it to your map and it should move to fall inside IDTM83 since it now has a projection defined it is reprojecting on the fly to match the data frame. This is a good thing.

You're Finnished.

Oliko se hyvä sinulle? Osta minulle olutta.

Downloads

Projection Finder shapefile and layer

Replace ntext with n/varchar(MAX)

2008-11-21T15:44:00.000-07:00

Starting with SQL Server 2005 replace ntext with more efficient varchar(max). varchar(max) and nvarchar(max) store data fields smaller than 8,000 characters in the table rather than as a large object (LOB/BLOB).

Beware when upgrading data currently stored as NText as it will remain a LOB until you run an update query setting the value equal to the current value, e.g.

UPDATE testTable SET testText = testText

Full details and thanks go to Making Stuff Faster

Like?

2008-09-12T13:34:00.000-06:00

How do you search for * and ? (asterisks and question marks) in MS Access?

Bracket the criteria:

SELECT * FROM Table WHERE Column NOT LIKE "*[*]*"

Serving up KML in IIS 6

2008-04-30T16:42:00.000-06:00

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.


Google Earth reads KML and KMZ files. The MIME type for KML files is

    * application/vnd.google-earth.kml+xml

The MIME type for KMZ files is

    * application/vnd.google-earth.kmz

Source: Google KML Tutorial


To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, and type application/octet-stream in the MIME type box.

To create a MIME type for a file without an extension, type a period (.) in the Extension box, and type your MIME type in the MIME type box.

7. Click OK.

Source: Microsoft TechNet

Resultant table not allowed to have more than one AutoNumber field

2008-04-10T11:02:00.000-06:00

Ever get this error on a create table or insert query? The work around is quite simple. Cast the autonumber as an integer. In query design view change your field definition for one of your autonumbers to read:

 XID: CInt([ID])

Or in SQL View:

CInt([ID]) AS XID

(Ran into this error message again this morning and it reminded me I should share the workaround.)

Simple Record-Level Auditing in SQL Server & ArcSDE

2008-04-08T19:00:00.000-06:00

I've had simple auditing set up in my databases for years thanks to our old DBA. This is not robust transaction logging, it just answers the questions "Who created this?", "When was this created?", "Who last edited this?" and "When was this last edited?".

After witnessing a brand new table audit itself without triggers, I set about to understand the setup. This seemed curious to me, so I thought it might also be curious to you.

It turned out to be a two step system which rendered my insert triggers superfluous (at least I think so, tell me if I'm missing something and I need insert triggers after all).

Our solution uses a combination of two methods. For inserts we are going to tie our audit fields to User Defined Data Types which inherit Defaults we have specified. For updates we will build a trigger to log the changes. We will use the following fields in our tables to track inserts and updates:

AUD_NUser - Creator
AUD_NDate - Creation Date
AUD_EUser - Editor
AUD_EDate - Edit Date

STEP 1: Create Defaults
Defaults, according to the SQL Server Help, specify what values are used in a column if you do not specify a value for the column when inserting a row. Often we will manually specify a default (e.g. "0" for an integer field to hold counts)

In SQL Server, right-click on "Defaults" and select "New Default".

Default for logging Insert User

Name: "DEF_UserName"
Value: "system_user"

Default for logging Insert Date

Name: "DEF_Date"
Value: "GetDate()"

NOTE: If the user is logged into SQL Server using Windows Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login (e.g. "MYDOMAIN\myusername"). If the user is logged in to SQL Server using SQL Server Authentication, SYSTEM_USER returns the SQL Server login (e.g. "sa").

STEP 2: Create User-Defined Data Types
User-defined data types allow one to build custom specifications beyond the generic integer, decimal, varchar options. For example, one could create a custom character field of length 5 to store zip codes.

In SQL Server, right-click on "User Defined Data Types" and select "New User Defined Data Type".

User-Defined Data Type for storing Usernames

Name: "AUD_UserName"
Data type: "char"
Length: "50"  (note this may need to be longer depending on your domain and usernames)
Allow NULLS: Check
Rule: "(none)"
Default: "dbo.DEF_UserName"

User-Defined Data Type for storing Timestamps

Name: "AUD_Timestamp"
Data type: "smalldatetime"
Length: "4"
Allow NULLS: Check
Rule: "(none)"
Default: "dbo.DEF_Date"

STEP 3: Creating Audit Fields in the Database Table

Add the following audit fields to your database table. Set the Data Types to your new User Defined Types, note the Length, Default Value and Allow Nulls fields will be configured automatically.

Field       Data Type
AUD_NUser   AUD_UserName
AUD_NDate   AUD_Timestamp
AUD_EUser   AUD_UserName
AUD_EDate   AUD_Timestamp

Congratulations, inserts are now covered! And the best part is, for any future table you only have to repeat Step 3 (we'll look into automating that too in a future post).

On to updates....

STEP 4: Create a Trigger to log Updates

In SQL Server, right-click on your a Table with Audit Fields and select "All Tasks" and then "Manage Triggers". The Trigger Properties dialog will open with "<new>" selected as the name. Below this in the "Text:" textbox enter the following substituting [tablename] and [primarykey] to match your table.

Create Trigger [tablename]_UTrig ON dbo.[tablename] For Update As
SET NOCOUNT ON
UPDATE a SET a.AUD_EUser = System_User, a.AUD_EDate = getdate()
FROM [tablename] a JOIN Inserted b ON a.[primarykey] = b.[primarykey]

You now have basic auditing in your database that works no matter where the edits take place be in SQL Server, MS Access, a custom application or even ArcGIS.

Yes, ArcGIS! And not only when editing a linked SQL Server data table, but also with integrated security now part of ArcGIS, you can add these edit fields to an SDE Table using the same steps described above and log edits to an SDE feature layer.

Making Copies with a HP DesignJet 5000ps (Queue Management)

2008-04-08T17:28:00.000-06:00

At my old job making copies on the plotter was a piece of cake. You went into the Queue, selected the job and number of copies and presto! For some reason, possibly because I hardly make paper maps anymore, learning queue management on the DesignJet 5000ps did not come intuitively.

My latest map kept running out of memory on the plotter, so when it finally did print after spooling for three hours, I had the incentive I needed to explore the DesignJet 5000ps menus.

I must admit that I have waited five years and wasted the total of several days spooling print jobs. It isn't even complicated.

Starting at the Main Menu (use "Top" key to make sure you are at the Main Menu)

Press the "ENTER" button (this will select the first option "Printing")
Highlight "Queueing & Nesting..." and press "ENTER"
Highlight "Queue Management" and press "ENTER"
Highlight the job number (These are negative numbers in the order last printed. Select "-1" for the last job.)
Highlight "Copies = 0..." and Press "ENTER"
Highlight the number of copies you want in the Copies Menu and Press "ENTER"
Press the "BACK" button to return to the Job Menu (Titled "-1:" in this example)
Highlight "Move to Top" and Press "ENTER"

The plotter should start warming up and printing will begin almost immediately. Best yet, it will continue to print with no interruption for processing.

I just made three copies of a 36"x60" shaded relief poster in the time it took to type this out.

Recording a Booby Observation for Posterity

2008-04-04T18:18:00.000-06:00

CC Licensed photo by Rileypie

Building on the database that spurred my Color Considerations, I spent the better part of today debating the best way to log observers in a database. Having documented it to explore the pros and cons with my coworkers, I figured I'd share my musings with y'all as well. What way do you think is best? Is there a better pattern I left out?

OUR EXAMPLE
On a blue-footed booby capture Nov. 11, 2007 we had four biologists recorded on the capture event: Mike, Roy, Phil and Jay.

How do I record this? Let’s review a few options:

OPTION 1
Two Integer Fields to an Observer lookup table

Observer1ID: “272”     (MIKE)
Observer2ID: “312”     (ROY)

NEGATIVE: The rest of the observers disappear in the data abyss.

OPTION 2
One Integer Field to an Observer lookup, One Free Text Field

Observer1ID: “272”     (MIKE)
OtherObservers: “ROY, PHIL, JAY”

NEGATIVE: Nothing lost, but only able to reliably query on Observer1… Other Observers are listed, but there is no way to find PHIL. He could be “PHIL, P., PHILLIP, PHILIP, or FILLUP”

OPTION 3
Observation Table linked to Observation_Observer Table by ObservationID

Observation_Observer Table has the following Structure for example record, “123”

ObservationID    ObserverID   Rank
123              272          1
123              312          2
123              21           3
123              128          4

NEGATIVE: The weakness of this model is that it is possible to enter an observation with no observer since enforcing the creation of child record in a database is difficult and one would have to rely on the interface for data integrity. Additionally, querying subtables is just a pain and often done incorrectly by users.

OPTION 4
Modified Option 3 – With Observers assigned roles.
Observation Table linked to Observation_Observer Table by ObservationID

Observation_Observer Table has the following Structure for example record, “123”

ObservationID    ObserverID   RoleID
123              272          1   (Primary Observer)
123              312          2   (Secondary Observer)
123              21           6   (Sample Collector)
123              128          2   (Secondary Observer)

NEGATIVE: This model shares most of the same weaknesses as Option 3. The addition of roles does codify the each of the observer's parts better, but introduces another weakness that no Role is necessarily required. At least in Option 3 one could produce a 1-to-1 relationship by selecting the highest ranked Observer (lowest number) as long as one tested for nulls first.

Option 5
Modified Option 2 & 4 combined – Primary Observer in the Main Table with Secondary Observers in a subtable assigned roles.

Observation Table

PrimaryObserverID: “272”

Observation_Observer Table has the following Structure for example record, “123”

ObservationID    ObserverID   RoleID
123              312          2   (Secondary Observer)
123              21           3   (Sample Collector)
123              128          4   (Secondary Observer)

NEGATIVE: The only weakness of this is that entering secondary observers may be a little tiring over the free text field Option 2. One may also want to consider the ability to enter free text for anecdotal observations rather than creating another Observer record. Perhaps certain applications would enter structured data in the Observation Comment field and the PrimaryObserver would be “Anecdotal – See comments”. For most incidentals however, follow-up is very important making the addition of a new observer an obvious and necessary step so a biologist has a phone number or email to confirm the sighting.

I have my favorite. Which option do you think has the best combination of ease of use and future queryability? Do you have a better design pattern?

Color Considerations

2008-04-02T17:18:00.000-06:00

I love the odd places my job takes me. Today I am building a database to house all of the "marks" we place on animals we capture. Tags, Collars, Leg Bands, RFID Tags (they're in fish and your passport), Tattoos, Radio Collars, GPS Collars, Backpacks, Wing Tags.... the list is seemingly endless.

The fun thing about designing databases is figuring out ahead of time all the exceptions. For the moment at least, I now know more details about tagging animals than many field biologists.

So far we have the following attributes:

Tag Type	Collar, Band, Round, Square
Tag Location	Right Leg, Left Leg, Right Wing, Left Wing, Neck, Nose, Right Ear, Left Ear
Tag Construction	Plastic, Metal, Leather, Ink (tattoo)
Color	Red, Green, Yellow, Black, White
Mark	R-2345, 562626849057, TIGGER, Y-7892-23

(We'll leave radio frequencies out of our discussion and focus purely on the visual aspects)

The challenging part of this is deciding what to leave in, what to leave out.

I can probably discard Tag Construction and just allow color to cover it. But discarding color and moving everything over to tag type (e.g. Yellow Right Ear Tag, Pink Fluffy Collar) would result in an unusable dropdown-list-from-hell.

In designing databases, you search for the optimal combination of fields which allows the user to subset the information when necessary, but not fragment it so far that information cannot be located or easily entered.

For example, when looking for a Wolverine with a Yellow Ear Tag (but you don't know the Tag Number) one may query by Species, Tag Color and Tag Location and get a list of yellow-tagged wolverines in return.

This all sounds simple, but for this to work you can't have a free text field for color. If you do, and many of our historical spreadsheets and databases do just this, you get the following "Y" , "Yellow", "Yell", "Ylw" and "Yellew". So oddly enough, you need a pick list for color. And the same rules apply. Not too many or you'll end up with "Marigold", "Wheat", "Burnt Umber*" and "Mustard" and when searching for "Yellow" you won't find any wolverines. Add too few and the appropriate color may not be available (e.g. Pink).

We need a standard list of colors. Wikipedia assists:

In the 1969 study Basic Color Terms: Their Universality and Evolution, Brent Berlin and Paul Kay describe a pattern in naming "basic" colors (like "red" but not "red-orange" or "dark red" or "blood red", which are "shades" of red). All languages that have two "basic" color names distinguish dark/cool colors from bright/warm colors. The next colors to be distinguished are usually red and then blue or green. All languages with six "basic" colors include black, white, red, green, blue and yellow. The pattern holds up to a set of twelve: black, grey, white, pink, red, orange, yellow, green, blue, purple, brown, and azure (distinct from blue in Russian and Italian but not English).

From which we now have our final color list** (user additions will be possible of course, but strongly discouraged).

ColorID Color
-99     Unknown
1       Black
2       White
3       Red
4       Green
5       Blue
6       Yellow
7       Grey
8       Pink
9       Orange
10      Purple
11      Brown
12      Azure

Granted, graphic artists and Donnie Hoyle will tell me that we should probably have two lists, one for hue and one for saturation, but these are biologists we're talking about. I'll catch enough flack for "Azure".

More Reading:

* There actually is no "Burnt Umber" in a Crayola pack, although it is my favorite random color name. It is an amalgamation of two other Crayola colors, "Burnt Sienna" and "Raw Umber," both fine colors in their own right.
** The optimal solution would probably not use named colors at all, but instead a graphical color picker where one used an eyedropper to capture the color. Then searches would be performed using a fuzzy threshold slider (like photoshop's select by color) to select tags with a similiar hue and saturation. One problem with this approach however would be that it would lack color blind usability. And again, these are biologists, not graphic artists.

Color Considerations is cross-posted on my personal blog Stupid Dingo.

Auto Format (and Color) Outlook Appointments

2008-04-02T12:09:00.000-06:00

A few years ago I got turned on to the idea of indexing your life by color. In a quick glance your mind can comprehend really complex patterns. By coloring entries in my calendar I am able to tell immediately if I am available or if a future appointment conflicts with a work meeting.

There are a number of ways to set this up. Outlook allows you to add a label to every appointment. However this is an Outlook specific feature and I sync my calendar across Outlook, Yahoo! and iCalendar. The later two don't even have labels.

Besides, calendars should be simple. Complexity only hinders usability, so I prefer an automated solution.

How to color appointments in Outlook automatically:

In Calendar, right-click the calendar grid, and then click Automatic Formatting on the shortcut menu.
Click Add, and then type a name for the rule.
In the Label list, click a color.
Click Condition to specify the conditions under which the color will be applied.

Note: If you manually assign a color to an item, automatic coloring cannot be used on that item.

Step 4 opens the Condition Filter Dialog. There's a lot you can do.

Here's a simple example I use to highlight Meetings in my Calendar:

Ctrl-Shift-R

2008-04-01T23:59:00.000-06:00

No wonder Ctrl-F5 never seemed to work.

Force a browser reload. Internet Explorer Users should press Ctrl-F5. Firefox Users should press Ctrl-Shift-R.

Learning the Hard Way: Installing Drupal on XP

2008-03-28T19:45:00.000-06:00

Open Source on Closed Source. Probably a bad idea from the start.

Installing Drupal hearkened back to the old days of computing. Way too much jargon and far too little push-a-button-and-it-works. But I'm sold on the Drupal. If I wasn't, I wouldn't have made it through the past two hours trying to install it (I'm tempted to say her here, which suggests a certain love-hate relationship is already evolving).

Drupal installations should be straight forward. Lullbot manages the whole install in one five minute video that I would highly recommend. My installation took significantly longer.

LESSONS LEARNED
GOTCHA #1
MySQL - At least in my instance the directions for setting permissions given in INSTALL.mySQL.txt were not sufficient to allow the database creation scripts to run.

Command provided in INSTALL.mysql.txt:

  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE
TEMPORARY TABLES, LOCK TABLES
ON databasename.*
TO 'username'@'localhost' IDENTIFIED BY 'password';

Command that fixed it:

  GRANT ALL PRIVILEGES ON databasename.*
TO 'username'@'localhost' IDENTIFIED BY 'password';

Maybe I mistyped, but if I did, I mistyped several times as I repeated the directions more than once.

GOTCHA #2
This one was painful. I use Firefox almost exclusively, but in hoping to find a Windows-specific permissions error messages I switched over to IE in the previous step. Because when you create the first super-administrator account IE7's firewall blocks Drupal's reply with the default super-administrator password. As a result I was stuck on this screen for 90 minutes. I'd enter the information and screen would just postback blank.

I googled everything I could think of:

first user account nothing happens drupal
cannot get password for first account drupal
can't get password for administration account drupal 5
can't create first user drupal
show users mysql
http://localhost/drupal/index.php?q=user/register
cannot admin account drupal

NOTE: Included here in hope this may lead another lost soul to the light.

And finally I found the answer on the drupal Forums in one of tens of entries on roughly the same problem. It wasn't base URLs, it wasn't Apache settings (I'm on IIS)... it was Internet Explorer 7!

Internet Explorer blocking open source software. There's some irony in here somewhere I'm sure.

Printing Folder Contents

2008-03-19T13:54:00.000-06:00

Sometimes the old ways were a lot simpler. In DOS and UNIX printing a file directory was as simple as "DIR". And if you wanted to be really fancy you could add a couple characters to switch the listing format.

DIR command line switches:

/B Uses bare format (no heading information or summary)
/L Forces lowercase display of file names
/O List by files in sorted order:
- N by name (alphabetical)
- S by size (smallest first)
- E by extension (alphabetical)
- D by date/time (oldest first)
- G group directories first
- - using this prefix reverses the sort order
/P Pauses after each screenful of information
/S Lists files in the specified directory and all subdirectories
/W Uses wide list format
/X Displays 8.3 versions of long file names

And if you wanted to put this list in a text file to save, copy, paste or print it was as simple as:

dir /b /s > DirectoryListing.txt

Somewhere in the ease of use of folders and drag and drop what was simple became hard. In DOS and UNIX this was all easy. It can be again.

Follow these steps from Microsoft Support to add simple right-click "Print Directory Listing" functionality in Windows XP (for Vista see the support article). And stick around for how to customize these directions to print a directory listing to a text file.

Step 1: Create the Printdir.bat file
1. Click Start, click Run, type notepad, and then click OK.
2. Paste the following text into Notepad:

@echo off
dir %1 /-p /o:gn > "%temp%\Listing"
start /w notepad /p "%temp%\Listing"
del "%temp%\Listing"
exit

3. On the File menu, click Exit, and then click Yes to save the changes.
4. In the Save As dialog box, type the following text, and then click Save:

%windir%\Printdir.bat

Step 2: Create a new action for file folders
1. Click Start, click Control Panel, and then click Folder Options.
Or, click Start, point to Settings, click Control Panel, and then click Folder Options.
2. On the File Types tab, click File Folder.
3. Click Advanced, and then click New.
4. In the Action box, type Print Directory Listing.
5. In the Application used to perform action box, type printdir.bat.
6. Click OK.
7. Click OK two times, and then click Close.

Step 3: Edit the registry
After you follow the steps in the previous sections, Search Companion may start when you double-click a folder instead of the folder being opened. Or, if you have associated other actions with file folders, those actions may be performed instead.

To resolve this issue, follow these steps:
1. Start Registry Editor.
2. Locate the Default value under the following registry subkey:

HKEY_CLASSES_ROOT\Directory\shell

3. On the Edit menu, click Modify.
4. In the Value data box, type none.
5. Click OK.
6. Exit Registry Editor.

That's all well and good. But what if you actually like trees?

With minor edits to these directions we may save a directory listing to a text file in the parent directory.

Step 1: Create the Txtfiledir.bat file
1. Click Start, click Run, type notepad, and then click OK.
2. Paste the following text into Notepad:

@echo off
dir %1 /-p /o:gn > "DirectoryListing.txt"
exit

3. On the File menu, click Exit, and then click Yes to save the changes.
4. In the Save As dialog box, type the following text, and then click Save:

%windir%\Txtfiledir.bat

Step 2: Create a new action for file folders
1. Click Start, click Control Panel, and then click Folder Options.
Or, click Start, point to Settings, click Control Panel, and then click Folder Options.
2. On the File Types tab, click File Folder.
3. Click Advanced, and then click New.
4. In the Action box, type Write DirectoryListing.txt.
5. In the Application used to perform action box, type txtfiledir.bat.
6. Click OK.
7. Click OK two times, and then click Close.

Step 3: Edit the registry
After you follow the steps in the previous sections, Search Companion may start when you double-click a folder instead of the folder being opened. Or, if you have associated other actions with file folders, those actions may be performed instead.

To resolve this issue, follow these steps:
1. Start Registry Editor.
2. Locate the Default value under the following registry subkey:

HKEY_CLASSES_ROOT\Directory\shell

3. On the Edit menu, click Modify.
4. In the Value data box, type none.
5. Click OK.
6. Exit Registry Editor.

That will get you started, now revisit the command line and DIR switches and roll your own.

Maintaining Cross-Database Referential Integrity

2008-03-11T16:37:00.000-06:00

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS
SELECT SexID, Sex, Rank
FROM Master.dbo.PIC_Sex
ORDER BY Rank

Yes, that's right, Male and Female! You don't even want to go down that road.

Step 2:
We create a new user-defined function xdf_ValidateSex to validate if a given SexID exists in the view PIC_Sex.

CREATE FUNCTION [dbo].[xdf_ValidateSex] (
        @SexID int
)
RETURNS int AS
BEGIN
  RETURN
        (
        SELECT SexID FROM [dbo].[PIC_Sex] WHERE SexID = @SexID
        )
END

I've found this function really handy at some bars in the Village (see note above).

Step 3:
In our Observation Table we add a constraint on the field SexID which tests against the function xdf_ValidateSex to see if the SexID exists.

Enter the following Constraint Expression:

([dbo].[xdf_ValidateSex]([SexID]) is not null)

Viola! You can now validate Sex programmatically! Just add this constraint to your front door (or back door as the case may be) and no more embarrassing mornings waking up the spoonee instead of the spooner (or the other way around, I'll let you work out the validation details on your own).
Hint: Add a WHERE clause to the initial view to match your preferences.

SQL Illustrated

2008-02-11T11:33:00.000-07:00

The best illustration of SQL Joins I've stumbled across:
http://www.codinghorror.com/blog/archives/000976.html

Elaborate Hack for Transparency

2007-06-25T18:03:00.000-06:00

ArcGIS supports transparency*.
* Some restrictions apply.

But if you really want to do anything with transparency you quickly learn that the functionality available is completely inadequate.

I work with a number of layers that rather than resembling a stain glass window with neatly segmented regions, come closer to resembling the outcome of tossing said window down a flight of stairs. The smashed bits all laying in a pile overlapping each other sometimes seven or eight layers deep.

But how to represent this mess? In ArcGIS you really can't because ArcGIS only draws the top most layer. Take a yellow piece of stained glass and drop it on top of a blue one and in the overlap you don't get green, but blue. Even when you set transparency!

The only way to complete our Glad bag analogy is to make two independent layers with transparency applied. Not bad when there's only two. But what if there's 582? That would take a while. And what if the layer with 582 is constantly growing so we'd have to make new definition queries or static shapefiles every single time we appended a record. Not doable.

However while ArcGIS does not support transparency properly, it occurred to me that ArcIMS does.

With that idea, I present:

Hacking ArcGIS - Lesson 1: True Transparency

Step 1: Author a new map .AXL in which includes transparency (0.6 works well) and displays by unique value.

NOTE: I don't use Author at all in more, but it works great for building a unique values display.

Step 2: In ArcIMS Administrator create a new image service using the .AXL

Step 3: Add the new image service to your ArcGIS map.

Step 4: Use a copy of the layer with display set to transparent to label.

That's it! You can now print and export a map with true transparency.

Democracy Player

2007-06-22T14:26:00.000-06:00

I abhor Windows Media Player. I've been looking for a solid non-proprietary video player for some time. VLC is good, but could use some work on the user interface.

Today I downloaded Democracy Player, a free, open source, and built by a non-profit organization with the goal of keeping internet video from being dominated by any one source.

Here's there brief run down of why you should consider Democracy Player:

1. Can't play a video? Try us.
Play virtually any video-- Quicktime, WMV, MPEG, AVI, XVID, and more. Browse your collection, make playlists, stay organized.

2. Get Internet TV Shows
Subscribe to any video RSS feed, podcast, or video blog. Explore over 1,000 free channels with the built-in Channel Guide.

3. Search YouTube
Download and save videos from YouTube, Google Video, Yahoo Video, and other sites.

4. High Definition and Fullscreen
Your computer screen is a high-def display. Watch free HD videos in gorgeous fullscreen.

5. BitTorrent Animal
Easily download any BitTorrent file. Fast. Then watch it in the same app. Simple.

I know what you're saying, so what? Media Player, Quicktime, VLC, whatever it is your using works fine for you... but this is something different that I wasn't expecting. #2, turns Democracy Player into a TV by integrating smoothly with Video RSS feeds. You can browse channels, stop videos and return to them days later just where you left them. You can even build your own channels from other's content.

And it plays .wmv files, too.

NOTE: Apparently the next release will be called Miro

Could AJAX Replace Flash?

2007-06-21T12:15:00.000-06:00

In at least some applications I think the answer is yes.

The animation control included with the ASP.Net AJAX Control Toolkit is pretty impressive. Animate OnMouseOver, OnMouseOut, OnClick, OnDoubleClick and more. Transition effects, movement, visibility screening and the whole thing is controlled by an XML document.

Here's a 20 minute video (unfortunately only available in .wmv) on using the Animation Control packaged with the Toolkit.

I'm going to try using it in place of Flash in my next application.

AJAX without the work

2007-06-20T01:49:00.000-06:00

I've been experimenting this week with ASP.Net's new AJAX Control Toolkit. I've hard-coded some of my own JavaScript and AJAX... and by comparison this is painfully slick. It's too easy. I'm sure I'll discover some serious limitations, but at least for the moment I'm pretty blown away.

Take a second to browse all the controls that are included in the download, which being JavaScript will work in any application not just ASP.Net.

Saving Label Expressions

2007-06-19T11:35:00.000-06:00

Using the database querying label expressions I wrote about on Thursday, I got to learn a new "feature" of ArcGIS today: Disappearing label expressions on modification of a definition query

That's right, spend five minutes building a database query into your label expression, get it working perfectly and then to celebrate try limiting it with a definition query. Bam! No more label expression. Ain't ArcGIS beautiful?

In dealing with this "feature", I discovered ArcGIS has the ability to Save and Load label expressions.

I'm quickly developing a catalog of label expressions.

Ctrl -Alt -Delete in RDC and VM

2007-06-18T07:23:00.000-06:00

I'm always bringing up the Task Manager in my machine when I lock up a process on a Remote Desktop Connection(RDC) or Virtual Machine(VM).

Turns out all you have to do is right click on the Task Bar in the RDC or VM to bring up the Task Manager.

We'll file this in the "Who Knew?" section.

Copy Error Messages to Clipboard

2007-06-15T14:32:00.000-06:00

Don't you just love the error messages with just one prompt, OK, as if you are agreeable to it? ArcGIS is full of them. I'm especially fond of "Memory at XXXX08008878XXX cannot be found."

You know them well. But did you know not only can you copy and paste the error message into a picture editing software such as Office Picture Manager, but you can also use Ctrl-C to copy and paste the contents into Notepad, Word, Outlook or a textbox when updating your blog.

---------------------------
License
---------------------------
Provide your license server administrator with the following information:

Cannot find SERVER hostname in network database
The lookup for the hostname on the SERVER line in the
license file failed. This often happens when NIS or DNS
or the hosts file is incorrect. Workaround: Use IP-Address
(e.g., 123.456.789.123) instead of hostname
Feature: ARC/INFO
Hostname: Ifwis3b
License path: @Ifwis3b
FLEXlm error: -14,7. System Error: 11001 "WinSock: Host not found (HOST_NOT_FOUND)"

---------------------------
OK
---------------------------

Apparently, this has been available since Windows 2000!

Link

URL Rewriting

2007-06-14T19:50:00.000-06:00

Recently it came to my attention that google and other major search engines were no longer indexing querystrings. I'm not sure when the switch took place but on a number of sites I maintain the majority of the content is no longer indexed.

Enter URL Rewriting which allows us to convert:
http://foo.com/tools/product/mcleod

Into
http://foo.com/tools.aspx?product=mcleod

This is extremely commonplace in the Apache world, but how do we accomplish it within IIS?

Today I downloaded Helicon's ISAPI_Rewrite 2.11 Lite. It's free and best of all it works off regular expressions.

Installation and Configuration
1.) Download ISAPI_Rewrite
2.) Install
3.) Open the global httpd.ini file
Start > Programs > Helicon > ISAPI_Rewrite > httpd.ini
4.) Add a rule to process querystrings, in this example we'll loop through the querystring of all files ending in .aspx

[ISAPI_Rewrite]

# Repath .aspx files
RewriteRule (.*?\.aspx)(\?[^/]*)?/([^/]*)/([^/]*)(.*) $1(?2$2&:\?)$3=$4$5 [NS,I]

This will turn:
http://foo.com/tools/product/mcleod

Into:
http://foo.com/tools.aspx?product=mcleod

Extra points if you've actually used a McLeod (or at least know what one is).

The Lite version of ISAPI_Rewrite only works at the global level, and cannot be configured to work differently in each virtual directory. So far it is working really well, and I may be buying a full version in the near future.

Link

Coalesce

2007-06-13T14:04:00.001-06:00

Building on the vein of representing one-to-many relationships I've always been frustrated trying to display child records in tables and datagrids. Examples include showing multiple authors of publications, children in a classroom, and fish species observed in a lake.

Until today the two best methods I had were both hacks.
Method 1
Use Three Queries
1.) Build a crosstab
2.) Concatenate the output of the crosstab
3.) Join the product back to the parent table

- or -

Method 2
Loop through child records programmatically to build a comma-delimited list.

Today I discovered a far more elegant solution using COALESCE within a User Defined Function to build comma-delimited list in Views and Stored Procedures.

For this example we'll be building a comma-delimited list of fish species observed in each water (lakes and streams).

Step 1: Create a new User Defined Function(UDF)

CREATE FUNCTION dbo.udf_GetFishByWater(@WaterID int)
RETURNS VARCHAR(1000) AS

BEGIN
DECLARE @FishList varchar(1000)

SELECT @FishList = COALESCE(@FishList + ', ', '') + SpeciesName
FROM VU_FishSpeciesByWater
WHERE WaterID = @WaterID

RETURN @FishList
END

Step 2: Call the User Defined Function in a View

SELECT WaterName As Stream, Parent,
dbo.udf_GetFishByWater(WaterID) as FishSurveyed
FROM GIS_Hydrography

Result:

Stream	Parent	FishSurveyed
Abbott Gulch	S. Fk. Boise River	Redbank Trout, Sculpin (Var. Species)
Adair Creek	Jungle Creek	Cutthroat Trout, Westslope Cutthroat Trout
Adams Creek	Mann Creek	Rainbow / Redband Trout (Wild), Rainbow Trout
Agency Creek	Lemhi River	Bull Trout, Cutthroat Trout, Rainbow Trout, Sculpin (Var. Species)
Ahrs Gulch	Saint Joe River	Brook Trout, Cutthroat Trout
Airplane Lake	Ship Island Creek	Cutthroat Trout, Rainbow Trout
Alder Creek	Morgan Creek	Cutthroat Trout, Rainbow / Redband Trout (Wild), Rainbow X Cutthroat Trout
Alder Creek	Patterson Creek	Bull Trout, Cutthroat Trout, Rainbow / Redband Trout (Wild), Rainbow X Cutthroat Trout, Unknown Species
Alder Creek	S. Fk. Payette River	Dace (Var. Sp.), Rainbow Trout, Sucker (Var. Sp.)

...

Link

NOTE: These are now stored as Scalar-valued Functions in SQL Server 2008

ArcGIS One-to-Many Labeling

2007-06-12T12:58:00.000-06:00

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one.

We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships.

The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this?

I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine.

We're going to do a database query inside a label expression, loop through the results and output the entire list to label.

For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names.

1.) Open ArcGIS and add your spatial layer with the unique identifier shared with your database.
2.) In the label expression, click Advanced

3.) Replace the labeling expression with the following:

Function FindLabel ([ID])
Dim strQry, strInfo, i
i = 1
strQry = "SELECT Outfitter FROM VU_GIS_Labeling WHERE ID = " & [ID]
Dim Conn
set Conn = createobject("ADODB.Connection")
Dim rs
set rs = createObject("ADODB.Recordset")
Conn.Open "PROVIDER=SQLOLEDB;Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=aDatabaseName; Data Source=aServerName"
Conn.CursorLocation = 3
rs.Open strQry, Conn, 3, 1, 1
'if more than one records are found, append to the existing string.
Select Case rs.RecordCount
Case -1, 0
'If no record is found, return empty string
strInfo = ""
Case 1
'reading only the first record
strInfo = rs.Fields("Outfitter")
Case Else
Do While Not rs.eof
'if multiple records indicate how many using count
strInfo = strInfo & vbNewLine & rs.Fields("Outfitter") & " (" & i & ")"
i = i + 1
rs.movenext
Loop

End Select
'closing connections this is a must
rs.Close
Conn.Close
Set rs = Nothing
Set Conn = Nothing

'returning string for labeling
FindLabel = strInfo
End Function

You'll need to replace the bold values with those appropriate for your situation.

This example also uses SQL Server, different databases require different database connection strings:

Oracle
“PROVIDER=OraOLEDB.Oracle; Data Source=aDatabaseName; User ID=aUserName; Password=aPassword”

MySQL
“driver={MySQL ODBC 3.51 Driver}; Server=aServerName; Database=aDatabaseName; uid=aUserName; PWD=aPassword”

Microsoft Access
“PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=c:\myDatabase.mdb;”

Microsoft SQL Server (using Windows NT Integrated security)
“Provider=SQLOLEDB; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=aDatabaseName; DataSource=aServerName”

4.) Finally, test your label expression for typos using the Verify button and if successful, OK your way out.

The final product:

Link

When ArcIMS Site Restoration Fails

2007-06-11T10:08:00.001-06:00

Every once in a great while you'll open ArcIMS Administrator and be confronted with Site Restoration Pending, Would you like to restore?

At least in my experience, it rarely restores. You can click Yes and wait all morning and nothing happens. So what to do?

Shutdown ArcIMS Services in this order: Tasker, Monitor, Application
Browse to C:\Program Files\ArcGIS\ArcIMS\AppServer and delete ArcIMSSite.sez
Start ArcIMS Services in reverse order: Application, Monitor, Tasker
Open Administrator
Recreate your services from .axl files

TIP:

To speed the recreation process it is helpful to name your .axl files the same as the service names in your applications.
I'd also recommend storing your ArcIMS site settings in an external configuration file (such as web.config in asp.net) to better facilitate recovery and configuration/server changes on the fly.

Link

One Idea

2007-06-10T23:32:00.000-06:00

At our bi-annual training recently our boss challenged us to come up with one good idea every day.

Of course, this immediately became a joke around the office. By 8:05AM I'd be yelling out, "I've had my idea! I'm going home!"

But it got me thinking.

There is nothing about what I do that is static. Every single day I am challenged to learn a slew of new information just to keep up with the pace of technology. What if I documented those ideas? What would it look like after a month? A year?

oneidía - every day I will post one idea.

Or maybe, more accurately, What I Learned Today.

Most of the content will be based off solutions I find on other's websites and will be cited appropriately, but it may prove an interesting read for those of you interested in GIS, programming, the web and databases.

This is idea 1.