EXIF Extraction

So, like any good programmer, I have the camel on my bookshelf where the allmighty Larry asserts (p. 609) that laziness is the first great virtue of a programmer.  Not to be a virtue-less programmer, I was off the teh googlez to search for an EXIF library.

I figured that it's bound to have come up before and lo and behold I found many a library.  Most did pretty much what I wanted, but this is for a web app and every single library left a lock on the file after it was done executing.  I guess I could have restarted the app pool after every page load, but that seemed a bit excessive.

I poked around MSDN and saw that it wasn't that difficult to read it by myself.

So, I wrote my own chunk of code:


b = new System.Drawing.Bitmap(fileInfo.FullName)
  Dim encoding As New System.Text.ASCIIEncoding()
  for each pi in b.PropertyItems
    if pi.ID = 40091 then ' I only need the title
      description = System.Text.Encoding.Unicode.GetString(pi.value)
      exit for
    end if
  Next


and viola, EXIF data.

Sidebar here:  I am a c# guy.  I cannot stand using vb.  but, DotNetNuke is written in vb so, I don't have much of a choice (I'm not rolling a new class for 15 lines of code)

But, with the EXIF data came the aforementioned file lock.

Back to MSDN to poke around at the Image Class.  Skip down a bit and lookie lookie it implements IDisposible.  Oh boy, is it really this easy?

yes, in fact it is.


using b = new System.Drawing.Bitmap(fileInfo.FullName)
  Dim encoding As New System.Text.ASCIIEncoding()
  for each pi in b.PropertyItems
    if pi.ID = 40091 then
      description = System.Text.Encoding.Unicode.GetString(pi.value)
      exit for
    end if
  Next
End Using

Game, set, match.

Backup Script (2.0)

So, as promised, I am posting my new backupscript.  I've completely reworked it.  You know call it with a specific database, backup type (log, full, or differential), and a backup path.

Here's the basic logic:

If we try to backup tempdb, bail (You can't back up tempdb)

If you try to backup master with differential or log, bail

If you try to take a log backup of a non full recovery db, bail
  (I dont have any bulk logged DBs, but I probably should change this)


Get the DB Guid (This was for databases that I restored with the same name, but were different databases altogether.  I using the Database Name to query the history.  the GUID is much more robust.

Force the BackupPathRoot to have a trailing \  - So the path works properly.

Get the last full backup information

If there was never a full backup, switch the backup type to full

If the last full is not in the backup path root, or is missing, switch to full (I'm thinking about changing this to only happen if we're taking a differential)

If we're taking a log backup, Check the log chain (just need to check from the last full or the last differential)

In the backup path root, make a subfolder for the database if it doesn't exist.

Backup the database with the following format:  %BackupPathRoot%\%ServerName%\%DatabaseName%\%DatabaseName% YYYYMMDD HHMMSS %BackupType%.bak

so, there ya go...

here's the link : BackupScript

Here's the source:


USE AdministrationIF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='BackupDatabase')
  DROP PROC BackupDatabase
GOCREATE PROC BackupDatabase
  @database SYSNAME,
  @backuptype VARCHAR(20),
  @backupPathRoot VARCHAR(255)  WITH ENCRYPTION
AS/*
--Test Data
declare
  @database sysname,
  @backuptype varchar(20),
  @backupPathRoot varchar(255)

set @database = 'robtest'
set @backuptype = 'log'
set @backupPathRoot='C:\Backup'
--*/SET NOCOUNT ON
DECLARE
  @databaseGuid UNIQUEIDENTIFIER,
  @debugmessages INT,
  @DifferentialBackupLocation NVARCHAR(260),
  @DifferentialBackupSetId INT,
  @FileExists INT,
  @filename VARCHAR(255),
  @folder VARCHAR(255),
  @FullBackupLocation NVARCHAR(260),
  @FullBackupSetId INT,
  @now DATETIME,
  @LogMessage VARCHAR(MAX),
  @BackupLogID INT
  --select @BackupLogID=MAX(id) from BackupLog -- Just for TestingSET @debugmessages = 0-- Dont Back up TempDBIF (@database = 'TempDB') RETURN-- Only Full backups for MasterIF (@database = 'Master' AND @backuptype <> 'Full') RETURN-- Don't take Log backups when the database isn't in full recoveryIF (@backuptype = 'Log' AND EXISTS (SELECT 1 FROM sys.databases WHERE recovery_model_desc <> 'FULL' AND name=@database))
  RETURN-- Get the Database GuidSELECT @databaseGuid = database_guid FROM sys.database_recovery_status WHERE database_id = DB_ID(@database)-- Make sure the @backupPathRoot has a trailing \IF (@backupPathRoot NOT LIKE '%\') SET @backupPathRoot = @backupPathRoot + '\' -- Get the last full backup location and idSELECT @FullBackupLocation=physical_device_name, @FullBackupSetId=backup_set_idFROM msdb.dbo.backupmediafamily fJOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_idWHERE backup_set_id=(
  SELECT MAX(msdb.dbo.backupset.backup_set_id)
  FROM msdb.dbo.backupset
  WHERE msdb.dbo.backupset.TYPE = 'D'
    AND database_name=@database
    AND database_guid=@databaseGuid )
IF (@FullBackupLocation IS NULL) BEGIN SET @backuptype='Full'  EXEC BackupLogEntry 'BackupDatabase', @database, 'No Full backup exists' END
ELSE IF ( @FullBackupLocation NOT LIKE @backupPathRoot + '%') BEGIN SET @backuptype='Full' EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup not in the correct folder' END
ELSE BEGIN
  EXEC MASTER..xp_fileexist @FullBackupLocation, @FileExists output
  IF (@FileExists = 0) BEGIN
    SET @backuptype='Full'
    SET @LogMessage = 'Full Backup Location (' + @FullBackupLocation + ') missing from the folder'
    EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
  END ELSE BEGIN
    IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup exists'
  END
END
-- Check to make sure the files are in the correct locationIF (@backuptype = 'Log') BEGIN

  --Get differential backup location
  SELECT @DifferentialBackupLocation=physical_device_name, @DifferentialBackupSetId=backup_set_id
  FROM msdb.dbo.backupmediafamily f
  JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
  WHERE backup_set_id=(
    SELECT MAX(msdb.dbo.backupset.backup_set_id)
    FROM msdb.dbo.backupset
    WHERE msdb.dbo.backupset.TYPE = 'I'
      AND backup_set_id > @FullBackupSetId
      AND database_name=@database
      AND database_guid=@databaseGuid
  )

  -- Check differential backup location
  IF (@DifferentialBackupLocation IS NOT NULL) BEGIN
    IF (@debugmessages=1) BEGIN SET @LogMessage = 'Checking Differential Backup Location (' + @DifferentialBackupLocation + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF ( @DifferentialBackupLocation NOT LIKE @backupPathRoot + '%' ) BEGIN
      SET @LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') in wrong folder'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      SET @DifferentialBackupLocation = NULL
    END
    ELSE BEGIN
       EXEC MASTER..xp_fileexist @DifferentialBackupLocation, @FileExists output
      IF (@FileExists = 0) BEGIN
        SET @LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') missing'
        EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
        SET @DifferentialBackupsetId = NULL
      END ELSE BEGIN
        IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Differential backup found'
      END
    END
  END

  IF (@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Checking Log Chain'

  DECLARE cur CURSOR FOR
    SELECT physical_device_name
    FROM msdb.dbo.backupmediafamily f
    JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
    WHERE backup_set_id > COALESCE(@DifferentialBackupsetId, @fullbackupsetid)
      AND database_name=@database
      AND database_guid=@databaseGuid
      AND TYPE = 'L'

  OPEN cur
  FETCH next FROM cur INTO @filename

  WHILE @backuptype='log' AND @@FETCH_STATUS=0 BEGIN
    IF (@debugmessages=1) BEGIN SET @LogMessage = 'Checking Log Backup Location (' + @filename + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF (@filename NOT LIKE @backupPathRoot + '%') BEGIN
      SET @backuptype = ''
      SET @LogMessage = 'Differential Backup (' + @filename + ') in wrong folder'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
    END ELSE BEGIN
      EXEC MASTER..xp_fileexist @filename, @FileExists output
      IF (@FileExists = 0) BEGIN
        SET @backuptype = ''
        SET @LogMessage = 'Differential Backup (' + @filename + ') missing'
      EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      END
    END
    IF @backuptype = '' BEGIN
      IF @fullbackupsetid IS NULL SET @backuptype='Full'
      ELSE SET @backuptype='Differential'
    END
    FETCH next FROM cur INTO @filename
  END
  CLOSE cur
  DEALLOCATE curEND


SET @now = GETDATE()SET @folder = @backupPathRoot + @@SERVERNAME + '\' + @database + '\'SET @folder = REPLACE(@folder,'/','')EXEC MASTER..xp_create_subdir @folderSET @filename = @folder + @database + ' ' + REPLACE(CONVERT(VARCHAR,@now,121),':','-') + ' ' + @backuptype + '.bak'SET @filename = REPLACE(@filename,'/','')
SET @LogMessage = 'Taking a ' + @BackupType + ' Backup,  Filename: ' + @filenameEXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
IF @backuptype = 'Log'
  BACKUP LOG @database TO DISK=@filename ELSE IF @backuptype = 'Differential'
  BACKUP DATABASE @database TO DISK=@filename WITH  differentialELSE IF @backuptype = 'Full'
  BACKUP DATABASE @database TO DISK=@filename GO--alter database robtest set recovery full

--BackupDatabase 'RobTest','Differential','C:\Backup'

Backup Script - Needs to be updated

Ack, It's been a while since I've updated my SQL backup script.  I've completely reworked it.  I'm in class this week, but will post it early next week.

Integrate Resharper Unit Test Runner after a successful build

Whilst trying to use TDD properly, I noticed that I'd have to build my solution and then run my unit tests. Now, most of the unit test runners will build your project at the same time, but it still was a little less convenient than I'd like.


I scraped together a few snippets from teh interwebs and came up with the following to be added to the VS Macros: Pop open the Macros IDE (Tools - Macros - Macros IDE) and in the Project Explorer choose the Environment Events Module. Then Add the following code:

Private SolutionBuildSuccess As Boolean


Private Sub BuildEvents_OnBuildBegin( _
   ByVal Scope As vsBuildScope, _
   ByVal Action As vsBuildAction _
) Handles BuildEvents.OnBuildBegin
  SolutionBuildSuccess = True
End Sub


Private Sub BuildEvents_OnBuildProjConfigDone( _
  ByVal Project As String, _
  ByVal ProjectConfig As String, _
  ByVal Platform As String, _
  ByVal SolutionConfig As String, _
  ByVal Success As Boolean _
) Handles BuildEvents.OnBuildProjConfigDone
  If Success = False Then
    SolutionBuildSuccess = False
  End If
End Sub


Private Sub BuildEvents_OnBuildDone( _
  ByVal Scope As EnvDTE.vsBuildScope, _
  ByVal Action As EnvDTE.vsBuildAction _
) Handles BuildEvents.OnBuildDone
  If SolutionBuildSuccess Then
    Dim win As Window
    win = DTE.ActiveWindow
    DTE.ExecuteCommand("ReSharper.ReSharper_UnitTest_RunSolution")
    win.Activate()
  End If
End Sub


Going through each of these:
SolutionBuildSuccess: This is just a boolean that will be true if every project builds.  No sense in even running tests if you can't even compile


BuildEvent_OnBuildBegin: This just resets the SolutionBuildSuccess at the begin of each build


BuildEvents_OnProjConfigDone: So, there are 2 events when a build finishes.  One fires when the complete build process is done (OnBuildDone).  This one, however, does not carry the result of the project build(s).  So, there is OnProjConfigDone which fires for each project that builds and has the result.  This will just set the SolutionBuildSuccess to false if any of the projects fail to build.


BuildEvents_OnBuildDone: This one fires when the build is all done.  So, I need to check to see if all the projects built successfully.  Then I save the currently active window.  Finally we run the tests and make that previously saved active window active again.  The reason for that is that after I'm done building and running the tests, generally I'm ready to code more.  So, instead of remembering that I have to smash ctrl-tab (I think) to jump back, I took the lazy approach and put it into the step.


Now, I remember back in the old days when F6 was the command to build stuff.  I'm not sure what one of my tomfooleries changed it, but it did.  So, I re-assigned it back to being my keyboard shortcut for build.


Any Comments or suggestions will probably be ignored, but feel free to leave them anyway.. 

Notepad++ Customize context Menu with TextFX

First off, I have used a bunch of different editors in my day, and have finally settled on Notepad++ ... the main reasons are:

• It's Free
• It has a fairly small footprint when it runs
• It can be portable
• It's customizable up the yin yang

This last point brings me to why I'm posting. You can add new items to the context menu which is a huge plus. The one I needed, however, was sort. I commonly will have a few lines of text that I'll need to sort

I'll have this:
Cat
Ball
Zebra
Molybdenum

I'll want this:
Ball
Cat
Molybdenum
Zebra

There is a really cool plugin called TextFX that has a command "Sort lines case insensitive (at column)". So, I figured I'd chuck that into the context menu.

Turns out, that integrating TextFX is not trivial. In fact, it's cryptic beyond belief. I think I finally figured out how to do it. I found a reference for the Notepad++ command names and used this in conjunction with another post regarding integrating TextFX into the context menu to figure out how to do all of this.

First off, TextFX is not integrated in the proper way with Notepad++. So, I had to do some playing.

In the aforementioned blog post the OP was trying to integrate the Rewrap Text command into the context menu. They were able to achieve this by using the PluginEntryName of "TextFX Characters". That's all well and good, but the Rewrap command is actually under the TextFX Edit menu. What I think is happening is that Notepad++ is seeing the TextFX Characters menu first and using that as the PluginEntryName. So, back to my issue, I found the corresponding command name for sorting on the command name reference page (T:Sort lines case insensitive (at column)) popped it in... reload N++ ... and ... poof... sort in the context menu.

For those of you that just want the code, here it is: <Item PluginEntryName="TextFX Characters" PluginCommandItemName="T:Sort lines case insensitive (at column)" ItemNameAs="Sort Lines" />

Happy Notepad++ing

Invalid URI scheme 'file://' for map control. Control must be hosted in a HTTP(s) website.

But... I'm not using file:// for my map control.

So, I'm programming with ESRI's Silverlight Map control. This started out as a test project, but it's become more of a production application. Instead of moving all my code to a new project, I'll just rename it from SilverlightApplication1 to MyProdcutionApplication.

I rename all the namespaces, remembering to rename the corresponding XAML references that don't get refactored. Then I rename the projects themselves and have to organize the folders.

So, I remove all the projects from the solution, rename the folders, and add them back in. I hit compile and woohoo, no errors. So, I go to run it and I get the following gem:

I recheck my map references and sure enough, I <am> using http://path/to/my/rest/endpoint ... so.. off to google.

After a 20 minute search trying all variations of the error, I finally stumble upon this thread in the ESRI forums. Back in 2009, Don Freeman got the same error and lo and behold, it was because the default start project was the Silverlight Application, not the Test Website.

I flipped my startup project to the test web app and poof... all is well

Logon Failed in SSMS on Windows 2008 (or Windows 7)

We recently moved our SQL Server from Windows Server 2003 to Windows Server 2008. When I tried to connect through SSMS on the server I got a rather odd error:
.

Now, I'm a SQL Administrator and am able to connect to the server from my local PC (Windows XP). I check the SQL Event log and see the following:
. After some Googling I find an article referencing UAC. It suggests right clicking on SSMS and running as administrator. I do this, accept the annoying message, and yowza, the system works.

Now, this is all well and good, but I really don't want to have to right click and run as administrator each time. Plus, as infrequently as I actually log on to the server to use SSMS, I'm sure I'll forget and end up googling this exact same error again. So, I poked around a little bit and found out that you can force the .exe to always run as administrator

• SSMS Properties
  
• Compatability Tab
  
• Show Settings for all users
  
  Notice how this is now for the ssms.exe file, not for the shortcut anymore
• Check "Run this program as administrator"
  

Now, whenever you run SSMS on the server, you will get the annoying message and it will work. I'm a little surprised that Microsoft didn't include that in the install package for SQL Server 2008, but at least there's a pretty easy fix.

HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials

I get a call this morning that one of my applications isn't working. I go to check it and sure enough I'm getting the following: "HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials". This application impersonates a specific Windows account so I check the usual stuff...

• Password change : Nope
• Security on the filesystem: All set properly
• Long username problem : Nope, not the problem here
• Account Disabled: nope

So, I'm running out of ideas.. so, I try to recycle the App pool (smart me, created a separate application pool just for this particular application). The pool comes back online and still no go.

I start looking through the log files and I'm not getting any more information except for the "invalid credentials". I take a look at the account again and chuck it into domain admins just to get this up and running. Recycle IIS this time and poof... same error

So, I've now ruled out any possible unauthorized access issue. So I take one final look th the user properties and I notice that even though the account isn't locked out, the password has lo and behold expired. Then I notice that the checkbox for "Password never expires" is not checked. I check the checkbox, apply the changes, and poof.. everything works

So, the lesson here, boys and girls, is to make sure you make sure your service accounts passwords do not expire

Sending Mail in SQL 2000

When our fleet division gets a new vehicle, they spend a few days (or weeks) prepping it and getting it ready. They assign the vehicle number, hourly rate, and a bunch of other stuff. After they're done, our finance department needs to track this in their inventory. The current fleet maintenance software can generate a report, but it doesn't contain everything that finance needs, so, the current practice is:

• Enter the data into the fleet software
• Re-enter it into an old access database (which has a report that has all the info)
• Print off the report from Access
• Make 3 copies
• Interoffice them to Finance.

I've been tasked with coming up with a better solution. After a few minutes of deliberation, a trigger seemed like a logical solution. The trigger will just fire off an email to all the parties when a vehicle is added. No problemo.

Sending mail in SQL Server is nice and easy. Just use sp_send_dbmail and you're good to go. That is, along as you are using SQL 2005 or better. Back in the dark ages (SQL 2000 and 7.0), however, you didn't have sp_send_dbmail. Instead, you had xp_sendmail.

Now, you ask, what is the problem with xp_sendmail. A bunch, but here's the highlights :

1. It's deprecated. I'm surprised it's still around in 2k8, but I bet it won’t be in the next version
2. You need to run it as a db_owner or a sysadmin. Now, you can grant others permissions to it, but that means mucking with permissions in the master database and that's generally frowned upon.
3. You can only send mail as the profile that's set up. This may not seem like a big deal, but I send out emails from the database and I want the recipients to be able to respond to different people based on the message. That means I'll have to set up a separate mail profile for each respondent.

Of course, the SQL Server development team saw these problems (and probably oodles more) and came up with sp_send_dbmail which addresses all of these

Now, since I’m using SQL 2000, I don’t have that option. After much googling, I was able to find xp_smtp_sendmail. The only issue was that most of the links were broken. So, I had to do a little more sleuthing and eventually found the right page in SQLDev.Net. After following their step by step instructions, I was able to send mail as the appropriate person and cut down about 10-15 hours of redundant work from the process.

Yay me.

Of course, we won’t mention that I forgot that the deleted table in a trigger is aptly named deleted and not updated. D’Oh

IIS Username too long causes 401.1 error

So, I'm making a new ASP.Net webpage that is accessing a database through Integrated Security and it's testing fine on my local PC running under my user credentials. All is working fine and dandy.

I go to publish it to my live server and remember that I have to set the security of the directory to be other than the IUSR account in order to access my database. No problemo. I just fire up IIS and hop over to the directory tab.

I hop back over to my browser and I get this lovely message:

HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.

I recheck my username and password and sure enough they are valid. I check the directory permissions and they're set properly as well. Then I remember.. the ASP.Net temporary file location (c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files) and sure enough that's set right too. So I check the IIS log file and it's as helpful as the error message.

So. Off to Google. First article mentions separating the Application Pool for separate user accounts. Seems unlikely, but I give it a whirl. Create a new App Pool. Set the Security, recycle IIS jsut for good measure and ... still the same error message.

It's getting to be quitting time.. so ... off to sleep on it.

I get back to work and check other ASP.Net apps that use security and they're all set in the Default App Pool (Which I really need to separate when I have some free time) and the security is set how I'd expect it.

I then notice something peculiar about the account that i'm using. It's a wee bit long. See, we used to use a common domain account for all our ASP.Net apps (ASPNet) but someone typed it in wrong once and it got disabled and all my apps failed. So, a year or so ago, I started creating new accounts for each ASP.Net application (ASPNetCalendar, ASPNetAgenda ... ). This account was named ASPNetContractorServiceRequest. Now, when I was creating the account I got some goofy error message about the name being too long, but I just ignored it figuring it was some old legacy error message. Turns out that the error message WAS important. When I created the account it actually truncated the name so 20 characters (ASPNetContractorServ) So, I changed the username and poof everything worked.

Insert Triggers always run

Who'da thunk it.
I have an insert trigger that does some data validation. no big deal. The validation that it's performing is this:

if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)


looks simple enough, but there are times when I will have an insert statement that isn't inserting any rows. Apparently triggers get fired whether or not there is any data actually being inserted. They get fired anytime there is an insert statement. So, I had to modify the code to have the following

if (exists (select 1 from inserted) begin
if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)
end


Happy SQLing.. (;

Transaction Log Backup script

So, Like a good boy, I have my SQL Server databases in full recovery model. Because of this, I also employ an aggressive backup strategy. The databases get a full backup weekly to tape, differential backup daily to tape, and transaction log backup to disk every 15 minutes.
We have a few robust SQL Server boxes each of which house lots of databases. I want the same backup strategy applied to each one. For the tape backups, we're employing HP OpenView Storage DataProtector which has a nice interface for doing just such a task.
There isn't a nice little tool (that I've found) in SQL Server to do this however. So, I crafted my own.

I originally had the transaction log files all going to the same file for a particular database. Since transaction logs are based on the last full or differential backup, this made the restores really complicated. I wanted to make sure that there was only 1 transaction log backup file per differential or full backup. So I baked that into the script as well.

Now, since I'm backing up the transaction logs to disk and I want those stored onto tape, there is a job in our tape backup that will also backup all the Transaction Log Backup Files. Finally, I wanted to delete the transaction log backup file has been archived to tape.

Hopefully all this makes sense. And now... the code:



/*
Transaction Log Maintenance Script.sql
Rob Bittner
August 5, 2009

Script to back up all the transaction logs on a database and warn if there
are file backups instead of tape backups

1) For Each Database:
- If the last backup was not a tape backup email the help desk.
- Otherwise, back up the transaction log.
2) Go through all the existing Transaction Log Backup files and delete those
that have been backed up to tape through the nightly file backup.

Modifications:

November 18, 2009 (RB) : Changed Email to go to the help desk instead of me

*/
declare cur cursor for
select name
from sys.databases
where state_desc='online' and recovery_model_desc='full' and name <> 'model'
open cur
declare @backuppath varchar(255)
declare @name varchar(255)
declare @filename varchar(255)
declare @lastbackupdate datetime
set @backuppath='e:\sqldata\Backup\'

fetch next from cur into @name

while @@fetch_status = 0 begin
print 'Backing up ' + @name

select @lastbackupdate=max(backup_start_date) from msdb.dbo.backupset where type in ('i','d') and database_name=@name
set @filename= @backuppath + @name + ' Transaction Log Backup ' + replace(convert(varchar,@lastbackupdate,120),':','') + '.bak'

if not exists ( -- see if there was no tape backup the last time is was backed up
select 1
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
and device_type = 7) begin
if not exists (
select 1 -- See if there already was a backup attempt (to prevent multiple emails)
from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name='Transaction Log Maintenance')
and step_id=0 -- job finished
and CONVERT -- date started
( DATETIME, RTRIM(run_date) ) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 ) / 216e4
> @lastbackupdate
) begin


declare @body varchar(max)
declare @path varchar(max)
declare @subject varchar(255)

select @path=bmf.physical_device_name
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
order by backup_start_date

set @subject='Transaction log backup failed on ' + @@servername

set @body=
'<html><head><style type="text/css">body{font-family: calibri;} h1,h2,h3 { margin:0}</style></head>'+
'<body>'+
'<h1>Error when backing up transaction log</h1>' +
'<h2>Most recent backup on the database is a file backup</h2><hr />' +
'<h3>Server: ' + @@servername + '</h3>' +
'<h3>Last Backup Path: ' + @path + '</h3>' +
'<h3>Last Backup Time: ' + convert(varchar,@lastbackupdate,109) + '</h3>' +
'<p style="font-weight:bold">To preserve the integrity of our database backup process, a full backup of ' + @name + ' must be performed utilizing Dataprotector ASAP!</p>'+
'<h2>Failure to back up this database to tape will cause the transaction log to continue to grow</h2>' +
'<p> - SQL Server Database Mailer</p>' +
'</body>' +
'</html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='email address here',
@subject = @subject,
@body = @body,
@body_format = 'HTML',
@profile_name='HelpDesk' ;
end -- Only email once if statement
end else begin
backup log @name to disk=@filename
end


fetch next from cur into @name
end

close cur

deallocate cur

declare @deletestatement varchar(255)
set @deletestatement = 'del ' + @backuppath + '* /a-a /q'
print @deletestatement
EXEC master..xp_cmdshell @deletestatement



Custom LINQ orderby expression

LINQ expressions are so slick that I don't know how I lived without them for so long. I had a little issue with a custom orderby, however, and found a pretty neat solution.

Here's the objects:


Festival{ String FestivalName, IEnumerable Events ...}
Event { String Type, DateTime Date ...}


A festival has many different Events: Start, Finish, Performances, etc. I want a list of festivals in order according to the start date. I was going to expose a specific property in Festival that returned the date of the Start event if it existed, but I wanted to generalize it. I knew about FirstOrDefault and came up with the following:


List Festival=
(from d in FestivalList
orderby s.Events.FirstOrDefault(q => q.Type=="Start").Date
select d)


I ended up getting a NullReferenceException because not all festivals were properly entered and some didn't have Start events. I decided that these should show up first. So I had to use a different approach with the orderby by using another inline LINQ query.


List Festival=
(from d in FestivalList
orderby
(from e in s.Events
where e.Type == "Start"
select e
).DefaultIfEmpty(new Event()).First().Date
select d)


The DefaultIfEmpty specifies how we should handle when there isn't an Event. In this case, I'm returning an empty event which will return DateTime.MinValue (This is part of the Event.Date definition). Slicker than snot.

Old Projects

Revisiting old code always amazes me. How I could think that inline SQL statements would actually be a good idea is beyond me. I know that i knew how to sprocify that stuff before. Anyway, Now I'm running into to Schema lock errors. fun times had by all...

PDF + SQL Reporting Services

Working with SQL Reporting Services has proved to be much easier than Crystal Reports. It's no big surprise that it integrates into M$'s products much easier. Since we're an exclusively M$ shop, that's good news for me.

What I'm currently working on is to create a mailing labels application for a web based GIS application. Basically, we want to provide a pdf that the user can save, or print with information pulled from a database. So, the GIS app will provide me with the Parcel Numbers, I'm going to take those and chuck them into a sproc and return a PDF.

I'm going to be coding a web application that will take a report name, parameters and then will return a PDF with the results.

I'll be posting more details as I code them.