User Defined Data Type Definition Script

Recently I worked on a project where every data type was user defined. In case you weren’t aware, SQL Server supports the use of User Defined Data Types. These are custom data types which are based on the standard types. For example, if your data always uses a two character string for states, you may […]

Database Views - Using PowerPivot with Problem Databases Part 2

In the previous post I mentioned the use of views to simplify querying your database in general and PowerPivot in particular.

What are Database Views?

Views are virtual tables, which you can create with a SQL statement. Some of the advantages of using views are:

Simplified Data Access – The view hides the data structure from […]

Updating a SQL Table From Another Table (Fixing Your Salesperson Admin Mistake)

The Problem

Yesterday I demonstrated how to use T-sql to update the Customer Table (SLCDPM) to avoid hours of manual labor. I mentioned that you should always make a backup of the table before running an update statement in case you’ve made an error.

One of my readers, Kim, astutely asked me how to use […]

Using T-SQL to Automatically Update M2M Tables

One of the primary values a good DBA brings a company is in automating tedious, time-consuming tasks. In my last article, I discussed the problems when adding a sales person to M2M. M2M’s standard procedure is to create a new sales person and manually re-assign every customer (over 700 of them) to the new sales […]

What's in a Name?

In the past two weeks, I’ve had two separate people ask me the same question. They want to know how to decrypt the Utuser.dbf file so they can retrieve their user list from M2M.

While I won’t show you how to break M2M encryption, I will give you two simple ways to get your user […]

Removing Null Country Values From the Syaddr Table

Unfortunately M2M does not automatically populate the Country field on many screens when a US State is selected. This can cause problems in reporting when an executive wants sales broken down by country and many of them are grouped under Null.

Anyway, if you want to see how many addresses are missing “United States” you […]

Let’s Play the Orphaned Records Game

In the past couple of weeks, I have discussed the problems of orphaned records in the M2M Database and promised to provide a script to find them. So, I’ve decided to turn it into a game.

If you’d like to play, paste the following script into your Query Analyzer or SQL Server Management Studio and […]

Orphaned Records and Technical Hypochondria

I found more orphaned records recently when querying a M2M table with an order by clause. Once again, this occurs because in my opinion the M2M database is not properly designed. Here’s a quick walk-through on how I found it. Incidentally, the screen shots are from TOAD using M2M’s practice database.

A manager tasked me with […]

Using User Defined Functions to Suppress "Blank" Dates

As long as I have been working with the M2M Database I had always wondered one thing. Why on earth does M2M use 1/1/1900 dates? It’s fairly obvious that it was an artificial date when one wasn’t applicable, but why? Why not just allow your date fields to be null and be done with it?

For […]

Solution to Encyclopedia Brown and the Case of the Data Corruption

This is a continuation from this previous post.

“It’s simple,” Encyclopedia said. “First I ran the following query against our database.”

SELECT SOM.fshipvia
FROM somast SOM
WHERE SOM.fsono = ‘123456’

“The value returned was UGB, which is the actual value for UPS Ground Bill in our database. So, I knew that the Ship Via […]