Skip to main content


Showing posts from 2009

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

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 …

Clearing SQL Server Log Files (ldf)

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 queryEnter the following:
-- Set the database to manipulate Use YOURDB; go -- Truncate the log by changing the -- d…

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

If you have root access:
Install required libmotif3 package from the Ubuntu repositoriesDownload the en.linuxx86.tar.gz file from Citrix websiteExtract the contents of the archiveOpen 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 AgreementAgree to remaining defaults (y) and press 3 to quit installerRunning 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…

Repairing Broken Data Sources

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 PropertiesIn the Properties dialog box choose the Source tabClick Set Data SourceBrowse to the location of the data layer, select and click AddOK 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 ArcCatalogRight-click and select Set Data Source(s)Highlight one of the Data Layers that is in error and click Replace.…

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

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

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 NotepadSource:

The Case IsNull Now

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:

Would be used to create the View:

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

This post is part of an ongoing seriesDesign for ExceptionsSpreading the Sheets and Letting Relations InForming a New RealityThere'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 ProjectHighlight the table for which you want to create a form (let's start with Cities)Click the Create Tab, Select FormPress 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 subs…

Spreading the Sheets and Letting Relations In

This post is part of an ongoing seriesDesign for ExceptionsSpreading the Sheets and Letting Relations InForming a New RealityThere'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 t…

Design for Exceptions

This post is part of an ongoing seriesDesign for ExceptionsSpreading the Sheets and Letting Relations InForming a New RealityThere'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:
CityZipCodeEagle83616Kuna83634Idaho City83631
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&…

An Inquiry into IN Query

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:
NOTE: Coverages, shapefiles, and other non-geodatabase file-based data sources do not support subqueries. Subqueries done on a versione…

Making Macro Security Warnings Disappear

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 ToolsSelect 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 EditorNavigate to the Macro that is causing you WarningsGo to Tools > Digital Signature and click ChooseHighlight 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

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 EditorUnder the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSessionDouble-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 Visua…

HowTo Remove Annoying Advertisements and Take Back Your Browser

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
// @description Removes ads from Yahoo Mail (AJAX)
// @include http://**
// ==/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'…

Saving changes not permited in SQL 2008

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:

Restoring Orphaned SQL Server 2008 Users

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 Us…

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

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

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 …