Setting up DB Mail on SQL 2005

The following code will set up all the necessary parts for SQL 2005 DBMail. Note that you will need to also enable Database Mail in Surface Area Configuration for Features. Also the variables need to be set to suit the installation.

USE msdb

GO

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = 'MyMailProfile';
SET @AccountName = 'MyMailAccount';
SET @SMTPAddress = 'my.smtp.server.address';
SET @EmailAddress = 'myemail@myorg.com';
SET @DisplayUser = 'My Real Name';

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

To send an email :

EXEC msdb.dbo.sp_send_dbmail
@recipients =N'someone@someaddress.com',
@body = 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = 'MyMailProfile'

Search Cache Plans Stored Procedure

Excellent article in SQL Server Central which inspects cached plans looking for poor performance. Full credit to Ian Stirk (Ian_Stirk@yahoo.com) for a great, easy to use and very useful routine.

Code is as follows :


CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS
/*----------------------------------------------------------------------
Purpose: Inspects cached plans for a given string.
------------------------------------------------------------------------

Parameters: @StringToSearchFor - string to search for e.g. '%missingindexes%'.

Revision History:
03/06/2008 Ian_Stirk@yahoo.com Initial version

Example Usage:
1. exec dbo.dba_SearchCachedPlans '%missingindexes%'
2. exec dbo.dba_SearchCachedPlans '%columnswithnostatistics%'
3. exec dbo.dba_SearchCachedPlans '%tablescan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

-----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY cp.usecounts DESC
END

SQLIO resources

Just a few useful links for SQLIO / SQLIOSim analysis :

Kevin Kline Blog

Technet SQL IO Basics

How to use SQLIOSim

VS 2008 Excel Addin tab issue (ribbon designer) - incorrectly merging custom tab with 3rd party tab

The ribbon designer is a great tool, however it seems to have a serious flaw. The issue is that if you have already got a custom tab installed (e.g. the Team tab from Team Foundation Server), then any tab created using the designer is merged with the existing custom tab. Annoying to say the least.

The issue seems to be with the way that tab is identified to Office. The property group that defines this is ControlId, which is a little confusing. This property group contains two properties, ControlId and OfficeId.

ControlId is a drop down with 2 options : Office or Custom. This is where the confusion begins. As this is an Office addin, one would assume that the ControlId should be set to Office, and the OfficeId should be set to a distinct name. However, if you change the OfficeIdfrom TabAddIns , your custom ribbon will not appear. And if you leave it as TabAddIns, then your custom ribbon will be merged with any other custom addin that you happen to have installed, e.g. the Team Foundation "Team" menu.

In fact you must set ControlId to Custom. This in turn alters OfficeId to CustomId. You then enter your distinct name in the (Name) property. Yep this is a little strange ! This will automatically set the CustomId and therefore the tab's Id within Excel. And when it is installed, it will not merge with any existing tabs.

I think the reason for this can be explained if you export the ribbon to XML. You will see that the tab has a property of IdMso = . It seems that this can only ever be set TabAddIns. Any other setting does not work. However, if you have set the ControlId to Custom, then in the XML the tab now has a property of Id = , and this works whatever the Id is. This matches how the XML used to look under VS 2005.

I'll put this down as a strange and confusing "feature" ;)

Deploying Office 2007 Addin with VS 2008

Following on from my post last year about deploying from VS 2005, I have since upgraded the product to VS2008 (nicer development environment for Office addins for a start!). Unfortunately, the installation project I created for VS 2005 does not seem to be working after it's been converted to 2008. As per usual, the error reported from Excel 2007 is best described as minimal, so I have decided to start this all again from scratch.

The MSDN pages have been updated for .NET 3.5 and can be found here.

I am currently working my way through this. First stop is that I have had to recreate the addin project itself from scratch, as this did not convert at all. I am also changing the name of the assembly, just in case some weird caspol issue is getting in the way.

Next stop is creating a new installation routine.

Following the instructions in the MSDN link above produced a perfectly working installation.

NOTE
There are considerable changes between the VS2005 and VS2008 installation projects for Office addins, guess I should have known! (it's not any easier though).

Bootstrapping GAC in ClickOnce Visual Studio 2005

One of the main shortcomings of ClickOnce is the inability to install GAC related items, and to execute addition installation requirements such as creating registry settings.
To accomplish this you must create a custom Bootstrap which is installed as part of the pre-requisites. This is easier than it sounds. In essence :

Create a project which includes all the references required.
Create an installation project that loads the references into the GAC (and does other pre-requisite actions)
Copy the msi to the boot strapper folder for Visual Studio (e.g. C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages or C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages)
Create a product.xml to identify the msi.

*Update - make sure you create a subfolder to hold the language specific EULA. Without this it will break!

Alter the Prerequisites in the click once publish tab to include this new project.

There are some nice articles on this, not least some example code from dasBlonde.

Also :

Installsite bunch of links
MSDN bootstrap manifest generator

High I/O high CPU from fragmented indexes

Interesting blog post that goes a long way to describing the mechanics involved when a CLUSTERED index is poorly designed