Coding myself into a corner

coding myself into a cornerI spend an awful lot of my working life adding new features and improvements to a legacy database system.

A legacy system is an old method, technology, computer system, or application program, “of, relating to, or being a previous or outdated computer system.” Often a pejorative term, referencing a system as “legacy” often implies that the system is out of date or in need of replacement.

My particular legacy system is Microsoft Access. We use Microsoft Access to provide the front end for all our internal databases. The particular technology we use is ADP files which are only supported in Access 2000, 2003 or 2010.

Microsoft has already dropped support for Access 2000 and 2003, and support for 2010 is due to be dropped in 2020.

So why am I coding myself into a corner? Its simple for every feature or improvement I create in Access, I increase the amount of features I need to create or port to a new front end.

This is a very depressing thought, everything I create will need creating again. I am not saving myself work but increasing the amount of work I need to do again.

The bad news is that my employers like almost all businesses demand results and like a good employee I have been delivering them. I have been promised the mythical “when we are quiet” you can work on rebuilding the database front end. I know this will likely never happen so what are my options.

Do nothing I have warned my managers that this work needs doing and it is up to them to give me the resources I need. This is not an acceptable option. Firstly I am not future proofing the business, 2020 will be here before we know it. Also I am not developing myself as a developer, as the only experience I am getting is with Legacy technology that has expired or will do very soon. No one wants to employ someone who only has legacy experience.

Do something I need to keep delivering results and doing what is asked of me. Like any good engineer I should be multiplying my estimates by four, meaning that I have some time that can be used for looking at the bigger picture. This is a win-win option really. Employer gets a solution that is future proof, Employee gets valuable experience in up to date technology.

What do you think? Have you been tied to legacy technology? Why not leave a comment below.

SQL Transaction Log Backups

Like many DBAs I spend a lot of time maintaining my SQL Server backups.

From SQL Server I maintain both full backups and transaction log backups. I have often restored my full backups but until recently I have never restored a transaction log backup. All backup strategy’s are only as good as the last time you tested the restore process.

So what is a transaction log backup?

A transaction log backup contains all the transaction log records generated since the last full backup and is used to allow the database to be recovered to a specific point in time (usually the time right before a disaster strikes).  Since these are incremental, if you want to restore the database to a particular point in time, you need to have all the transaction log records necessary to replay database changes up to that point in time.

How to do the restore.

First right click on Databases in SQL Management Studio and select restore database. You should then get a screen similar to this.

restore1

In source click the … to allow you to select your backup files.

Now normally I have only ever selected one file here, the *.bak file. Instead select the *.bak and all the *.trn files as well. After SQL Server has chugged for a few minutes (time will depend on number of transaction files and server/disk speed etc) the restore plan section should fill up with files.

In the destination database box, type in the name of the database you want to restore. I recommend using a different name to avoid overwriting the original database, appending Test or a datetime to the name is what I usually do.

On my test server I need to untick the take tail-log backups option off the options screen before I can execute the restore.

Now you can either check the tick boxes in the restore plan section or (more fun) click the timeline button to select at what point in time you want to restore to.

restore2

You can either select the point in time with your mouse or specify the exact point in the time textbox. Alternatively you can just select the most recent point, probably the most likely option when disaster strikes.

Now that I have tried doing this on my test server I feel much more confident that when disaster does strike I can get things restored quickly and painlessly.

How often should you run transaction backups?

The answer to this question depends on how critical your data is. Until very recently I ran mine ever 15 minutes, I have increased this to every 5 minutes, but I have seen recommendations of running it every minute. The more critical your data the more often you should run them.

SQL Server Management Studio

I use SQL Server Management Studio all the time for writing queries, getting data and general SQL development.

SQL Server Management Studio

I have enjoyed seeing the improvements that each new version of SQL Server Management Studio (SSMS) introduced. One great improvement was intellisense.

This feature saves typing and reduces errors by automatically suggesting tables, column names or other database objects.

A common query that I get asked to write is provide a spreadsheet that gives the information that satisfies certain criteria. This is easy to do in SSMS, you can write the query, click execute and the rows that satisfy the criteria are displayed. These rows can then be easily copy/pasted into Excel or other spreadsheets.

A common data item that gets stored in databases is addresses and addresses often contain line breaks to make the data display better. In the earlier versions of SSMS, when you copied and pasted these line breaks were ignored and the data displayed the same in SSMS as it did in Excel. However in the more recent version, theses line breaks got copied across breaking your spreadsheet and making it hard to see what data corresponded with what.

Now I don’t know if this should be described as introducing a bug or fixing one. I can easily argue both sides. If your data contains a line break and you copy this data it should include the line break in the destination, but if it does that it displays badly in Excel.

The fix I have been using until recently is to use the following TSQL command in my queries.

SELECT REPLACE(REPLACE(@str, CHAR(13), ”), CHAR(10), ”)

This command replaces any line breaks with an empty string. Both Char(10) and Char(13) are needed because you can have different types of line breaks. This is great if you are writing the script from scratch but isn’t great if your are running a stored procedure or your query has a lot of columns.

The answer to this is to use Visual Studio to run your SQL query. In Visual Studio you can write and run queries via Server Explorer and the results produced don’t contain line breaks. I have only just discovered this solution, but so far it has worked and is very easy to do, plus as I do most of my development in Visual Studio anyway it saves me having to open SSMS to test my queries.

My Love/Hate Relationship with Microsoft Access

For historical reasons I use Microsoft Access as the front-end for all my line of business databases.

Access allows you to create an ADP file that can connect to your SQL Server database. However Office has dropped support for this, so the last version of office that I can use is Office 2010.

Before I start hating on Access there are some good things about the platform. Users with very little knowledge can create a database fairly quickly. Development time is also fairly low as it doesn’t take long to create a new form, and add a few buttons which open different reports.

For the last 5 years or so I have built a range of different forms in Access, (and even if I do say so myself), I have got quite good at creating forms that users can be more productive with. Access has the concept of a subform which is a form embedded in a form, each form can be connected to a different query and can be connected to each other so it is relatively easy to create something quite powerful.

Access report are an easy way to create professional looking reports that can be sent out to clients, or used by management and they also have the concept of subreports, (same as subforms but embedded in a report), this means you don’t have to write one query to pull back every piece of data that shows up in a report.

VBA or Visual Basic for Access allows you easily do things programmatically, like run something if something is selected or a button is clicked. This is not the easiest programming language to work with, however once you have used it for a while you get used to its quirks. My favourite function is IIF, the syntax is fairly simple IIF(expr, truepart, falsepart), if expr evaluates to true the truepart is executed (executed can be as simple as displaying certain text in certain conditions), if it is false the falsepart is executed, and you can also chain them together which I have done to produce something very complex looking.

Now for the negative side of things. Access creates a binary ADP file, binary files can not be version controlled in git or subversion. This is a big problem as I want to keep track of every change I make. It also means that branching or working with others on big projects is also difficult.

One work around I have found to this problem is AccessSVN. This program takes an ADP and creates text files of every form and report. It also can create an ADP from these files as well, however I have had varying success with this feature so I prefer to just create the text files if possible. What I do after I have made a change to the ADP is to run AccessSVN, create text files and commit these to source control. You can then see what controls have been changed or added. It also helps with bugs as I have found that sometimes while testing access will apply a filter to a form, which will break things if deployed, which is very annoying.

Access can only do what access was designed to do. There are some advanced features that we would like to add to our databases. Things like sending emails or uploading reports to a website. This is not Access fault as it was never designed to be able to do such things but I am often asked about what is possible.

End of support for Office 2010 is approaching and I have plans to stop using Access but until then I need to keep supporting our existing front ends.

Requirement Gathering

Why is it so hard to find out exactly what is needed when designing changes to a system?

Requirements-Gathering-User-Experience-UX-Project-CartoonWhen adding functionality to databases I always like to dive in and start adding extra columns and new tables. But at some point you need to find out what is needed.

The process of gathering requirements that I have followed goes something like this.

My boss tells me that we need a system to solve x problem. This is the broad overview of what is needed.

Next I need to find out specifically how the system is being used and what things need adding. Discussing the specifics with my boss often results in a high level of confusion. I always try to speak with the users that will be using this system on a daily basis. If I create something that isn’t liked people will avoid using it and it won’t solve the original problem so I always think it is very important to speak to the day to day user.

After I have an idea of what is needed, I build something. Once I have something that sort of works, I will try and demonstrate this to the user to gather feedback. This feedback is invaluable as it will often reveal if I am going in the right direction and reveal missing requirements that need to be incorporated in the finished solution.

I will often repeat the last stage a few times especially if there is a big change needed, once I am happy I will demonstrate to my boss before deploying the solution.

After deployment there is often a period of fixing issues and gathering feedback before I can consider the project finished.

This whole process is very time consuming as there is a period of changes being tweaked back and forth following feedback. A better way is to gather a detailed specification of what is needed and work towards delivering that.

Overflow

Today I encountered a new error.

error

Run-time error ‘6’: Overflow doesn’t really tell me much. The error was occurring in the Access ADP front-end of our main database. It was only occurring for one particular Id number which was really confusing me.

First thing I tried was to remove the most recent changes I had made, this made no difference. So time to look at the VBA code that was run just before the error.

Dim iPropertyId As Integer
iPropertyId = DLookup(“PropertyId”, “Survey”, “[ProjectNo]=” & Me.ProjectNo)

All looks good, even running the Dlookup query in SQL Management Studio threw no errors.

SELECT PropertyId FROM dbo.Survey WHERE ProjectNo =

Next thing I tried was explicitly setting iPropertyId to the problematic Id, still the same error.

This made me look at the error message again. Overflow suggest something being too big, but what could it be.

Integer has a size limit of 32,767 and the value I was trying to pass into iProperty was 32779. This was what was causing the problem, my database had grown to such a size that Integer was too small, I needed to change it to a Long Integer. Long has a size of 2,147,483,647 so my database should keep working for a while more.

The famous programming help website Stack Overflow is named after such an error. This error shows how difficult it is to decide what variable type to use, as you don’t know how quickly your database will grow.