oneidía

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

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

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

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

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality 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 t...

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality 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 zipc...

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality 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 t...

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

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

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

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

How do you search for * and ? (asterisks and question marks) in MS Access? Bracket the criteria: SELECT * FROM Table WHERE Column NOT LIKE "*[*]*"

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

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

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

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

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! Slugs are awesome . Step 2: We create a new user...

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

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