SQL Server Scheduled Backups with NAnt

When installing and using production web applications, backup is always a must. I have written about SQL Server Backup before and this post would expend on that. The main goal here is to create a solution that periodically backs up your database. To do this, we’ll use the backup script from the previous post, employ NAnt to execute the script and zip the created backup. Last, we’ll schedule the execution of the NAnt build script through the Windows Task Scheduler or the “at” command. This procedure can backup a local or a remote SQL Server (as long as the remote server is on the same network).

Prerequisites

• Installed and configured version of NAnt (see Getting Started with NAnt - .NET Build Tool)
• Installed NAntContrib tasks (for the SQL task, see NAntContrib on SourceForge)
• Task Scheduler Service enabled in Control Panel/Admin Tools/Services
• SQL login with “dbo” rights to the database (to install the backupDatabase procedure)
• SQL login that has “public” rights to the database (so it can execute the backup)

Backup SQL Procedure

The script below with create the SQL server stored procedure to create a backup file of a given database. It takes two parameters:

1. databaseName – The SQL server database to be backed up
2. backupDirectory – The directory where the backup file will be created

You need to execute it against the database that you will be backing up and give the “public” role execution permissions to the procedure.

Code

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.18.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 None

Usage:
 exec dbo.backupDatabase ''ensembleVideo'', ''C:\Temp''

Parameters:
 @databaseName - varchar
 - The database to backup

 @backupDirectory - varchar
 - The path to where the database should be
 backed up. This should be an existing directory on
 the SQL Server where the database is located

Description:
 Backsup a given database to the specified directory
*/
create procedure dbo.backupDatabase
	@databaseName varchar(100),
	@backupDirectory varchar(1000)
as
declare @backupFileName varchar(100),
	@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
	@databaseDataFile varchar(100), @databaseLogFile varchar(100),
	@execSql varchar(1000)

-- If the backup directory does not end with ''\'', append one
if charindex(''\'', reverse(@backupDirectory)) > 1
	set @backupDirectory = @backupDirectory + ''\''

-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName = @backupDirectory + @databaseName + ''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'') + ''.bak''

set @execSql = ''
backup database ['' + @databaseName + '']
to disk = '''''' + @backupFileName + ''''''
with
  noformat,
  noinit,
  name = '''''' + @databaseName + '' backup'''',
  norewind,
  nounload,
  skip''

exec(@execSql)'
go

NAnt Backup Script

The script is somewhat complex. Here are the list of features:

• Backup a local SQL Server instance or a SQL Server instance on the same network. The key is that the file system of the networked instance must be available through UNC shares to the machine executing the backup script.
• Use a specified connection string or read the connection string from a .NET (or other type of XML)configuration file.

I will not dive into the script itself since it's properties and flow is documented fairly well.

Code

<project name="backupDatabase" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
	<!-- Set the name of the backup file that will be created -->
	<property name="zipFileName" value="databaseBackup" overwrite="false" />

	<!-- Set the path where the backup file will be finally stored -->
	<property name="localBackupDirectory" value="C:\Temp" overwrite="false" />

	<!-- Set the local path (relative to the SQL Server instance) where SQL Server will write the backup file -->
	<!-- This has to always be a local path since SQL server can't write to network paths -->
	<property name="sqlServerLocalBackupDirectory" value="C:\Temp" overwrite="false" />

	<!-- Set the UNC path to the above "sqlServerLocalBackupDirectory" local path -->
	<!-- This is only needed if you are backing up SQL server on the network -->
	<property name="sqlServerUNCBackupDirectory" value="\\beehive\windows$\Temp" overwrite="false" />
	<!-- For backing up a local SQL Server instance, commented the first "sqlServerUNCBackupDirectory" property -->
	<!-- and uncommented the one below this line, or set the "sqlServerUNCBackupDirectory" property to "" -->
	<!--<property name="sqlServerUNCBackupDirectory" value="" overwrite="false" />-->

	<!-- Set the .NET connection string for connecting to the database -->
	<!-- This setting always takes precedence over the config file below -->
	<!-- To use a config file instead, set this property to "" (like so value="") -->
	<property name="connectionString" value="Data Source=beehive\sql2005;Initial Catalog=ensembleVideo;User ID=test;Password=test;" overwrite="false" />

	<!-- Alternatively set the path to where the build script should get the connection string from  -->
	<!-- This is usually a app.config or connectionString.config file -->
	<property name="connectionStringConfigFilePath" value="C:\Inetpub\wwwroot\myApp\config\connectionStrings.config" overwrite="false" />

	<!-- Set the XPath expression that will be used to grab the connection string from the config file -->
	<property name="connectionStringXPath" value="/connectionStrings/add[@name = 'sqlServerConnection']/@connectionString" overwrite="false" />

	<!-- Set the regular expression that's needed to get the database name from the connection string -->
	<property name="getDatabaseNameFromConnecionStringRegEx" value="Initial Catalog=(?'databaseName'.*?);" overwrite="false" />

	<property name="todaysDate" value="${string::substring(string::replace(datetime::to-string(datetime::now()), '/', '.'), 0, 10)}" />
	<property name="todaysLocalBackupDirectory" value="${path::combine(localBackupDirectory, todaysDate)}" />

  	<target name="run">
		<!-- If the SQL Server UNC directory was not specified and the local backup directory exists -->
		<if test="${string::get-length(sqlServerUNCBackupDirectory) == 0 and directory::exists(localBackupDirectory)}">
			<!-- This is a back of a local SQL Server instance -->
			<mkdir dir="${todaysLocalBackupDirectory}" />

			<property name="todaysSqlServerLocalBackupDirectory" value="${todaysLocalBackupDirectory}" />
			<property name="todaysSqlServerUNCBackupDirectory" value="${todaysLocalBackupDirectory}" />

			<property name="localSqlServer" value="true" />
		</if>

		<if test="${string::get-length(sqlServerUNCBackupDirectory) > 0 and directory::exists(sqlServerUNCBackupDirectory) }" >
			<property name="todaysSqlServerLocalBackupDirectory" value="${path::combine(sqlServerLocalBackupDirectory, todaysDate)}" />
			<property name="todaysSqlServerUNCBackupDirectory" value="${path::combine(sqlServerUNCBackupDirectory, todaysDate)}" />

			<!-- This is a back of a networked SQL Server instance -->
			<mkdir dir="${todaysSqlServerUNCBackupDirectory}" />

			<property name="localSqlServer" value="false" />
		</if>

		<!-- If the connection string is empty,
		the connection string file exists and the XPath to find the connection string is not empty -->
		<if test="${string::get-length(connectionString) == 0 and file::exists(connectionStringConfigFilePath)
							and string::get-length(connectionStringXPath) > 0}" >
			<!-- Get the connection string to the database from the connection string config file -->
			<xmlpeek
				file="${connectionStringConfigFilePath}"
				xpath="${connectionStringXPath}"
				property="connectionString">
			</xmlpeek>
		</if>

		<!-- If the connectionString property is not empty and the regular expression to get the database name is not empty -->
		<if test="${string::get-length(connectionString) > 0 and string::get-length(getDatabaseNameFromConnecionStringRegEx) > 0}">
			<!-- Get the database name from the connection string -->
			<regex pattern="${getDatabaseNameFromConnecionStringRegEx}" input="${connectionString}" />
	
	    		<!-- Execute the stored procedure to bckup the database -->
			<sql connstring="Provider=SQLOLEDB;${connectionString}" transaction="false" delimiter=";" delimstyle="Normal">
			exec dbo.backupDatabase '${databaseName}', '${todaysSqlServerLocalBackupDirectory}';
			</sql>

			<!-- Zip up the created databse backup file -->
			<zip zipfile="${path::combine(todaysSqlServerUNCBackupDirectory, zipFileName + '-' + todaysDate + '.zip')}" ziplevel="9">
				<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
					<exclude name="**/*.zip" />
					<include name="*.*" />
				</fileset>
			</zip>

			<!-- Delete all other files in the today's backup directory except for the created zip files -->
			<delete>
				<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
					<exclude name="**/*.zip" />
					<include name="*.*" />
				</fileset>
			</delete>

			<!-- Move the contents of today's backup directory to the local backup directory -->
			<move todir="${localBackupDirectory}">
				<fileset basedir="${todaysSqlServerUNCBackupDirectory}">
					<include name="*.*" />
				</fileset>
			</move>

			<!-- Delete the "todays" directories -->
			<if test="${localSqlServer}">
				<delete dir="${todaysLocalBackupDirectory}" />
			</if>

			<if test="${not localSqlServer}">
				<delete dir="${todaysSqlServerUNCBackupDirectory}" />
			</if>
		</if>
  </target>
</project>

Setting Up Backup Script and Scheduling

Review the build script and set following properties to match your setup:

• zipFileName – The name of the zip file that will be created for the database backup
• localBackupDirectory – The local directory where the backup will be stored
• sqlServerLocalBackupDirectory - The local path (relative to the SQL Server instance) where SQL Server will write the backup file. This has to always be a local path since SQL server can't write to network paths.
• sqlServerUNCBackupDirectory – The UNC path to the above "sqlServerLocalBackupDirectory" local path. This is only needed if you are backing up SQL server on the network. For backing up a local SQL Server instance, set this "”.
• connectionString - The .NET connection string for connecting to the database. This setting always takes precedence over the “connectionStringConfigFilePath“ setting. To use a configuration file instead, set this property to "".
• connectionStringConfigFilePath - Alternatively set the path to where the build script should get the connection string from. This is usually a app.config or connectionString.config file.
• connectionStringXPath - The XPath expression that will be used to grab the connection string from the configuration file.
• getDatabaseNameFromConnecionStringRegEx - The regular expression that's needed to get the database name from the connection string.

The next step is to create the schedule with either the Task Scheduler or with “at” command.

To use the Task Scheduler:

1. Create a .bat file with the following: “driveLetter:\path\to\nant.exe /f:pathToNAntBackupScript.build” and of course replace that with the actual path to nant.exe and to the NAnt build script you got here.
2. Go to Control Panel / Scheduled Tasks / Add Scheduled Task
3. Browse for the .bat file you created in #1
4. Configure the schedule run as often as you would like

To use the “at” command:

1. Do the same as #1 above.
2. Open a command prompt and execute the “at” command:
   ”at 23:00 /every:M,T,W,Th,F pathToBatFileFromStep1.bat”
   
   That will schedule the task to execute every day of the week at 11:00pm. You can get more info on the “at” command from How To Use the AT Command to Schedule Tasks.

Bonus

You don’t have to hard code the values in the NAnt build script. You can pass them from the command line. In that manner you can reuse the same script for different database. You simply need to call the script with –D:propertyName=”value" like so:

path\to\nant.exe /f:pathToNAntBackupScript.build –D:zipFileName="myDatabase" –D:localBackupDirectory="D:\Temp"

Downloads

http://tech-cats.net/blog/downloads/sql/procedure-dbo.backupDatabase.txt
http://tech-cats.net/blog/nantScripts/backupDatabase.build
http://tech-cats.net/blog/nantScripts/backupDatabase.bat

Silly Little SQL Server Information Schema Helpers

If you work with SQL Server like I do, it is often necessary to drop functions and/or procedures through T-SQL. There are two ways to do this:

1. Using the sysobjects built-in to SQL Server
2. Using the information schema

Microsoft says they do not recommend the sysobjects way since things might change in future version of SQL Server. Regardless, just for reference, here is how to drop a view if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[listVideos]') and objectproperty(id, N'IsView') = 1)
drop view [dbo].[listVideos]
go

Doing the same with a stored procedure is not really much different:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addEncoding]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure dbo.addEncoding
go

Now for the more recommended way, using the information schema:

Dropping a view:

if exists (select * from information_schema.views where table_name = 'listVideos')
  drop view dbo.listVideos
go

Dropping a stored procedure:

if exists (select * from information_schema.routines where routine_name = 'addEncoding' and routine_type = 'procedure')
  drop procedure dbo.addEncoding
go

That is all good and swell, but if you are like me, you might hate to write that long SQL statement every time, so I thought I would shorten it a bit. Here is what I came up with for querying the information schema for procedures and functions. The same can be easily applied to views and tables.

First, we need a single function to check if a procedure or a function exists. I called it “routineExists”. You can download it as a text file from http://tech-cats.net/blog/downloads/sql/routineExistsFunction.txt. Here is the script to create it in your database:

if exists (select * from information_schema.routines where routine_name = 'routineExists' and routine_type = 'function')
	drop function dbo.routineExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @routineName varchar(255)
 - The name of the procedure or function

 @routineType varchar(25)
 - The type of routine (usually ''procudure'' or ''function''

Usage:
 select dbo.routineExists(''getCopyInformation'', ''procedure'')
 select dbo.routineExists(''getCopyInformation'', ''function'')
 if (select dbo.routineExists(''getCopyInformation'', ''procedure'')) = 1 print ''exists''

Returns:
 0 if the routine does not exist
 1 if the routine exists

Description:
 Checks the information schema view for the existance of the routine name passed in
*/
create function dbo.routineExists(@routineName varchar(255), @routineType varchar(25))
	returns bit
as
begin
	declare @routineExists bit
	set @routineExists = 0

	if exists (
		select	routine_name
		from		information_schema.routines
		where		routine_name = @routineName
						and
						routine_type = @routineType
	) set @routineExists = 1
	else set @routineExists = 0

	return @routineExists
end
'

The usage is quite simple:

-- Does the stored procedure 'getCopyInformation' exists, will return 0 or 1
select dbo.routineExists('getCopyInformation', 'procedure')

-- Does the function 'getCopyInformation' exists, will return 0 or 1
select dbo.routineExists('getCopyInformation', 'function')

-- If the procedure 'getCopyInformation' exists, print 'exists'
if (select dbo.routineExists('getCopyInformation', 'procedure')) = 1 print 'exists'

Better but still not quite satisfactory. Next step is wrap the "routineExists” function in individual functions, one for procedures and one for functions.

Let’s start with the procedures one. You can download it from http://tech-cats.net/blog/downloads/sql/procedureExistsFunction.txt. I creatively called it “procedureExists”:

if (select dbo.routineExists('procedureExists', 'function')) = 1 drop function dbo.procedureExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @procedureName varchar(255)
 - The name of the procedure

Usage:
 select dbo.procedureExists(''getCopyInformation'')
 if (select dbo.procedureExists(''getCopyInformation'')) = 1 print ''exists''

Returns:
 0 if the procedure does not exist
 1 if the procedure exists

Description:
 A wrapper for the dbo.routineExists that explicetly checks
 for the existance of the procedure name passed in
*/
create function dbo.procedureExists(@procedureName varchar(255))
	returns bit
as
begin
	return (select dbo.routineExists(@procedureName, ''procedure''))
end
'

Usage is pretty much the same as the “routineExists” function without the “routineType” parameter:

-- Does the stored procedure 'getCopyInformation' exists, will return 0 or 1
select dbo.procedureExists('getCopyInformation')

-- If the procedure 'getCopyInformation' exists, print 'exists'
if (select dbo.procedureExists('getCopyInformation')) = 1 print 'exists'

The function one is called “functionExists”. You can download it from http://tech-cats.net/blog/downloads/sql/functionExistsFunction.txt.

if (select dbo.routineExists('functionExists', 'function')) = 1 drop function dbo.functionExists
go

exec dbo.sp_executesql @statement = N'
/*
Created:
 07.22.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 information_schema.routines

Parameters:
 @functionName varchar(255)
 - The name of the function

Usage:
 select dbo.functionExists(''getCopyInformation'')
 if (select dbo.functionExists(''getCopyInformation'')) = 1 print ''exists''

Returns:
 0 if the function does not exist
 1 if the function exists

Description:
 A wrapper for the dbo.routineExists that explicetly checks
 for the existance of the function name passed in
*/
create function dbo.functionExists(@functionName varchar(255))
	returns bit
as
begin
	return (select dbo.routineExists(@functionName, ''function''))
end
'

Example Usage:

-- Does the function 'listToTable' exists, will return 0 or 1
select dbo.functionExists('listToTable')

-- If the function 'getCopyInformation' exists, print 'exists'
if (select dbo.functionExists('listToTable')) = 1 print 'exists'

Downloads

http://tech-cats.net/blog/downloads/sql/routineExistsFunction.txt
http://tech-cats.net/blog/downloads/sql/procedureExistsFunction.txt
http://tech-cats.net/blog/downloads/sql/functionExistsFunction.txt

Querying the File System - From ColdFusion to SQL Server

While looking for a solution of a different problem, I realized that SQL Server has a feature called extended stored procedures. Extended stored procedures, at least in SQL Server 2000, have to be written in C++ and compiled to a DLL. A good example of such a procedure is my previous article on Using Regular Expression in SQL Server. I am not going to get into how to write extended stored procedures in this article since my C++ skills are rusty at best. Instead, I will talk about using an existing stored procedure to query the file system and how to move from using ColdFusion to using SQL Server.

Using ColdFusion

Getting a list of files is pretty trivial in ColdFusion. All you have to do is use the "<cfdirectory>" tag like so:

<cfset localDirectoryPath = "C:\Temp" />

<cfdirectory
	name="textFiles"
	action="list"
	filter="*.txt"
	directory="#localDirectoryPath#" />

That will get a list of all files with the ".txt" extension in the "C:\Temp" directory.

Getting a list of files from a network drive is not any different except for specifying the networks path as UNC path (and a little service configuration):

<cfset networkDirectoryPath = "\\larry\share" />

<cfdirectory
	name="textFiles"
	action="list"
	filter="*.txt"
	directory="#networkDirectoryPath#" />

As stated above, this will not work if you do not have the ColdFusion service configured to use a specific account that has access to your network path.

The typical ColdFusion service setup looks like the following screen shot when accessed through the "Services" configuration in Windows:

To be able to query a network path, you need to specify a user that has access to that network path:

Using SQL Server

The setup for using SQL Server for querying network paths is the same as the one for ColdFusion:

Default Setup But should be something like

If you do not setup the SQL Server to run as the appropriate user, trying to query a network path with the method provided here, you will an access denied error:

With that aside, I have written a custom stored procedures based on the built-in extended stored procedure "xp_cmdshell". This procedure will return a query with the following:

• fileID - unique auto incremented integer value
• fileName - the name of the file
• lastModifiedOn - the date the file was last modified
• fileSize - the size of the file in bytes

Here are the results of querying the local path "C:\Temp" without a file extensions filter:

exec dbo.getDirectoryFileList 'c:\temp', null

Once configured the procedure can also work on network drives like so to return only files with the ".btw" extension:

exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'

Show Me the Code

/*
Type:		Stored Procedure
Name:		dbo.getDirectoryFileList
Author:		Boyan Kostadinov
Created:	03.17.2008
Dependencies:	master.dbo.xp_cmdshell
Usage:		exec dbo.getDirectoryFileList 'c:\temp', null
		exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'
Parameters:	@directoryPath varchar(255)
		- The path of the local or network directory

		@fileExtensionFilter varchar(10) - Optional
		- The file extension to filter the file list by
Returns:	A list of files found on the file system
*/
create procedure dbo.getDirectoryFileList
	@directoryPath varchar(255),
	@fileExtensionFilter varchar(10) = null
as

set nocount on

-- Declare and initialize local variables
declare @dosCommand varchar(5000)
set @dosCommand = ''

-- If the file extension fileter was empty, set it to all files
if @fileExtensionFilter is null or ltrim(rtrim(@fileExtensionFilter)) = ''
	set @fileExtensionFilter = '*.*'

-- If the directory path does not have an ending '\', append one
if substring(@directoryPath, len(@directoryPath), 1) <> '\'
	set @directoryPath = @directoryPath + '\'

-- Build the dos command to get a list of files
select @dosCommand =
	'insert into #tempFileList(fileListRow) ' +
	'exec master.dbo.xp_cmdshell ''dir ' + @directoryPath + + @fileExtensionFilter + ''''

-- Create a temporary table to store the file list
create table #tempFileList (
	fileListRow varchar(1000) null
)

-- Create the #fileList temporary table to store the file list
create table #fileList (
	fileID int primary key identity(1,1) not null,
	[fileName] varchar(255) not null,
	lastModifiedOn datetime not null,
	fileSize bigint not null,
)

exec(@dosCommand)

-- 8 - Delete unneeded data from the #OriginalFileList
delete from #tempFileList
where fileListRow is null

delete from #tempFileList
where fileListRow like '%Volume%'

delete from #tempFileList
where fileListRow like '%Directory%'

delete from #tempFileList
where fileListRow like '%<DIR>%'

delete from #tempFileList
where fileListRow like '%bytes%'

if not exists (select * from #tempFileList where fileListRow like '%access is denied%')
begin
	-- Populate the #fileList table with the final data
	insert into #fileList(lastModifiedOn, fileSize, [fileName])
	select	ltrim(substring(fileListRow, 1, 10))
		+
		' '
		+
		rtrim(ltrim(substring(fileListRow, 11, 15)))
		+
		'm'
		as 'lastModifiedOn',
		replace(ltrim(substring(fileListRow, 21, 18)), ',', '') as 'fileSize',
		ltrim(substring(fileListRow, 40, 1000)) as 'fileName'
	from	#tempFileList

	select * from #fileList
end
else
	select fileListRow as errorMessage from [#tempFileList] as e

-- Drop the temporary tables
drop table #tempFileList
drop table #fileList

set nocount off
go

Download

You can download the stored procedure from http://tech-cats.net/blog/downloads/sql/getDirectoryFileList.txt

References

Accessing the Windows File System from SQL Server

Get Business Days and Working Hours in a Month with SQL Server

In business applications, there often is a need to know the number of business days and/or hours in a month/year. There is a dozen ways to do this depending on your language but here is a SQL Server implementation. The function to get the number of working hours in a month, simply uses the first function - dbo.fnGetBusinessDaysInMonth and by default multiplies the number of business days by 8 hours a day.

To use these custom functions, you would call them as follows:

-- Get the number of business days in the month using the current date as a base
select dbo.fnGetBusinessDaysInMonth(getdate())
-- Outputs 21

-- Get the number of business days for January, the date can be any January date
select dbo.fnGetBusinessDaysInMonth('01/01/2008')
-- Outputs 23

-- Get the number of working hours for the current month with the default (8) number of hours per day
select dbo.fnGetWorkingHoursInMonth(getdate(), null)
-- Outputs 168

-- Get the number of working hours for the current month with 4 of hours per day
select dbo.fnGetWorkingHoursInMonth(getdate(), 4)
-- Outputs 84

And here are the function definitions:

/*
Type:		Function
Name:		dbo.fnGetBusinessDaysInMonth
Author:		Boyan Kostadinov
Created:	02.05.2007
Dependencies:	None
Usage:		select dbo.fnGetBusinessDaysInMonth(getdate())
		select dbo.fnGetBusinessDaysInMonth('01/01/2008')
Parameters:	@currentDate(datetime)
		- The date to use as a starting point
Description:

Gets the number of business days in a month
*/
alter function dbo.fnGetBusinessDaysInMonth(
	@currentDate datetime
)
returns int
as
begin

declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime

-- Get the beginning of the month
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))

-- Get the the beginning date of the next month
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))

-- Get the date range between the beginning and the end of the month
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)

return
(
	-- Get the number of business days by getting the number
	-- of full weeks * 5 days a week plus the number days remaining
	-- minus any days from the remaining days that are a weekend day
	select	@dateRange / 7 * 5 + @dateRange % 7 -  
	(
	        select	count(*)
		from
	        (
	            select 1 as d
	            union
	            select 2
	            union
	            select 3
	            union
	            select 4
	            union
	            select 5
	            union
	            select 6
	            union
	            select 7
	        ) weekdays
	        where	d <= @dateRange % 7
		        and
			datename(weekday, dateadd(day, -1, @endOfMonthDate) - d)
		        in ('Saturday', 'Sunday')
	)
)

end

To get the number of working hours in a month, we can use the dbo.fnGetWorkingHoursInMonth function defined as:

/*
Type:		Function
Name:		dbo.fnGetWorkingHoursInMonth
Author:		Boyan Kostadinov
Created:	02.05.2007
Dependencies:	None
Usage:		select dbo.fnGetWorkingHoursInMonth(getdate(), null)
		select dbo.fnGetWorkingHoursInMonth('01/01/2008', 4)
Parameters:	@currentDate(datetime)
		- The date to use as a starting point

		@workingHoursInADay(int) - Optional, default is 8
		- The number of working hours in a day
Description:

Gets the number of business days in a month
*/
create function dbo.fnGetWorkingHoursInMonth(
	@currentDate datetime,
	@workingHoursInADay int = null
)
returns int
as

begin

if @workingHoursInADay is null
	set @workingHoursInADay = 8

return dbo.fnGetBusinessDaysInMonth(@currentDate) * @workingHoursInADay

end

Using Generic Lists and SQL Server Views in Model-Glue

Model-Glue comes with some generic messages that you can use to manipulate data from your database. Generic messages are only available when you are using an ORM adapter with Model-Glue (such as Reactor or Transfer). In general, "messages" are how Model-Glue refers to requests to the server. As a very brief overview, messages are broadcast by the event handler and then handled by the defined Model-Glue controller. You can read more about this at The Model-Glue Event Lifecycle in Layman's Terms on Doug Boude's blog.

I find that I most often use the "ModelGlue.genericList" message which will return a ColdFusion query from the table specified. To use this feature in your event-handler, you simply add something like so under the "broadcasts":

<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />
</message>

The "object" and "queryName" are the two required arguments you have to specify. However, you can also add an order by clause:

<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />
</message>

Or even a criteria to filter the data:

<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />

  <!-- The column by which the data will be filtered -->
  <argument name="criteria" value="userName" />
</message>

The "criteria" argument is the equivalent of using the "where" clause in your SQL query. It has to contain a name of a column already part of your table. It can also contain a hard coded value for the column value such as:

<!-- The column by which the data will be filtered -->
<argument name="criteria" value="userName=boyan" />

If a hard coded value is not specified for the "criteria" argument, Model-Glue looks for the data in the form or url scope. In case current example, it will look for variable with the name "userName" in the event scope and use it's value to set the value of the "criteria".
The last, somewhat advanced option in the "genericList" message is the "gatewayMethod" argument. In case your query is more complicated than a simple table lookup (such as many joins, various arguments and special processing), you can use the "gatewayMethod" argument to specify what cfc method Model-Glue should use to get the "genericList". The use this argument, the method has to reside in the model gateway created for the object specified under the "object" argument. In this example, the "gatewayMethod" has to exists in the "Users" gateway. Here is what a gateway generated by Reactor looks like:

<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
	extends="usersGateway" >

</cfcomponent>

And here is a simple "gatewayMethod" to get a list of users based on a SQL Server stored procedure. The method takes a single numeric parameter called "showDeletedUsers" based on which it returns all users or only currently active users. Please note that this method is over simplified to show how the functionality works:

<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
	extends="usersGateway" >

<cffunction name="getUsers" access="public" output="false" returntype="query">
	<cfargument name="showDeletedUsers" type="numeric" required="yes" />

	<cfset var usersQuery = queryNew("") />

	<cfstoredproc datasource="#_getConfig().getDsn()#" procedure="dbo.spGetUsers">
		<cfprocparam
		type="in" null="no" cfsqltype="cf_sql_int"
		dbvarname="showDeletedUsers" value="#arguments.showDeletedUsers#" />

		<cfprocresult name="usersQuery" resultset="1">
	</cfstoredproc>

	<cfreturn usersQuery />
</cffunction>

</cfcomponent>

To invoke this method through the "genericList", you simple use:

<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />

  <!-- The column by which the data will be filtered -->
  <argument name="criteria" value="showDeletedUsers=0" />

  <!-- The custom method to use when retrieving the data -->
  <argument name="gatewayMethod" value="getUsers" />
</message>

The last thing to remember is that you can use the "genericList" with SQL Server views and not just tables. Views are like a custom table that you have defined on the server. They can contain custom fields and data from multiple tables. So if you have a created a view called "vGetUsers" (instead of using the custom "gatewayMethod"), you can use in the "genericList" in the same manner as you would a table:

<message name="ModelGlue.genericList">
  <!-- The name of your view -->
  <argument name="object" value="vGetUsers" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />
</message>

All Your Dates are Belong to Us - Custom Date Formatting in SQL Server

Most people that use SQL Server are familiar with formatting dates inside SQL Server. The common approach is:

convert(varchar, getdate(), 106) -- Displays a date in the format 25 Jan 2008

That is all fine and good if your requirements are satisfied with the formats available inside SQL Server. SQL Server does provide a decent number of formats. Here is the list right from the SQL Server help:

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
-	130*	Kuwaiti	dd mon yyyy hh:mi:ss:mmmAM
-	131*	Kuwaiti	dd/mm/yy hh:mi:ss:mmmAM

Wouldn't you know it, my requirements were not :-) I tried working with the built in formats and had to roll my own after all anyway. So here is the function I came up with: Please note: The function below relies on using regular expressions inside SQL Server. SQL Server does not have built in regular expression support so the function below relies on the method described in my other blog post about Using Regular Expression in SQL Server.

/*
Type:		Function
Name:		dbo.fnFormatDate
Author:		Boyan Kostadinov
Created:	01.25.2008
Dependencies:	master.dbo.fn_pcre_replace
Parameters:	@inputDate(datetime)
		- The date to format

		@formatString(varchar)
		- the format string to use (Examples "dd mm yyyy", "mmm.dd.yy")
Description:

Formats a given date based on the format specified in @formatString
d 	- one digit day (when applicable)
dd	- two digit day
ddd	- short day name
dddd	- long day name
m	- one digit month (when applicable)
mm	- two digit month
mmm	- short month name
mmmm	- long month name
yy	- two digit year
yyyy	- four digit year
*/
create function dbo.fnFormatDate
(
	@inputDate datetime,
	@formatString varchar(25)
)
returns varchar(20) as
begin
	declare @returnValue varchar(25)

	-- Declare local vairables
	declare @formattedDate varchar(25),
		@day varchar(20), @month varchar(20), @year varchar(20),
		@dayFormat varchar(5), @monthFormat varchar(5), @yearFormat varchar(5)

	set @dayFormat = ''
	set @monthFormat = ''
	set @yearFormat = ''

	-- Convert the supplied date to day mon year (25 Jan 2008)
	set @formattedDate = convert(varchar, @inputDate, 106)

	-- If the format string contains a format for the day
	if charindex('d', @formatString) > 0
		-- Get the day format string
		set @dayFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(d{1,4}).*', '$1')

	-- If the format string contains a format for the month
	if charindex('m', @formatString) > 0
		-- Get the month format string
		set @monthFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(m{1,4}|M{1,4}).*', '$1')

	-- If the format string contains a format for the year
	if charindex('y', @formatString) > 0
		-- Get the year format string
		set @yearFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(y{2,4}).*', '$1')

	-- Format the day value based on the format string for the day
	select	@day =
		case @dayFormat
		when 'dd' then master.dbo.fn_pcre_replace(@formattedDate, '^(\d+).*', '$1')
		when 'ddd' then substring(datename(dw, @formattedDate), 1, 3)
		when 'dddd' then datename(dw, @formattedDate)
		else convert(varchar, day(@formattedDate))
	end

	-- Format the month value based on the format string for the month
	select	@month =
		case @monthFormat
		when 'mm' then master.dbo.fn_pcre_replace(convert(varchar, @inputDate, 101), '^(\d+)/.*', '$1')
		when 'mmm' then master.dbo.fn_pcre_replace(@formattedDate, '\d+\s(\w+)\s\d+', '$1')
		when 'mmmm' then datename(m, @formattedDate)
		else convert(varchar, month(@formattedDate))
	end

	-- Format the year value based on the format string for the year
	select	@year =
		case @yearFormat
		when 'yy' then substring(convert(varchar, year(@formattedDate)), 3, 2)
		else convert(varchar, year(@formattedDate))
	end

	set @returnValue = @formatString

	-- If the day format was specified
	if @dayFormat <> ''
		-- Replace the day format string with the actual day value
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @dayFormat, @day)

	-- If the month format was specified
	if @monthFormat <> ''
		-- Replace the month format string with the actual month
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @monthFormat, @month)
	
	-- If the year format was specified
	if @yearFormat <> ''
		-- Replace the year format string with the actual year
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @yearFormat, @year)

	-- Return the formated value
	return @returnValue
end

To test this function, I created a table that hold the following date format string:

formatString             
-------------------------
dd MMM yy
dd MMM yyyy
dd-MM-yy
dd-MM-yyyy
dd.MM.yy
dd.MM.yyyy
dd/MM/yy
dd/MM/yyyy
ddMMMyy
ddMMMyyyy
MM-dd-yy
MM-dd-yyyy
MM/dd/yy
MM/dd/yyyy
MMM dd yyyy
MMM dd, yy
MMM dd, yyyy
MMMdd,yyyy
MMMddyyyy
yy.MM.dd
yy/MM/dd
yyMMdd
yyyy-MM-dd
yyyy.MM.dd
yyyy/MM/dd
yyyyMMdd
MMMyyyy

I tested the function with the simple SQL query:

select df.formatString,
 dbo.fnFormatDate(getdate(), df.formatString)
 as formattedDate
from dateFormats as df

And here are the results:

formatString              formattedDate       
------------------------- --------------------
dd MMM yy                 25 Jan 08
dd MMM yyyy               25 Jan 2008
dd-MM-yy                  25-01-08
dd-MM-yyyy                25-01-2008
dd.MM.yy                  25.01.08
dd.MM.yyyy                25.01.2008
dd/MM/yy                  25/01/08
dd/MM/yyyy                25/01/2008
ddMMMyy                   25Jan08
ddMMMyyyy                 25Jan2008
MM-dd-yy                  01-25-08
MM-dd-yyyy                01-25-2008
MM/dd/yy                  01/25/08
MM/dd/yyyy                01/25/2008
MMM dd yyyy               Jan 25 2008
MMM dd, yy                Jan 25, 08
MMM dd, yyyy              Jan 25, 2008
MMMdd,yyyy                Jan25,2008
MMMddyyyy                 Jan252008
yy.MM.dd                  08.01.25
yy/MM/dd                  08/01/25
yyMMdd                    080125
yyyy-MM-dd                2008-01-25
yyyy.MM.dd                2008.01.25
yyyy/MM/dd                2008/01/25
yyyyMMdd                  20080125
MMMyyyy                   Jan2008

Kill All Database Connections to a SQL Server Database

Today I needed to kill the active connections to a particular database so I can delete it. Usually that can be a painful process of opening enterprise manager and killing each active process one by one. While that approach might be fine for one or two active connections, when you have over 10, it gets to be annoying. So here is a script that will get all the active connections for a given database and kill each one. This is already part of my other post titled SQL Server Script to Restore a Database from File.

-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'

set @execSql = '' 
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     and
     DBID <> 0
     and
     spid <> @@spid
exec(@execSql)

Update
As Noel pointed out, it turns out there is much simpler way:

alter database dbName set single_user with rollback immediate

and to revert that

alter database dbName set multi_user with rollback immediate