Hey! I have a blog!

I had very good intentions when starting this blog - it was going to be updated regularly.  Ah well.  Looks like I haven't really done a review of any kind since 2012.  So here's a quick review of 2013:

Did project work for 6 different clients, and minor maintenance work for 3 others.

The project work was broken down into the following industries:

• Financial - 22%
• Mining - 14%
• Engineering - 27%
• Pulp/Paper - 27%
• Forestry - 6%
• Airport - 4%

It was a moderately busy year - not quite as busy as the previous year, but not too bad.

This year is shaping up to be a good one if the first two months are any indication.  I've done about as much work in the last two months as I normally do in three.  If I were able I'd probably take March off, but the task list beckons...

SQL Update Trigger Error

I was creating a trigger in SQL Server today and encountered an odd error.  The trigger was in a purchase order table and I wanted it to update a value in each row of the purchase order line table for the po when a vendor date was changed.

The error I was getting was:

No row was updated.
The data in row 1 was not committed.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).

Much Google searching later...

To fix, add the following line above your UPDATE statment

SET NOCOUNT ON

That fixed the problem for me.

September (and summer, and fiscal year) Review

The last three months were a little lighter than usual, but I did work on a few things:

• Access database that converts sales data to resource requirements by employee position
• Database maintenance utility to purge old data from a program that's been running a number of years
• Maximo related program that automatically emails vendors to remind them of outstanding purchase orders.
• Client ranking function from an EMS system to help determine which clients are the most profitable.
• Maximo related program to generate RFQ spreadsheets to email to clients and then upload the returned data from the clients.
• SQL function to return written off totals for the year in an EMS database.
• Plus a number of minor requests and edits to existing programs.

My fiscal year ended yesterday.  2011/2012 was a good year.  Got a new client at the beginning of it and it looks like they are going to be a long term one.

Restoring SQL Server Database with Change Data Capture

Just upgraded my SQL Server to version 2012.  I needed to because a client had upgraded and some features were making it difficult to restore their database to my computer.  Still, I got an annoying error when trying to restore the database via the wizard:

"System.Data.SqlClient.SqlError: Could not update the metadata that indicates database DBNAME is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] ' drop". The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request. (Microsoft.SqlServer.SmoExtended)"

I love cryptic error messages.

Anyway, after a bit of research I was able to restore the database by using the following line:

restoredatabase DBNAME FROM DISK = 'c:\data\BackupFile.bak' with keep_cdc 

The important bit was the 'with keep_cdc' option.

June Review

June was a bit slower, as is often the case just before summer.  There was one small SSRS project and a bunch of changes and enhancements to existing programs.

Going to attempt to keep July and August light on the work, as my oldest son is home from school.  Also, I'm turning 40 this month, so as reward for making it to the life half-way mark intact, I'm going to try to take as much time off as possible and enjoy the summer.

September will be the awe-inspiring time of both my kids in school (My youngest turned 5 this year).  Looking forward to it.  Hopefully I'll be able to find myself some largish projects.

May Review

May involved a lot of Maximo work.  I wrote a program to automatically email purchase orders that were generated in the Maximo re-order process.  The program is in Access and it generates a PO report that is printed as a PDF.  It automatically attaches the PDF file to an Outlook email message and then puts it into the user's outbox.  Got a lot of complements on this one - the main user said they didn't realize how much time they were spending manually on this task until they didn't have to do it anymore.  Aside from that, did some revisions on some of the custom java classes and minor changes to other items I had done previously.

The rest of my time in May was spent writing some stored procedures and SSRS reports for one client, and revisions to Access programs for a couple of other clients.

The fiscal year of 2011/2012 is turning out to be a good one.  So far I've billed more than last fiscal year and I've still got 4 months to go.

Spring Review

Well, it appears regularly contributing to a blog is more difficult than originally thought, especially when things are busy.  Here's a quick re-cap of what I've been working on these last few months.

• Wrote a program to track a company's inventory of forestry certification credits.
• Wrote some SQL Server stored procedures to take a snapshot of data in an EMS system.  Also created some SSRS reports to view the data and compare the historic data with live data.
• Added a bunch of changes to a Ministry of Forests database I had written a few years back.
• Wrote a function to upload data from a spreadsheet to a Great Plains Dynamics SQL Server database.