The views expressed herein may not reflect the views of my employer, the Joint Chiefs of Staff, or the Council of Elrond. Fnord.

Month: April 2015

I like free software. And I particularly love multi-platform software. Microsoft just scratched both of those itches.

They recently announced a new program editor called Code. Perhaps not the greatest of names, but it seems in my limited use with it to be quite capable. It supports RegEx, Git, and side by side editing, some definite pluses. I didn’t notice a column mode, which can be very useful for cleaning up data files. It might be there and I just haven’t found it yet. I also doesn’t seem to have a macro capability, and if it’s missing that, then it gets called a VERY basic editor. Still, time will tell, especially since it has a Request New Features link. Based on what I’ve seen in it, it’s no replacement for TextPad, but it’s not bad.

Tonight I’ll load it on my Macs at home and see what it looks like there as I’m not too keen on the program editor that I’ve been using under OS-X.

Small things make a difference. Keep in mind that I came from a command-line, pre-mouse, era. I loved working in command shells and not using mice and I kind of resent certain aspects of mice being forced upon me. But that’s the way things are these days and mice are pretty much unavoidable.

However, there are still things we can do at the command line to make things a little easier. If you don’t know about the keyboard shortcuts to do copy/cut/paste, I don’t know that I can help you.

So here’s one way to improve your life slightly:

Click Start

Click All Programs

Click Microsoft Office

Right Click on Access and select Properties.

There you’ll see a field called Shortcut Key. Make it active. Hold down the Control key and hit A. Click OK. The key combination Ctrl-Alt-A will now open Access. I use W for Word, X for Excel, and T for TextPad. When I worked in more of a support role, I used P for PowerPoint.

I do this on every Windows computer that I use regularly. It’s a small thing, but it’s another way that I can avoid taking my hands off the keyboard and stay away from the mouse.

Everybody has a favorite text editor. My fav was B.R.I.E.F., a DOS editor that I think I paid $300 for back in the ’80s, and it was well worth it. I love puns, and BRIEF was made by a software company called Under Ware. BRIEF was also my introduction to version control systems when a package called Sourcer’s Apprentice was released for it, that doubly-appealed to me as I worked for a game company called Flying Buffalo, and they published a magazine called Sorcerer’s Apprentice. SA was a very good VCS for its time, I used it a huge amount with FoxPro and DataFlex.

But VCS is not the topic du jour, though I am currently in the process of digging in to that: I found out that the current and previous versions of Access are compatible with VCS! So that’s going to be interesting to research.

BRIEF is still around, but I’m not really interested in using it anymore as the editors built in to Access and SSMS are quite good. But there are times, such as when I’m working on CMD or Perl files, that I need an external editor. For that I’ve pretty much settled on TextPad. It’s been around since 1992 and exists as both a free and paid version. It supports RegEx and you can also open files in a binary mode which I found very useful a few years back when I had a file downloaded from a vendor that wasn’t loading correctly in to SQL Server (turns out they were losing either the CR or the LF from the end of the line, making the entire file one massively long string) and the binary mode showed me the problem. Of course, getting a tech weenie at A Major Bank to understand that yes, both carriage returns and line feeds take space in a file was an interesting exercise in patience.

Yesterday and today I’ve been working on some moderately complicated reports in Access and have found TextPad to be very useful in getting done what I needed to do. Here’s a sample string of a total field adding up several columns:

=[SumPVLP]+[SumPVLS]+[SumPVLT]+[SumRDP]+[SumRDS]+[SumRDT]...

I needed to wrap each field in a NZ(), so I manually added the first part to the beginning of the string and the second part to the end, producing the following:

Since each line had 15 or 18 field names, and this was spread across three reports, it saved me a heck of a lot of work in the end. It’s a tool that I do not want to do without. Yes, I could have used the Access VBA editor, but I like NotePad and my brain wasn’t in VBA mode at that time: any editor could have done this. I have used other editors such as Notepad++ (my wife’s an Emacs freak), but I keep going back to TextPad.

I also use it to strip out a lot of lines from table scripts produced by SSMS using RegEx, it eliminates a lot of dross that I can do without.

I think the point of this post is to encourage people to work smarter, not harder. I could have done all the changes in Access in a zoomed box, but I like minimizing the amount of typing that I do if I can (you wouldn’t think it as verbose as I am!). Doing it in TextPad was pretty simple and using it helps to refresh my memory of its specific key commands and quirks.

Wow! I didn’t realize that I had missed a week posting. I wasn’t intending to post daily, still…. My excuse is that I had a board meeting in Albuquerque last week that forced me in to a last minute review to make sure my system was working properly and loaded on to my laptop correctly, plus travel. And even with that prep, I still had a bug creep in. Still, my presentation and demo went quite well and the bug was somewhat minor.

ANYWAY, today I have a code snippet that I just wrote. I love working with base tables in SQL Server, and needed all of the field names of my largest (field count-wise) table, some 50 fields. So I wrote this code to give me all of the field names so that I would’t be doing a SELECT * in a view.

If you want a table prefix to appear in front of each field name, change the @tblpre variable. Run this code with the output window set to Text, copy and paste, delete the row of dashes, and you should be good.

--GetTableFieldNames.sql, WW, 04/28/2015
--returns all field names with some exclusions:
--makes it easier to add a long list of field names
--to a view to eliminate SELECT *
declare @tblpre char(3) = ''
set nocount on
select ', ' + rtrim(@tblpre) + sc.name
from sys.columns sc
join sys.tables st
on sc.object_id = st.object_id
and st.name = '[TABLE NAME TO GET FIELDS FOR]'
where sc.is_identity = 0
and sc.is_computed = 0
and system_type_id <> 189 --exclude timestamps
set nocount off

The database that I’m developing in Microsoft Access and SQL Server is a student tracking system, and not surprisingly, the main table is called Students. After much consideration, I decided not to use the student ID as the primary key of the table, instead I’m using an integer identity(1,1). The main reason is that the number might change. Normally, when a student is assigned an ID by the State Department of Education, that number will never change. But if they’re enrolled in a private school, of which there are two or three types, they will have a number assigned by that organization, which could theoretically collide with a number assigned by the State. My solution was to add a char(1) field that discriminates between the different types of organizations that can assign an ID, and I have an index on that combination of ID# and Org.

The main reason why I didn’t want this number to change was that I didn’t want to cascade changes to two child tables, plus, I couldn’t cluster on it because there’s no telling what order they’ll be entered in, which would cause endless page splits and I/O hits.

Access has a .Dirty event to indicate changed data, but it only operates at the record level, not at the field level. (Or row and column level, if you want to be pedantic about it) But Access does save the previous value of a changed field in .OldValue, so we can recover from an accidental field change without cancelling all edits on a record!

I don’t want the ID# to be accidentally changed, so I developed the following code tied to the LostFocus event that pops up a message box warning the user and forcing them to click Yes to change the number.

Private Sub StudentID_LostFocus()
'can't directly check for Dirty, must compare to OldValue
With StudentID
If .Value <> .OldValue Then
retval = MsgBox("You have changed the "_
& "Student ID. Did you mean to do this? " _
& "Selecting No will reset it to the " _
& "original value.", vbYesNo)
If retval = vbNo Then .Value = .OldValue
End If
End With
End Sub

Yes, I was supposed to write about how I do DBCCs without doing maintenance plans, I’ve had to push that a bit.

It was announced as available on 15 April, according to the SQL Release Services blog. The only problem is that the link goes to an error page. *sigh* It’s going to be a while before my project goes to internal alpha release, much less full release, I’m sure they’ll get it fixed soon.

Most DBAs of any vendor’s system know how critical it is to back up your systems. User databases, system databases, etc. But do you ever restore them? That’s the only way to know if your backup is good. Of course, this represents a lot of difficulty if you’re managing a multi-terabyte system. I personally don’t have any experience with such, the largest that I’ve worked with was on the order of 150 gig.

Recently there was a post on SQL Server Central by a poor and most unfortunate soul who discovered that his system had found corruption on a production database. He had no backups, and apparently did not run DBCCs, and the error was one of those where your only options are to either restore from backup or to repair allowing dataloss. And without having a working backup, well…. Not a good position to be in, having to tell your bosses that when you get the database running again that you might lose information and have inconsistent data. Not good at all.

The sad part is that it’s not difficult to prevent. In SSMS expand the Management tree (I’m using 2014, but it’s been around for a long time), right click on Maintenance Plans, click on Maintenance Plan Wizard, and you’re on your way to getting automatic database consistency checks (DBCCs). AT THE LEAST you want to check Check Database Integrity, Update Statistics, Back Up Database (Full), and Back Up Database (Transaction Log). Clicking on Next lets you change the order that the tasks are run. Clicking on Next lets you select what databases this process will be run against. THIS IS IMPORTANT: you should probably check All Databases. System databases need love, too, particularly Master and MSDB. Master contains the metadata that defines your installation, MSDB contains your jobs and maintenance plans, along with information on backups, log shipping, sysmail, syspolicy, SSIS, etc. Model is important and should be checked, but it can be recreated from another installation, though you’ll lose customizations that you’ve done to it, if any. But it isn’t as likely to get corrupted since it is not normally transactionally active.

(you don’t have to worry about TempDB: it’s dropped and recreated every time SQL Server starts)

After you’ve selected the databases, Next will let you select where the report on all this activity goes, likewise you can have it mailed if you have the Database Mail services configured and running on the server.

And now you’re done. You’ve just taken some important steps towards protecting your data. But you’ve only taken first steps: you still have to review the reports and verify that the backups can be restored! If you don’t review the reports, how will you know your databases are clean and working properly? If you don’t restore your backups, how will you know if you can if you have a serious server crash or corruption issue?

Here’s the kicker: I don’t do maintenance plans, particularly for DBCCs. I’ll show you my personal method tomorrow.