Search This Blog

Posts

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.

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…

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.0Accept 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…

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 dialogIf 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.…

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.

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: http://www.winhelponline.com/blog/print-search-results-in-windows-vista/

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…

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 City83631That 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.

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

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 ApplicationThis time when you get the security warning choose, "Always Trust this Publisher"No more messages!

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.

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…

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 MasterFirst, 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.