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

CLR to create a directory / folder

The reason for this is the ridiculous security settings required to use xp_cmdshell - specifically having to use sp_xp_cmdshell_proxy_account . The issue is that this requires a domain login and password, which is problematical if your account passwords change regularly.

The way around this is to use a CLR to create the folder. The code for the CLR (assuming you know how to create a CLR project) is as follows :


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]

public static void cproc_createfolders(string folderName)
{
//Check whether folder exists
if (!System.IO.Directory.Exists((folderName)))
{
System.IO.Directory.CreateDirectory(folderName);
}
}
};

Simple enough. Installing it isn't. As this is accessing external to SQL Server, you need to set up the necessary permissions. There are two ways of doing this, via a login or by setting the database to be trustworthy. I used the latter, as this is a database behind a firewall, internal use only so felt it was OK. If you do not do this, you will get an error along these lines :

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

So :

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

Next is to install the assembly. This needs to specify that it is for External Access:


CREATE ASSEMBLY CreateDIR
FROM 'C:\CLR\myCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO


And finally a stored procedure to allow access from SQL :


CREATE PROCEDURE dbo.cproc_createfolder
(
@strPath Nvarchar(1024)
)
AS EXTERNAL NAME CreateDIR.StoredProcedures.cproc_createfolder



Access to this routine is via dbo.cproc_createfolder.

Took a lot of searching to find this, so I think it is only fair to credit Vadivel's Blog.