Sanjeev Hasiza's Blog - .NETArchitect

Snippet: Drop all connections to a database (SQL 2005)

2012-02-02T17:11:00.002-06:00

If you want to drop all the connections to a database immediately, you can use the following commands:

USE master
GO

ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE

Alternatively you can kill all the processes using a database with this code:

USE master
go

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Snippet: Drop all connections to a database (SQL 2005)

2012-02-02T17:10:00.000-06:00

If you want to drop all the connections to a database immediately, you can use the following commands:

USE master
GO

ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE

Alternatively you can kill all the processes using a database with this code:

Execute SQL Server DTS Package using your .NET applications / code

2010-03-23T15:33:00.005-05:00

Use this code to execute DTS package using your .NET application/code:

(Just change the variables in red below to match your SQL Server environment)

using System;

using System.Runtime.InteropServices;

using DTS;

namespace DotNetArchitect.DTSPackage

{

///

/// Code to execute DTS package using your .NET application/code:

/// (Just change the variables in red below to match your SQL Server environment)

///

class Class1

{

///

/// The main entry point for the application.

///

[STAThread]

static void Main(string[] args)

{

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

try

{

package.LoadFromSQLServer("SERVER_NAME", // server name

SERVER_USER_NAME, // server username

SERVER_PASSWORD, // server password

DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,

null, // if the package has a password, otherwise null

null, // probably null

"PACKAGE_NAME", // name of the DTS package

ref pVarPersistStgOfHost);

package.Execute(); // execute the package

}

catch

{

throw;

}

finally

{

package.UnInitialize(); // unwrap the package

System.Runtime.InteropServices.Marshal.ReleaseComObject(package); // tell interop to release the reference

}

What is NUnit and How to Use it?

2010-03-22T15:12:00.000-05:00

NUnit

NUnit is an open source unit testing framework built for the .NET Framework. NUnit allows you to write tests in the language of your choice to test a specific function of your application. Unit tests are an excellent way to test the functionality of your code when you first write it, and also to provide a method for regression testing of your application. The NUnit application provides a framework for writing unit tests, as well as a graphical interface to run these tests and view the results.

Writing an NUnit Test

As an example, I'm going to test the functionality of the Hashtable class in the .NET Framework to determine if two objects can be added and then retrieved. My first step will be to add a reference to the NUnit.Framework assembly, which will give me access to the attributes and methods of the NUnit framework. Next I'll create a class and mark it with the TestFixture attribute. This attribute lets NUnit know that this class contains NUnit tests:

using System; using System.Collections;

using NUnit.Framework;

namespace NUnitExample

[TestFixture]

public class HashtableTest

public HashtableTest()

{  }

Next I'll create a method and mark it with the [Test] attribute so that NUnit knows that this method is a test. Then I'll set up a Hashtable and add two values to it, then use the Assert.AreEqual method to see if I can retrieve the same values that I added to the Hashtable, as shown in the following:

[Test] public void HashtableAddTest()

Hashtable ht = new Hashtable();

ht.Add("Key1", "Value1");

ht.Add("Key2", "Value2");

Assert.AreEqual("Value1", ht["Key1"], "Wrong object returned!");

Assert.AreEqual("Value2", ht["Key2"], "Wrong object returned!");

This will confirm that I can add and then retrieve values from the Hashtable—a simple test, but one that showcases the capabilities of NUnit. There are a number of test types, as well as various Assert methods, that can be used to test every part of your code.

To run this test, I'll need to build the project, open the generated assembly in the NUnit application, and then click the Run button. Figure below shows the results. I get a warm and fuzzy feeling when I see that big green bar because it lets me know that the test passed. This simple example shows how easy and powerful NUnit and unit testing can be. Being able to write a unit test that can be saved and rerun whenever you change code not only makes it easier for you to detect defects in your code, but the result is that you can deliver better applications.

NUnit is an open-source project that is available for download from http://www.nunit.org. There is also an excellent NUnit Visual Studio .NET add-in which allows you to run unit tests directly from Visual Studio. This can be found at http://sourceforge.net/projects/nunitaddin.

Ten Must-Have Tools Every .NET Developer Should Have

2010-02-16T21:03:00.001-06:00

Today, I ran across Microsoft's Article written by "James Avery" on .NET must have tools. I thought I should share it with everyone. Here is the list of must-have .NET tools every .net developer should use:

.NET Tools

NUnit to write unit tests
NDoc to create code documentation
NAnt to build your solutions
CodeSmith to generate code
FxCop to police your code
Snippet Compiler to compile small bits of code
Two different switcher tools, the ASP.NET Version Switcher and the Visual Studio .NET Project Converter
Regulator to build regular expressions
.NET Reflector to examine assemblies

ASP.NET Impersonation - Part 2

2010-02-15T16:23:00.002-06:00

This article is in continuation with my previous article "ASP.NET Impersonation - Part 1", in Part-1 I described how can we do impersonation using IIS and NTFS folder permissions. In this article I am going to explain how can we do Impersonation using custom code. The code example I am giving below is actually what I wrote long time back to authenticate my application over the network and there was a need to Impersonate users using the user credentials and I couldn't use the IIS way of impersonation.

Custom code to Impersonate application users

I have published the source code below which is a custom Impersonation class I wrote. Just copy this source code in your project as a new class and name it "cSiteImpersonation.cs" and add a reference of this class in your client project. After that is done, just call the function "ImpersonateUser(..... )" with valid credentials right before the point where you need to access the network drive/folder (where impersonation required to access the resources) and that should work.

Here is the definition for the function ImpersonateUser:

ImpersonateUser(ByVal sUserName As String, _

ByVal sPassword As String, _

ByVal sDomain As String)

Here is the source code for the Impersonation class:

/********************************************/
/**** SOURCE CODE EXAMPLE ****/

/********************************************/

using System;

using System.Security.Principal;

using System.Security.Permissions;

using System.Runtime.InteropServices;

namespace Sanjeev.NETArchitect.Apps.Security.AuthenticateAndAuthorize

{

public class cSiteImpersonation

{

#region "Declarations"

private enum Logon

{

Network = 3,

NetworkCleartext = 8

}

private enum Provider

{

UseDefault = 0,

WindowsNT35 = 1,

WindowsNT40 = 2,

Windows2000 = 3

}

#endregion

#region "Public Functions"

public WindowsImpersonationContext ImpersonateUser(string sUserName, string sPassword, string sDomain)

{

//-----------------------------------------

// Function: ImpersonateUser

// Description: Changes to windows identity given the input parameters.

// Inputs: sUserName - The User to impersonate

// sPassword - The Password for the impersonated User.

// sDomain - The Domain that is being accessed

// Outputs: WindowsImpersonationContext, the Context for the Impersonation

//-----------------------------------------

WindowsIdentity objNewIdentity = default(WindowsIdentity);

objNewIdentity = GetWindowsIdentity(sUserName, sDomain, sPassword);

return objNewIdentity.Impersonate;

}

public WindowsIdentity GetCurrentIdentity()

{

//-----------------------------------------

// Function: GetCurrentIdentity

// Description: Returns the current windows identity.

// Inputs: None

// Outputs: WindowsIdentity, the current identity

//-----------------------------------------

return WindowsIdentity.GetCurrent();

}

public WindowsIdentity RevertIdentity(WindowsImpersonationContext CurrentImpersonation)

{

//-----------------------------------------

// Function: RevertIdentity

// Description: Undoes the impersonation, which reverts to the windows identity

// prior to the impersonation

// Inputs: None

// Outputs: WindowsIdentity, the current identity

//-----------------------------------------

CurrentImpersonation.Undo();

}

#endregion

#region "Private Functions"

[DllImport("advapi32.dll")]

private static extern bool LogonUser(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref int phToken);

//-----------------------------------------

// Function: LogonUser

// Description: Uses the windows32 advapi32.dll to logon to the windows

// environment as another user.

// Inputs: lpszUsername - The User to impersonate

// lpszDomain - The Domain that is being accessed

// lpszPassword - The Password for the impersonated User

// dwLogonType - Logon type

// dwLogonProvider - The type of windows provider

// phToken - Returned Security Token

// Outputs: Boolean, Success or Failure

//-----------------------------------------

[DllImport("Kernel32.dll")]

private static extern int GetLastError();

//-----------------------------------------

// Function: GetLastError

// Description: Used the Kernal32.dll to get the last error .

// Inputs: None

// Outputs: WindowsIdentity, the Context for the Impersonation

//-----------------------------------------

[SecurityPermissionAttribute(SecurityAction.Demand, ControlPrincipal = true, UnmanagedCode = true)]

private static WindowsIdentity GetWindowsIdentity(string UserName, string Domain, string Password)

{

//-----------------------------------------

// Function: GetWindowsIdentity

// Description: Logon the user and return the windows identity given

// the input parameters.

// Inputs: sUserName - The User to impersonate

// sDomain - The Domain that is being accessed

// sPassword - The Password for the impersonated User.

// Outputs: WindowsIdentity, the Context for the Impersonation

//-----------------------------------------

int SecurityToken = 0;

bool Success = false;

Success = LogonUser(UserName, Domain, Password, Logon.Network, Provider.UseDefault, SecurityToken);

if (!Success)

{

throw new Exception("Logon Failed. Error: " + GetLastError());

}

return new WindowsIdentity(new IntPtr(SecurityToken));

}

#endregion

}

/********************************************/

ASP.NET Impersonation - Part 1

2010-02-14T15:42:00.001-06:00

There are two ways of doing Impersonation in .NET:

1. Let the IIS handle it

2. Custom code to Impersonate application users

I will try to explain both ways of doing Impersonation as two different articles. Let me start with the option-1, which is the easiest out of these two and is preferred these days.

IIS way of doing Impersonation

When using impersonation, ASP.NET applications can optionally execute with the identity of the client on whose behalf they are operating. The usual reason for doing this is to avoid dealing with authentication and authorization issues in the ASP.NET application code. Instead, you rely on IIS to authenticate the user and either pass an authenticated token to the ASP.NET application or, if unable to authenticate the user, pass an unauthenticated token. In either case, the ASP.NET application impersonates whichever token is received if Impersonation is enabled. The ASP.NET application, now impersonating the client, then relies on the settings in the NTFS directories and files to allow it to gain access, or not.

For ASP compatibility, impersonation is disabled by default. If impersonation is enabled for a given application, ASP.NET always impersonates the access token that IIS provides to ISAPI extensions. That token can be either an authenticated user token, or the token for the anonymous user ( such as IUSR_MACHINENAME ). The impersonation occurs regardless of the type of authentication being used in the application and whether the user is authenticated, in which case, it is the anonymous user token.

Only application code is impersonated; compilation and configuration are read as the process token. If an application is on a UNC share, ASP.NET will always impersonate the token provided to IIS to access that share unless a configured account is used. If an explicit configured account is provided, ASP.NET will use that account in preference to the IIS UNC token. Applications that do want per-request impersonation can simply be configured to impersonate the user making the request.

Impersonation is disabled at the computer level by default and, unless overridden, all the application domains inherit this setting. You can enable impersonation by putting a configuration file in the application root directory.

As is the case with other configuration directives, this directive applies hierarchically. It is respected by nested applications in the hierarchy, unless explicitly overridden. The default value for this setting is as follows:

A minimal configuration file to enable impersonation for an application might look similar to the following example:

There is also name support for running an application as a configurable identity. For example:

This enables the entire application to run as domain\username, regardless of the identity of the request, so long as the password is correct. This type of impersonation can be delegated to another computer.

This concludes the IIS way of handling Impersonation. I will write another article tomorrow to explain how to write a custom code to Impersonation users for the application.

Better Coding Rules & Coding Standards for .NET Apps and SQL Server

2010-02-09T14:22:00.001-06:00

Followings are the great links I use for coding standards/rules references...

Rules to Better ...

http://www.ssw.com.au/SSW/Standards/default.aspx

.NET Apps

Rules to Better .NET Projects:

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterdotNETProjects.aspx

Rules to Better Code:

http://www.ssw.com.au/ssw/Standards/Rules/RulestoBetterCode.aspx#NamingConventions

SQL Server

Rules to Better SQL Server Databases:

http://www.ssw.com.au/SSW/standards/rules/RulesToBetterSQLServerDatabases.aspx

SQL Server Object Naming Standard:

http://www.ssw.com.au/ssw/Standards/DeveloperSQLServer/SQLServerStandard_1_ObjectNaming.aspx

SQL Stored Procedure Naming Standard:

http://www.ssw.com.au/ssw/Standards/DeveloperSQLServer/SQLServerStandard_2_StoredProcedureNaming.aspx

SQL Stored Procedure Formatting Standard:

http://www.ssw.com.au/ssw/Standards/DeveloperSQLServer/SQLServerStandard_3_StoredProcedureFormatting.aspx

SQL Server Indexes Naming Standard:

http://www.ssw.com.au/ssw/Standards/DeveloperSQLServer/SQLServerStandard_4_IndexesNaming.aspx

SQL Server Relationship Naming Standard:

http://www.ssw.com.au/ssw/Standards/DeveloperSQLServer/SQLServerStandard_5_RelationshipNaming.aspx

Automating Database maintenance in SQL 2005 Express Edition Part I

2010-02-08T14:06:00.001-06:00

I had a requirement to Automate the database backups etc in SQL Server 2005 and found this article. It is very interesting. If have same type of requirement and you need a script to automate the backups, you can follow this script below, just change the variable names to match your server and database.

It is a long script, make sure you copy everything below.

/***********************************************/
/* Script Starts Here */
/***********************************************/
use master
GO

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

CREATE PROCEDURE expressmaint
(
@database sysname, -- database name | ALL_USER | ALL_SYSTEM
@optype varchar(7), -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB
@backupwith varchar(500) = NULL, -- additional backup options
@backupfldr varchar(200) = NULL, -- folder to write backup to
@reportfldr varchar(200) = NULL, -- folder to write text report
@verify bit = 1, -- verify backup
@verifywith varchar(500) = NULL, -- additional verify options
@dbretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@dbretainval int = 1, -- specifies how many retainunits to keep backup
@report bit = 1, -- flag to indicate whether to generate report
@rptretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@rptretainval int = 1, -- specifies how many retainunits to keep reports
@checkattrib bit = 0, -- check if archive bit is cleared before deleting
@delfirst bit = 0, -- delete before backup (handy if space issues)
@debug bit = 0 -- print commands to be executed
)
AS

SET NOCOUNT ON
SET DATEFORMAT YMD

/************************
VARIABLE DECLARATION
************************/

DECLARE @fso int
DECLARE @file int
DECLARE @reportfilename varchar(500)
DECLARE @backupfilename varchar(500)
DECLARE @delfilename varchar(500)
DECLARE @cmd varchar(650)
DECLARE @backupfldrorig varchar(200)
DECLARE @databaseorig sysname
DECLARE @table nvarchar(600)
DECLARE @exists varchar(5)
DECLARE @err int
DECLARE @start datetime
DECLARE @finish datetime
DECLARE @runtime datetime
DECLARE @output varchar(200)
DECLARE @errormsg varchar(210)
DECLARE @datepart nchar(2)
DECLARE @execmd nvarchar(1000)
DECLARE @delcmd nvarchar(1000)
DECLARE @exemsg varchar(8000)
DECLARE @filecount int ; SET @filecount = 0
DECLARE @delcount int ; SET @delcount = 0
DECLARE @hr int ; SET @hr = 0
DECLARE @ret int ; SET @ret = 0
DECLARE @cmdret int ; SET @cmdret = 0
DECLARE @delbkflag int ; SET @delbkflag = 0
DECLARE @delrptflag int ; SET @delrptflag = 0
DECLARE @filecrt int ; SET @filecrt = 0
DECLARE @user sysname ; SET @user = SUSER_SNAME()
DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
DECLARE @jobstart char(12) ;
DECLARE @stage int ; SET @stage = 1

SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)
IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'
IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'
SET @backupfldrorig = @backupfldr
SET @databaseorig = @database

CREATE TABLE #files(filename varchar(255))
CREATE TABLE #exists(exist int,isdir int,parent int)
CREATE TABLE #databases(dbname sysname)

/**********************************
INITIALIZE FSO IF @report = 1
***********************************/

IF @report = 1
BEGIN
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating File System Object',16,1)
SET @ret = 1
GOTO CLEANUP
END
END

/************************
CHECK INPUT
************************/

-- check SQL2005 or higher
IF (select SUBSTRING(@@version,(CHARINDEX('-',@@version)+2),1))<9 BEGIN RAISERROR('SQL2005 or higher is required for sp_expressmaint',16,1) SET @ret = 1 GOTO CLEANUP END -- check sysadmin IF IS_SRVROLEMEMBER('sysadmin') = 0 BEGIN RAISERROR('The current user %s is not a member of the sysadmin role',16,1,@user) SET @ret = 1 GOTO CLEANUP END -- check database exists and is online IF @database NOT IN ('ALL_USER','ALL_SYSTEM') BEGIN IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
BEGIN
RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)
SET @ret = 1
GOTO CLEANUP
END
END

-- check @optype is valid
IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')
BEGIN
RAISERROR('%s is not a valid option for @optype',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END

-- check recovery mode is correct if trying log backup
IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))
BEGIN
RAISERROR('%s is not a valid option for database %s because it is in SIMPLE recovery mode',16,1,@optype,@database)
SET @ret = 1
GOTO CLEANUP
END
END

-- no log backups for system databases
IF @database = 'ALL_SYSTEM'
BEGIN
IF @optype = 'LOG'
BEGIN
RAISERROR('%s is not a valid option for the option ALL_SYSTEM',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
END

-- check that @backupfldr exists on the server
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
INSERT #exists
EXEC master.dbo.xp_fileexist @backupfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END

-- check that @reportfldr exists on the server
IF @reportfldr IS NOT NULL or @report = 1
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
DELETE #exists
INSERT #exists
EXEC master.dbo.xp_fileexist @reportfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END

-- check @dbretainunit is a vaild value
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')
BEGIN
RAISERROR('%s is not a valid value for @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)
SET @ret = 1
GOTO CLEANUP
END
END

--check @dbretainval is a vaild value
IF @dbretainval<1 BEGIN RAISERROR('%i is not a valid value for @dbretainval (must be >0)',16,1,@dbretainval)
SET @ret = 1
GOTO CLEANUP
END

-- check @rptretainunit is a vaild value if present
IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL
BEGIN
RAISERROR('%s is not a valid value for @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)
SET @ret = 1
GOTO CLEANUP
END

--check @rptretainval is a vaild value
IF @rptretainval<1 BEGIN RAISERROR('%i is not a valid value for @rptretainval (must be >0)',16,1,@rptretainval)
SET @ret = 1
GOTO CLEANUP
END

/***********************************
list of databases to process
************************************/

IF @database IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF @database = 'ALL_USER'
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id > 4
AND (@optype <> 'LOG' OR recovery_model <> '3')
ELSE
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id in (1,3,4)
END
ELSE
INSERT #databases(dbname) SELECT @database

/***********************************
INITIALIZE REPORT IF @report = 1
************************************/

-- generate report filename
SELECT @reportfilename = @reportfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
END + @jobstart + '.txt'

-- if no report just set @reportfilename to NULL
IF @report = 0 SET @reportfilename = NULL

IF @debug = 1
BEGIN
PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')
END

IF @report = 1
BEGIN
-- create report file
EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename
IF (@hr <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating log file',16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
-- set global flag to indicate we have created a report file
SET @filecrt = 1

-- write header
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ' + '[' + @user + ']'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output

IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
SET @output = 'Starting backup on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) = 'CHECKDB'
BEGIN
SET @output = 'Starting CheckDB on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) IN ('REINDEX','REORG')
BEGIN
SET @output = 'Starting Reindex on ' + convert(varchar(25),getdate(),100)
END

IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

/************************
BACKUP ACTIONS
************************/

IF UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB
IF UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX

-- if @delfirst = 1 we need to delete prior backups that qualify
IF @delfirst = 1 GOTO DELFIRST

-- this label is so that we can return here after deleting files if @delfirst = 1
DOBACKUP:

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- set backup start time
SET @start = GETDATE()

-- write to text report
IF @report = 1
BEGIN
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': ' +
CASE WHEN UPPER(@optype) = 'DB' THEN 'Full Backup '
WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '
WHEN UPPER(@optype) = 'LOG' THEN 'Log Backup '
END + 'starting at ' + CONVERT(varchar(25),@start,100)
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- backup subfolder
SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + @database + '\" MKDIR "' + @backupfldrorig + @database + '\"'
EXEC master.dbo.xp_cmdshell @execmd,no_output
SET @backupfldr = @backupfldrorig + @database + '\'

SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

/************************
FULL BACKUP
************************/

IF UPPER(@optype) = 'DB'
BEGIN

SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Full database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

/************************
DIFFERENTIAL BACKUP
************************/

IF UPPER(@optype) = 'DIFF'
BEGIN

SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' +
@backupfilename + ''' WITH DIFFERENTIAL' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' , ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure

SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Differential database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

/************************
LOG BACKUP
************************/

IF UPPER(@optype) = 'LOG'
BEGIN

SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure

SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Log backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

SET @stage = (@stage + 1)

FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur

/************************
VERIFY BACKUP
************************/

IF @verify = 1
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

SELECT @backupfilename = @backupfldrorig + @database + '\' + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

SET @start = GETDATE()

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +
CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- verify success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'
IF @debug = 1 PRINT @output

IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate verify runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Verify backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur
END

/************************
DELETE OLD FILES
************************/

-- we have already deleted files so skip to the end
IF @delfirst = 1 GOTO CLEANUP

-- this label is so that we can delete files prior to backup if @delfirst = 1
DELFIRST:

/************************
DELETE OLD BACKUPS
************************/

SET @datepart = CASE
WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@dbretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@dbretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@dbretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@dbretainunit) = 'MONTHS' THEN N'yy'
END

IF @debug = 1 PRINT '@datepart for backups = ' + @datepart

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

SET @backupfldr = + @backupfldrorig + @database + '\'
SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

-- load files in @backupfldr
IF @checkattrib = 1
SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
ELSE
SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'

IF @debug = 1 PRINT '@cmd = ' + @cmd

DELETE #files
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')

IF @debug = 1 SELECT * FROM #files

-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0

-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@dbretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based (include current backup that's not in #files)
BEGIN
IF @filecount>0
BEGIN
IF @dbretainval>1
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
END

SELECT @delcount = COUNT(*) from #files

END
ELSE
BEGIN
SELECT @delcount = 0
END
END

IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)

-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output

-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
ELSE
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END

FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END

-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur

-- clear temporary table and variables
DELETE #files
SET @cmd = ''
SET @delcmd = ''
SET @delfilename = ''
SET @datepart = ''
SET @filecount = 0
SET @delcount = 0
SET @cmdret = 0
SET @stage = @stage + 1

/************************
DELETE OLD REPORTS
************************/

DELREPORTS:

IF @rptretainunit IS NOT NULL
BEGIN
SET @datepart = CASE
WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@rptretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@rptretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@rptretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@rptretainunit) = 'MONTHS' THEN N'yy'
END

IF @debug = 1 PRINT '@datepart for reports = ' + @datepart

-- write to text report
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- load files in @reportfldr
SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(@databaseorig,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_' END + '*.txt"'

IF @debug = 1 PRINT '@cmd = ' + @cmd

INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL

IF @debug = 1 SELECT * FROM #files

-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0

-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@rptretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END

IF @debug = 1 PRINT STR(@delcount)

-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output

-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN

SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END

FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END

-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

-- update stage
SET @stage = @stage + 1
END
-- if we got here due to @delfirst = 1 go back and do the backups
IF @delfirst = 1
GOTO DOBACKUP
ELSE
GOTO CLEANUP

/************************
CHECKDB
************************/

CHECK_DB:

IF @optype = 'CHECKDB'
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- set backup start time
SET @start = GETDATE()

SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'
IF @debug = 1 PRINT 'DBCC Command : ' + @execmd

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

SET @finish = GETDATE()

--calculate checkdb runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'CheckDB completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database

END

CLOSE dcur
DEALLOCATE dcur

-- delete reports
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
GOTO DELREPORTS
END

/************************
REINDEX/REORG
************************/

REINDEX:

IF @optype in ('REINDEX','REORG')
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
IF @optype = 'REINDEX'
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'
ELSE
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'

IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

-- set start time
SET @start = GETDATE()

-- all user tables
CREATE TABLE #tables(tablename sysname)
EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +
N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +
N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +
N'WHERE t.is_ms_shipped = 0 AND i.type>0')

DECLARE tcur CURSOR LOCAL FAST_FORWARD
FOR SELECT tablename FROM #tables ORDER BY tablename
OPEN tcur
FETCH NEXT FROM tcur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN

IF @report = 1
BEGIN
IF @optype = 'REINDEX'
SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table
ELSE
SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table

EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

IF @optype = 'REINDEX'
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'
ELSE
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'

IF @debug = 1 PRINT 'Reindex Command : ' + @execmd

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE tcur
DEALLOCATE tcur
DROP TABLE #tables
GOTO CLEANUP

END CATCH

FETCH NEXT FROM tcur INTO @table
END

CLOSE tcur
DEALLOCATE tcur

SET @finish = GETDATE()

--calculate runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Index maintenance completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

DROP TABLE #tables

SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database

END

CLOSE dcur
DEALLOCATE dcur

-- delete reports
GOTO DELREPORTS
END

/************************
CLEAN UP
************************/

CLEANUP:

DROP TABLE #files
DROP TABLE #exists
DROP TABLE #databases

-- if we encountered errors deleting old backups return failure
IF @delbkflag<>0
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END

-- if we encountered errors deleting old reports return failure
IF (@delrptflag<>0 AND @delbkflag = 0)
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END

-- if we created a file make sure we write trailer and destroy object
IF @filecrt = 1
BEGIN
-- write final part of report
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)
+ ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''

-- destroy file object
EXEC @hr=sp_OADestroy @file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
END

IF @report = 1
BEGIN
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
END

RETURN @ret
GO
use master
GO

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

CREATE PROCEDURE expressmaint
(
@database sysname, -- database name | ALL_USER | ALL_SYSTEM
@optype varchar(7), -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB
@backupwith varchar(500) = NULL, -- additional backup options
@backupfldr varchar(200) = NULL, -- folder to write backup to
@reportfldr varchar(200) = NULL, -- folder to write text report
@verify bit = 1, -- verify backup
@verifywith varchar(500) = NULL, -- additional verify options
@dbretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@dbretainval int = 1, -- specifies how many retainunits to keep backup
@report bit = 1, -- flag to indicate whether to generate report
@rptretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@rptretainval int = 1, -- specifies how many retainunits to keep reports
@checkattrib bit = 0, -- check if archive bit is cleared before deleting
@delfirst bit = 0, -- delete before backup (handy if space issues)
@debug bit = 0 -- print commands to be executed
)
AS

SET NOCOUNT ON
SET DATEFORMAT YMD

/************************
VARIABLE DECLARATION
************************/

DECLARE @fso int
DECLARE @file int
DECLARE @reportfilename varchar(500)
DECLARE @backupfilename varchar(500)
DECLARE @delfilename varchar(500)
DECLARE @cmd varchar(650)
DECLARE @backupfldrorig varchar(200)
DECLARE @databaseorig sysname
DECLARE @table nvarchar(600)
DECLARE @exists varchar(5)
DECLARE @err int
DECLARE @start datetime
DECLARE @finish datetime
DECLARE @runtime datetime
DECLARE @output varchar(200)
DECLARE @errormsg varchar(210)
DECLARE @datepart nchar(2)
DECLARE @execmd nvarchar(1000)
DECLARE @delcmd nvarchar(1000)
DECLARE @exemsg varchar(8000)
DECLARE @filecount int ; SET @filecount = 0
DECLARE @delcount int ; SET @delcount = 0
DECLARE @hr int ; SET @hr = 0
DECLARE @ret int ; SET @ret = 0
DECLARE @cmdret int ; SET @cmdret = 0
DECLARE @delbkflag int ; SET @delbkflag = 0
DECLARE @delrptflag int ; SET @delrptflag = 0
DECLARE @filecrt int ; SET @filecrt = 0
DECLARE @user sysname ; SET @user = SUSER_SNAME()
DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
DECLARE @jobstart char(12) ;
DECLARE @stage int ; SET @stage = 1

SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)
IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'
IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'
SET @backupfldrorig = @backupfldr
SET @databaseorig = @database

CREATE TABLE #files(filename varchar(255))
CREATE TABLE #exists(exist int,isdir int,parent int)
CREATE TABLE #databases(dbname sysname)

/**********************************
INITIALIZE FSO IF @report = 1
***********************************/

IF @report = 1
BEGIN
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating File System Object',16,1)
SET @ret = 1
GOTO CLEANUP
END
END

/************************
CHECK INPUT
************************/

-- check SQL2005 or higher
IF (select SUBSTRING(@@version,(CHARINDEX('-',@@version)+2),1))<9 BEGIN RAISERROR('SQL2005 or higher is required for sp_expressmaint',16,1) SET @ret = 1 GOTO CLEANUP END -- check sysadmin IF IS_SRVROLEMEMBER('sysadmin') = 0 BEGIN RAISERROR('The current user %s is not a member of the sysadmin role',16,1,@user) SET @ret = 1 GOTO CLEANUP END -- check database exists and is online IF @database NOT IN ('ALL_USER','ALL_SYSTEM') BEGIN IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
BEGIN
RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)
SET @ret = 1
GOTO CLEANUP
END
END

-- check @optype is valid
IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')
BEGIN
RAISERROR('%s is not a valid option for @optype',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END

-- check recovery mode is correct if trying log backup
IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))
BEGIN
RAISERROR('%s is not a valid option for database %s because it is in SIMPLE recovery mode',16,1,@optype,@database)
SET @ret = 1
GOTO CLEANUP
END
END

-- no log backups for system databases
IF @database = 'ALL_SYSTEM'
BEGIN
IF @optype = 'LOG'
BEGIN
RAISERROR('%s is not a valid option for the option ALL_SYSTEM',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
END

-- check that @backupfldr exists on the server
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
INSERT #exists
EXEC master.dbo.xp_fileexist @backupfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END

-- check that @reportfldr exists on the server
IF @reportfldr IS NOT NULL or @report = 1
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
DELETE #exists
INSERT #exists
EXEC master.dbo.xp_fileexist @reportfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END

-- check @dbretainunit is a vaild value
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')
BEGIN
RAISERROR('%s is not a valid value for @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)
SET @ret = 1
GOTO CLEANUP
END
END

--check @dbretainval is a vaild value
IF @dbretainval<1 BEGIN RAISERROR('%i is not a valid value for @dbretainval (must be >0)',16,1,@dbretainval)
SET @ret = 1
GOTO CLEANUP
END

-- check @rptretainunit is a vaild value if present
IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL
BEGIN
RAISERROR('%s is not a valid value for @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)
SET @ret = 1
GOTO CLEANUP
END

--check @rptretainval is a vaild value
IF @rptretainval<1 BEGIN RAISERROR('%i is not a valid value for @rptretainval (must be >0)',16,1,@rptretainval)
SET @ret = 1
GOTO CLEANUP
END

/***********************************
list of databases to process
************************************/

IF @database IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF @database = 'ALL_USER'
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id > 4
AND (@optype <> 'LOG' OR recovery_model <> '3')
ELSE
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id in (1,3,4)
END
ELSE
INSERT #databases(dbname) SELECT @database

/***********************************
INITIALIZE REPORT IF @report = 1
************************************/

-- generate report filename
SELECT @reportfilename = @reportfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
END + @jobstart + '.txt'

-- if no report just set @reportfilename to NULL
IF @report = 0 SET @reportfilename = NULL

IF @debug = 1
BEGIN
PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')
END

IF @report = 1
BEGIN
-- create report file
EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename
IF (@hr <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating log file',16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
-- set global flag to indicate we have created a report file
SET @filecrt = 1

-- write header
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ' + '[' + @user + ']'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output

IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
SET @output = 'Starting backup on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) = 'CHECKDB'
BEGIN
SET @output = 'Starting CheckDB on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) IN ('REINDEX','REORG')
BEGIN
SET @output = 'Starting Reindex on ' + convert(varchar(25),getdate(),100)
END

IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

/************************
BACKUP ACTIONS
************************/

IF UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB
IF UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX

-- if @delfirst = 1 we need to delete prior backups that qualify
IF @delfirst = 1 GOTO DELFIRST

-- this label is so that we can return here after deleting files if @delfirst = 1
DOBACKUP:

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- set backup start time
SET @start = GETDATE()

-- write to text report
IF @report = 1
BEGIN
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': ' +
CASE WHEN UPPER(@optype) = 'DB' THEN 'Full Backup '
WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '
WHEN UPPER(@optype) = 'LOG' THEN 'Log Backup '
END + 'starting at ' + CONVERT(varchar(25),@start,100)
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- backup subfolder
SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + @database + '\" MKDIR "' + @backupfldrorig + @database + '\"'
EXEC master.dbo.xp_cmdshell @execmd,no_output
SET @backupfldr = @backupfldrorig + @database + '\'

SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

/************************
FULL BACKUP
************************/

IF UPPER(@optype) = 'DB'
BEGIN

SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Full database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

/************************
DIFFERENTIAL BACKUP
************************/

IF UPPER(@optype) = 'DIFF'
BEGIN

SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' +
@backupfilename + ''' WITH DIFFERENTIAL' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' , ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure

SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Differential database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

/************************
LOG BACKUP
************************/

IF UPPER(@optype) = 'LOG'
BEGIN

SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH -- backup failure

SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Log backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

END

SET @stage = (@stage + 1)

FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur

/************************
VERIFY BACKUP
************************/

IF @verify = 1
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

SELECT @backupfilename = @backupfldrorig + @database + '\' + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

SET @start = GETDATE()

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +
CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

-- verify success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'
IF @debug = 1 PRINT @output

IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

--calculate verify runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Verify backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur
END

/************************
DELETE OLD FILES
************************/

-- we have already deleted files so skip to the end
IF @delfirst = 1 GOTO CLEANUP

-- this label is so that we can delete files prior to backup if @delfirst = 1
DELFIRST:

/************************
DELETE OLD BACKUPS
************************/

SET @datepart = CASE
WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@dbretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@dbretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@dbretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@dbretainunit) = 'MONTHS' THEN N'yy'
END

IF @debug = 1 PRINT '@datepart for backups = ' + @datepart

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

SET @backupfldr = + @backupfldrorig + @database + '\'
SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

-- load files in @backupfldr
IF @checkattrib = 1
SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
ELSE
SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'

IF @debug = 1 PRINT '@cmd = ' + @cmd

DELETE #files
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')

IF @debug = 1 SELECT * FROM #files

-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0

-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@dbretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based (include current backup that's not in #files)
BEGIN
IF @filecount>0
BEGIN
IF @dbretainval>1
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
END

SELECT @delcount = COUNT(*) from #files

END
ELSE
BEGIN
SELECT @delcount = 0
END
END

IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)

-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output

-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
ELSE
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END

FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END

-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

FETCH NEXT FROM dcur into @database
END

CLOSE dcur
DEALLOCATE dcur

-- clear temporary table and variables
DELETE #files
SET @cmd = ''
SET @delcmd = ''
SET @delfilename = ''
SET @datepart = ''
SET @filecount = 0
SET @delcount = 0
SET @cmdret = 0
SET @stage = @stage + 1

/************************
DELETE OLD REPORTS
************************/

DELREPORTS:

IF @rptretainunit IS NOT NULL
BEGIN
SET @datepart = CASE
WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@rptretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@rptretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@rptretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@rptretainunit) = 'MONTHS' THEN N'yy'
END

IF @debug = 1 PRINT '@datepart for reports = ' + @datepart

-- write to text report
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- load files in @reportfldr
SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(@databaseorig,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_' END + '*.txt"'

IF @debug = 1 PRINT '@cmd = ' + @cmd

INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL

IF @debug = 1 SELECT * FROM #files

-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0

-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@rptretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd

SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END

IF @debug = 1 PRINT STR(@delcount)

-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output

-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN

SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END

FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END

-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

-- update stage
SET @stage = @stage + 1
END
-- if we got here due to @delfirst = 1 go back and do the backups
IF @delfirst = 1
GOTO DOBACKUP
ELSE
GOTO CLEANUP

/************************
CHECKDB
************************/

CHECK_DB:

IF @optype = 'CHECKDB'
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

-- set backup start time
SET @start = GETDATE()

SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'
IF @debug = 1 PRINT 'DBCC Command : ' + @execmd

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP

END CATCH

SET @finish = GETDATE()

--calculate checkdb runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'CheckDB completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database

END

CLOSE dcur
DEALLOCATE dcur

-- delete reports
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
GOTO DELREPORTS
END

/************************
REINDEX/REORG
************************/

REINDEX:

IF @optype in ('REINDEX','REORG')
BEGIN

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN

-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
IF @optype = 'REINDEX'
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'
ELSE
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'

IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

-- set start time
SET @start = GETDATE()

-- all user tables
CREATE TABLE #tables(tablename sysname)
EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +
N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +
N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +
N'WHERE t.is_ms_shipped = 0 AND i.type>0')

DECLARE tcur CURSOR LOCAL FAST_FORWARD
FOR SELECT tablename FROM #tables ORDER BY tablename
OPEN tcur
FETCH NEXT FROM tcur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN

IF @report = 1
BEGIN
IF @optype = 'REINDEX'
SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table
ELSE
SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table

EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END

IF @optype = 'REINDEX'
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'
ELSE
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'

IF @debug = 1 PRINT 'Reindex Command : ' + @execmd

BEGIN TRY

EXEC(@execmd)

END TRY
BEGIN CATCH

SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE tcur
DEALLOCATE tcur
DROP TABLE #tables
GOTO CLEANUP

END CATCH

FETCH NEXT FROM tcur INTO @table
END

CLOSE tcur
DEALLOCATE tcur

SET @finish = GETDATE()

--calculate runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Index maintenance completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END

DROP TABLE #tables

SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database

END

CLOSE dcur
DEALLOCATE dcur

-- delete reports
GOTO DELREPORTS
END

/************************
CLEAN UP
************************/

CLEANUP:

DROP TABLE #files
DROP TABLE #exists
DROP TABLE #databases

-- if we encountered errors deleting old backups return failure
IF @delbkflag<>0
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END

-- if we encountered errors deleting old reports return failure
IF (@delrptflag<>0 AND @delbkflag = 0)
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END

-- if we created a file make sure we write trailer and destroy object
IF @filecrt = 1
BEGIN
-- write final part of report
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)
+ ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''

-- destroy file object
EXEC @hr=sp_OADestroy @file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
END

IF @report = 1
BEGIN
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
END

RETURN @ret
GO
/***********************************************/
/* Script Ends Here */
/***********************************************/

JavaScript: Add a prompt dialogue box to confirm the changes when user selects to “close” the InProgress work window.

2009-03-05T22:04:00.000-06:00

Here is the sample on how you can have a prompting dialogue box when user tries to exit the window with work in progress.

*************************************************
Sample Code: Just copy paste this code in your html file and it should work.
*************************************************
<script language="JavaScript">

var isChoice = 0;

function callAlert(Msg,Title){

txt = Msg;
caption = Title;
vbMsg(txt,caption)
alert(isChoice);

}
</script>

<script language="VBScript">

Function vbMsg(isTxt,isCaption)

testVal = MsgBox(isTxt,3,isCaption)
isChoice = testVal

End Function

</script>

<input onclick="callAlert('Do you want to save the changes to Document1?','Document1')" type="button" value="Exit">

*************************************************

Please contact me @ shasiza@gmail.com if you have any question or issues implementing this script.

Export DataGrid to a Excel Spreadsheet

2008-05-15T23:46:00.001-05:00

Here is the code to do that:

Steps:
1. Load your DataGrid any way you want.
2. Name your DataGrid to be grdDataGrid.
3. Add a new button on the page "Button1".
4. Copy and Paste following code, and you are done.

*****************************************************
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();

Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "test"));
Response.Charset = "";

Response.ContentType = "application/vnd.xls";

StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

grdDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}

public override void VerifyRenderingInServerForm(Control control)
{

}
*******************************************************

When you click on the button1, the following dialog window will show up.

I hope it helps, please feel free to contact me if you have any question/comments.

Thanks,
Sanjeev

I am back

2008-05-15T23:44:00.002-05:00

Hi Guys,

It's been long time since I have posted my next post here, I am back now and will start updating my blog again regularly, please provide your feedback, that helps alot.

Thanks,
Sanjeev

Get up to speed on Visual Studio 2008 Get up to speed on Visual Studio 2008

2007-10-23T11:42:00.001-05:00

Get up to speed on Visual Studio 2008 Get up to speed on Visual Studio 2008

Visual Studio 2008 (and the .NET Framework 3.5) is set to release to manufacturing in a few weeks (November timeframe). I've been working with it for about 6 months myself, and once you get pass the BETA "issues" it is a nice upgrade over Visual Studio 2005 in a variety of areas.

news features:

* Multi-targeted frameworks
- Within VS2008, the IDE gives you a choice of working with the .NET 2.0, 3.0, or 3.5 framework
- Which means that I don't have to have Visual Studio 2005 (for backward compatibility) and Visual Studio 2008 installed to get the new features
- The ability to switch frameworks overtime, so that I can start with .NET 2.0, for example, and as my company/customer approves .NET 3.0/3.5, I can switch my project to the new framework, and Visual Studio 2008 will automatically give me new choices (UI, Controls, Coding options).

* LINQ (Language INtegrated Query)
- The ability to treat almost any object as data
- One querying language for Objects, XML, SQL Data, etc.
- and more. You should really watch the video on this one

* New features for the Web Developer, among them
- Split View in the IDE, which will synchronize the HTML view and the GUI view, so that you can work in either and see the results synchronized in both panes.
- New CSS Support. Features that you've seen in higher end HTML editors, but now in Visual Studio, so that you don't have to switch tools during Development

* Full ASP.NET Ajax integration
* And much more

Click here to see the Full Video Clips: http://msdn2.microsoft.com/en-us/vstudio/bb655906.aspx

How to load an Excel spreadsheet into an ADO.NET DataSet

2007-09-27T14:27:00.001-05:00

You can do this using following code:

DataSet objDS = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName.Replace("\\", "\\\\") + ";" +
"Extended Properties=\"Excel 8.0;\"";
objOLE = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
objOLE.Fill(objDS);

The above code snippet works great, but you must know the name of the worksheet (Sheet1 is the default for Excel). But what if you don't know the Worksheet name? What if you just want the first sheet? Then, you must use the Excel objects to get it:

1#region AnalyzeSpreadsheet
2public void AnalyzeSpreadsheet(string strFileName)
3{
4 //Excel variables
5 object con_true = true;
6 Excel.ApplicationClass objExcel = null;
7 Excel.Workbook objBook = null;
8 Excel.Worksheet objSheet = null;
9 try
10 {
11 //Create new instance of Excel Application
12 objExcel = new Excel.ApplicationClass();
13 //Set some options
14 objExcel.DisplayAlerts = false;
15 objExcel.ScreenUpdating = false;
16 objExcel.Visible = false;
17 objExcel.UserControl = false;
18 //Open spreadsheet
19 objBook = objExcel.Workbooks.Open(strFileName, Type.Missing,
20 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
21 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
22 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
23 //Find the 1st worksheet
24 objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1);
25 if (objSheet == null)
26 throw new Exception("Worksheet #1 not found!");
27 else
28 {
29 //Do something...
30 }
31 }
32 catch
33 {
34 //Handle exception
35 }
36 finally
37 {
38 ReleaseComObject(objSheet);
39 objSheet = null;
40 objBook.Close(con_true, strFileName, null);
41 ReleaseComObject(objBook);
42 objBook = null;
43 objExcel.Workbooks.Close();
44 objExcel.Application.Quit();
45 ReleaseComObject(objExcel);
46 objExcel = null;
47 }
48}
49#endregion
50#region ReleaseComObject
51private void ReleaseComObject(object o)
52{
53 Int32 i = 0;
54 Int32 j = 0;
55 try
56 {
57 for (i = 1; i <= System.Runtime.InteropServices.Marshal.ReleaseComObject(o); i++)
58 {
59 j = System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
60 }
61 }
62 catch
63 {
64 }
65 finally
66 {
67 o = null;
68 }
69}
70#endregion

Catch Web Browser Closing/UnLoad Event

2007-08-03T13:31:00.000-05:00

There is a way to catch browser closing event. The most elegant way to catch browsing closing event in IE and FireFox is the following script:

< html>
< head>
< title>< /title>
< /head>

< body onbeforeunload="alert('Closing');">
< /body>
< /html>

This Script works in IE 7 and in Firefox.

How to Get the IP address of the Host PC.

2007-07-09T12:02:00.000-05:00

This C# code snippet allows you to retrieve the IP address of the Host PC.

using System.Net;
......
......
......
String strHostName = Dns.GetHostName();
IPHostEntry ipEntry = Dns.GetHostEntry(strHostName);
IPAddress[] addr = ipEntry.AddressList;

ERROR: Unable to start debugging on the web server. you do not have permission to debug the application. the URL for this project is in the Internet..

2007-07-05T15:00:00.000-05:00

If you are running .NET Framework 1.1 and 2.0 together on the same machine, you might get this error when you try to run .NET 1.1 web application in debug mode:

"Unable to start debugging on the web server. you do not have permission to debug the application. the URL for this project is in the Internet zone."

It's easy to fix, just follow these steps:
1. Go to "Administrative Tools" and open "Internet Information Services(IIS)" .
2. Right click on "Default Web Site" and choose "Properties" option.
3. Select ASP.NET tab.
4. Look for ASP.NET version drop down and Change ASP.NET version from 2.0.xxx to 1.1.xxx.
5. Click Apply button.
6. Click Ok button.
7. Restart your IIS.

Try now opening your web project, there should not be a problem.

Drag and Drop ASP.NET 2.0 Web Parts in Firefox with ATLAS

2007-06-21T22:50:00.000-05:00

This Article is for the users who are still using ATLAS and have not yet moved to AJAX. The known issue in ATLAS is that there is no direct drag and drop support for FireFox and Netscape Browsers, in this Article I have explained how you can make it possible for Firefox browser.

To add this functionality to your existing pages, do the following:

1. Copy the "Atlas" run-time assembly (Microsoft.Web.Atlas.dll) from its installation folder to the application's Bin folder.

2. Copy these elements as children of the element:


<configSections>

<sectionGroup name="microsoft.web" type="Microsoft.Web.Configuration.MicrosoftWebSectionGroup">

<section name="converters" type="Microsoft.Web.Configuration.ConvertersSection"/>

</sectionGroup>

</configSections>



<microsoft.web>

<converters>

<add type="Microsoft.Web.Script.Serialization.Converters.DataSetConverter"/>

<add type="Microsoft.Web.Script.Serialization.Converters.DataRowConverter"/>

<add type="Microsoft.Web.Script.Serialization.Converters.DataTableConverter"/>

</converters>

</microsoft.web>

3. Copy these elements as children of the <system.web> element:


<pages>

<controls>

<add namespace="Microsoft.Web.UI" assembly="Microsoft.Web.Atlas" tagPrefix="atlas"/>

<add namespace="Microsoft.Web.UI.Controls" assembly="Microsoft.Web.Atlas" tagprefix="atlas"/>

</controls>

<tagMapping>

<add tagType="System.Web.UI.WebControls.WebParts.WebPartManager"

mappedTagType="Microsoft.Web.UI.Controls.WebParts.WebPartManager"/>

<add tagType="System.Web.UI.WebControls.WebParts.WebPartZone"

mappedTagType="Microsoft.Web.UI.Controls.WebParts.WebPartZone"/>

</tagMapping>

</pages>

4. 

<httpHandlers>

<remove verb="*" path="*.asmx"/>

<add verb="*" path="*.asmx" type="Microsoft.Web.Services.ScriptHandlerFactory" validate="false"/>

</httpHandlers>

<httpModules>

<add name="ScriptModule" type="Microsoft.Web.Services.ScriptModule"/>

</httpModules>

5. Add the Atlas ScriptManager control to the page:
<atlas:ScriptManager ID="ScriptManager1" runat="server" />

What is Digg?

2007-06-12T12:13:00.000-05:00

Digg is a user driven social content website. Ok, so what the heck does that mean? Well, everything on Digg is submitted by our community (that would be you). After you submit content, other people read your submission and Digg what they like best. If your story rocks and receives enough Diggs, it is promoted to the front page for the millions of visitors to see.

What can you do as a Digg user? Lots. Every person can digg (help promote), bury (help remove spam), and comment on stories... you can even Digg and bury comments you like or dislike. Digg also allows you to track your friends' activity throughout the site — want to share a video or news story with a friend? Digg it!

Want to know more how the site works? Read How Digg Works

if you decide to post a link on Digg, here's the snippet of code you need to add to your website to add the Digg Link to your website:

<script type="text/javascript">
digg_url = "digg url to your link";
<script>

What is AJAX?

2007-06-04T15:53:00.000-05:00

If you're not familiar with AJAX, here's the Wikipedia definition.

ASP.NET AJAX is a new Web development technology from Microsoft that integrates cross-browser script libraries with the ASP.NET 2.0 Web application framework. With ASP.NET AJAX, developers can quickly create pages with sophisticated, responsive user interfaces and more efficient client-server communication by simply adding a few server controls to their pages.

Create AJAX Animated GIF

2007-06-04T15:49:00.000-05:00

If you're not familiar with AJAX, here's the Wikipedia definition.

Basically, it's the process of updating small portions of a webpage instead of refreshing the entire page. This presents a much more enjoyable user experience and speeds up the website. When a portion of a webpage is being updated, you see an animated image indicating so. While working on AJAX enabled websites, I found the need to create custom animated images. These are not easy to create...until now.

Visit the following link and you can create your own animated AJAX image in seconds! http://www.ajaxload.info

10 Reasons That Projects Fail (And How to Avoid Them)

2007-06-02T21:53:00.000-05:00

courtsey: www.certmag.com
October 2005 - David Garrett

It’s called the Chaos Report, and you need to know about it.

Every year, The Standish Group (www.standishgroup.com), which tracks the success rates of IT projects, issues this report to an eager crowd of analysts, managers and techies who wonder why so many IT projects end in failure. And while the numbers vary by year, they’re often dismal: A recent Chaos Report claimed that “unqualified project successes” are well below 50 percent, and sometimes as low as a third of all projects tracked. Even worse, out-and-out failures, which are defined as projects that are abandoned midstream, can reach as high as 15 percent. Projects that simply have time problems, cost problems or poor execution can make up as much as half of all IT projects per year.
These numbers are bad news. Now more than ever, the world depends on IT—but IT and the people who run it don’t always have a dependable record. This leads to the simple but vexing question of why projects fail—and just as important, what you can do about it.
Projects fail for all kinds of reasons, from insanely low budgets to unwisely tight schedules. But some reasons pop up again and again, everywhere from mom-and-pop shops to the Fortune 500, so they’re listed here, along with advice on how to avoid them.
Scope, Scope and More Scope Far and away, scope issues are the number-one reason that projects fail. “ Scope” is simply the project manager’s term for the sum of work to be done on a project—what it entails, what it doesn’t and what its end products are. All too often, projects that fail have poorly defined scopes.
Here’s an example: You’re told to build a simple contact management system for your corporate intranet, and you’re given a list of features, a budget and a basic timeline. You build the system to spec, but as you’re building it, your users, impressed with your work, think of new features to add. It’s called “scope creep”—the insidious way that a project’s end-products get larger by degrees. Three weeks into the project, you find yourself bogged down by details that no one ever mentioned at the start. As a result, you miss your deadline, get slapped on the wrists and buy a large bottle of Jack Daniels.
Solution: Write a scope document—a list of what you will and won’t do by a project’s end. And if you’re really worried about scope creep, write an out-of-scope document, in which you explicitly state what you won’t add, emend or deliver. Then enforce it like the law.
Egos (Big Ones) You won’t find this reason on an official list of project pitfalls, but it’s one of the chief reasons for project failure. People have egos, and all too often they’re loath to swallow their pride, admit they’re wrong and move on. Feelings get bruised and people get mad, and as a result, projects get left undone.
Solution: Remember that a project manager is always a diplomat. True, there are some projects that demand a diplomat of Kofi Annan’s caliber, but even a small amount of courtesy, compromise and common sense can go a long way toward getting things done when politics run amok.
Bad Language This doesn’t mean curse words. It means poor communication. All too often, we speak and write in ways that don’t make things clear. Or we simply don’t speak or write enough in the first place.
Remember that taking a project from coal to diamonds involves keeping all the miners in the loop. You need to have a clear statement of mission, a well-defined scope, daily or weekly status reports and enough meetings to keep the team organized (but not so many that it steals their time). You also need a simple and direct way to learn about problems as soon as they occur. Without it, you’re flying blind—and your project is bound for failure.
Solution: If you’re doing a large project, write out a brief communications plan. Who needs to be kept in the know? Who gets copied on e-mail? When will you meet, and why? Is there a chain of command? And so on. Pin down these answers before you get started to avoid problems at the end.
Bad Budgets This one’s easy: Too many projects don’t have the money they need to succeed. Bootstrapping is a part of life, but a miserly budget can kill a project before it even begins.
Solution: Make sure you know what a project will cost before you start it—the best way is to write a line-item budget—and factor in a safety cushion for cost overruns. Then fight for your budget with the bean counters and decision-makers who control the purse strings. A few extra dollars can mean the difference between a glowing success and something that people whisper about behind closed doors.
Forgotten Users Too many IT projects commit this sin in spades. Remember that an IT project is built for the sake of its users, and not for the people who built it. All too often, the coders, engineers and network administrators who run a project speak with users at two points in the process only: the beginning, when they ask what users want, and the end, when they present the goods.
The problem, of course, is that users don’t always know what they want until they see it, or they don’t explain what they want very clearly. If you wait until a project is over to show users the results, you’re simply asking for trouble.
Solution: Involve your users at every phase of the project. If you’re building a Web site, get them to sign off on the site map. Get their input on every color, design and word of copy you write. Ask for their opinions weekly, if not more often, and you’ll have a better chance for a happy ending.
The Missing Bigwig Projects are merely the people who run them. Whether or not a project works depends on the team that’s assembled to drive it, and that makes politics and human nature an inherent part of project management.
Since people respond to authority, you need a good project sponsor: an executive with the power and clout to force people to work and free up the dollars your project needs. Some call this “ management buy-in.” Some call it “ management by force.” But whatever you call it, make sure you do it—a project that’s sponsored by a high-end executive is a project that’s less apt to fail.
Solution: Don’t be afraid to ask an executive to chair your project. If his name is tied to the project’s success, he’s more likely to exert the effort that’s needed (and perhaps knock some heads together) to get things done in a pinch.
Lack of Planning It seems easy, but it’s a lesson of such fiendish complexity that people forget it all the time: If you want your project to succeed, plan it thoroughly.
Remember that planning is both art and science, and entails more than a simple calendar. There’s risk management, change management, work breakdowns, documentation and more. So be sure to write up your plan in as much detail as possible. (The mere act of writing makes you view things in a light you may have ignored.) And bear in mind that the days of “cowboy coding,” in which you get to work as soon as you get a concept, are over.
Solution: Write out a project charter, a mission, a scope statement and a communications plan. Invest in a few good project management books for tips and advice on the lost art of planning and how to do it right.
Good Old Force Majeure Of course, not every project fails because of bad execution. There are things beyond our control—the economy, for instance, or simply a flood that wipes out your data center at two in the morning.
Force majeure is the legal term for an act that’s beyond a person’s control. Some call these acts of God. Some people blame the stars. Whatever the reason, it’s fair to say that some projects fail because of calamities beyond our control.
Solution: While you can’t walk on water and stop floods in their tracks, you can engage in a bit of risk planning to minimize your exposure to force majeure. Think of the disasters that could strike when you’re least ready for them, then make arrangements now. Increase your backups, build mirrors and redundant systems, write out a full-scale disaster recovery or business continuity plan. You’ll be glad you have one.
Dumb People This could be said more gently. It could even be said more subtly. But why bother? We all know that projects fail because some of the people who run them aren’t as swift (or merely as focused) as they should be.
Of course, we’re not immune from this ourselves. Remember that patience—that intangible asset that’s in smaller supply every day—is often a project’s best chance for success.
Solution: Choose your team wisely. Find the best people you can with the money you have and the choices you’re given. And invest in training as if it were a blue-chip stock: Choose it wisely, and it does nothing but increase your value.
The Technician-Manager Last but not least, don’t forget that good technicians don’t always make good managers—or good project mangers, for that matter.
You may be a dazzling writer of database systems. You may even know the rigors of ASP and PHP like some people know their ABCs. But can you pull off a project on time?
Remember that good management, and good project management, take a subtle mix of people, planning and communication skills. It takes a diplomat’s touch and an expert’s knowledge of the field. And it’s a skill that few of us are born with. The fact that you’re a whiz with event handlers or ActionScript doesn’t mean you can make the trains run on time—though you can learn to, and even excel at it with effort.
Last, remember that “project failure” and “project death” are very different things. In the end, failure is merely delay, not defeat, and if you take these lessons to heart and watch your projects like a hawk, you’ll find that success is closer than you think.

Send Email using ASP.NET 2.0

2007-05-30T15:20:00.000-05:00

Introduction

A common thing you do with Web Development is to send emails, I will show you here how to send emails using ASP.Net v 2.0, and also using the authentication for a SMTP server.

The namespace for sending mail within ASP.Net v 2.0 is not System.Web.Mail anymore this is replaced by System.Net.Mail

This article explains how to use System.Net.Mail namespace to send emails.

Technical Details:

The Send mail functionality is similar to Dotnet 1.1 except for few changes.

1. System.Net.Mail.SmtpClient is used instead of System.Web.Mail.SmtpMail (obsolete in Dotnet 2.0).
2. System.Net.MailMessage Class is used instead of System.Web.Mail.MailMessage (obsolete in Dotnet 2.0)
3. The System.Net.MailMessage class collects From address as MailAddress object.
4. The System.Net.MailMessage class collects To, CC, Bcc addresses as MailAddressCollection.
5. MailMessage Body Format is replaced by IsBodyHtml

Code Sample:

using System;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Net.Mail;

public partial class _Default : System.Web.UI.Page
{

#region "Send email"

protected void btnSendmail_Click(object sender, EventArgs e)
{

// System.Web.Mail.SmtpMail.SmtpServer is obsolete in 2.0
// System.Net.Mail.SmtpClient is the alternate class for this in 2.0
SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();
try
{
MailAddress fromAddress = new MailAddress(txtEmail.Text, txtName.Text);

// You can specify the host name or ipaddress of your server
// Default in IIS will be localhost
smtpClient.Host = "localhost";

//Default port will be 25
smtpClient.Port = 25;

//From address will be given as a MailAddress Object
message.From = fromAddress;

// To address collection of MailAddress
message.To.Add("admin1@yoursite.com");
message.Subject = "Feedback";

// CC and BCC optional
// MailAddressCollection class is used to send the email to various users
// You can specify Address as new MailAddress(admin1@yourdomain.com)
message.CC.Add("admin1@yourdomain.com");
message.CC.Add("admin2@yourdomain.com");

// You can specify Address directly as string
message.Bcc.Add(new MailAddress("admin3@yourdomain.com"));
message.Bcc.Add(new MailAddress("admin4@yourdomain.com"));

//Body can be Html or text format
//Specify true if it is html message
message.IsBodyHtml = false;

// Message body content
message.Body = txtMessage.Text;

// Send SMTP mail
smtpClient.Send(message);
lblStatus.Text = "Email successfully sent.";
}
catch (Exception ex)
{
lblStatus.Text = "Send Email Failed.
" + ex.Message;
}
}

#endregion

}