Focusing on setup and development using SQL Server products from 2000 to 2008 and beyond. Also about anything geeky that compels me to write a blog entry.

Wednesday, November 25, 2009

Building My First Website Using Microsoft Expressions

In years past, I have used MS Front Page to develop several websites, both personal and as a consultant. In the last few years I have only built ASP.NET web apps until this last weekend. I dipped my toe back into the website design pool using a tool that was new to me, Microsoft Expressions.

Expressions has many similarities to Front Page, but it goes further in mixing the graphical design tools with a light layer of the .NET controls to extend your site's functionality. In my case I just needed to build a basic website without any data interaction, at least at this point. I found the templates, menus, and toolbars to be well laid out and fairly intuitive.

I was able to get up and running and finish the first draft of a full website in around ten hours never having seen the interface ahead of time. To me that is the best indication of a well designed product.

The dynamic web templates allowed me to easily manage the top banner, navigation menu, and side bar separately from the page content on each page throughout the site. I did discover some hangups with the template approach. After developing eight or nine pages, I realized I needed to add a new editable zone to the template. I quickly discovered that this is a major issue once you have already applied your template to one or more web page.

Overall I give Expressions Web a big thumbs up as an excellent web development tool, and I look forward to using more of the functionality over time.

Saturday, November 14, 2009

Conditionally Blocking Scheduled Reporting

As is true for many DBAs, I have developed a custom set of SSIS packages along with a database that manages scheduled Reporting Services reports. It delivers to a mixture of email recipients and FTP locations after generating the output PDFs and Excel results.

Then came the inevitable weekend of trouble. The set of SQL Server 2005 databases, which provide the data for the reports, failed to load due to trouble with the source legacy systems. However, since the data in the SQL Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.

Therein lay the problem. I did not want any reports to generate, or worse yet email to recipients, if the recent day of data was not included.

The solution is fairly simple. The SSIS packages that load the SQL Server databases were logging to a table, and included a special status column. When each database was finished loading and had completed any subsequent updates required, a log entry with a status of DONE and UPDATED were entered. I just created a stored procedure to scan the log for databases that were scheduled to be loaded, filtered by UPDATED, and with a log time that fit into the correct date/time range for the current day's data load.

If the stored procedure found any records that did not meet the above criteria, it returned a result reporting accordingly. Then I just added a call to this stored procedure in my primary scheduled reporting stored procedure, where the list of scheduled reports to process is generated. If the data load check reported problems, I prevent the scheduled reports from processing. Then once the problem with the data load is resolved and all data is available, all I have to do it run the SQL Agent job that runs the scheduled reports and this time it will succeed.

I'm always a fan of a little more work in the back end to make a little less work for the routine. Not to mention, you don't have to answer Fred's email asking, "Why did I get two emails of the same report, and which one is right?"