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.

Save/default parameters for SSRS

The following link is a rather useful way to save parameters and recall by user in SSRS.

Handling multi value parameters from SSRS in a stored procedure

Actually quite easy, using this function :

CREATE FUNCTION [dbo].[fn_SplitIN]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
/* ========================================================================
fn_SplitIN

Function to return a table for joining for a multi valued parameter from SSRS

How to use :

SELECT cols
FROM mytab
WHERE cols IN (SELECT Item FROM fn_SplitIN(@multiValueParam,','))

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 23/03/2009 Brian Jones Initial Version
=========================================================================== */

RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)

SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ',@delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END

RETURN
END

Conditional Where Clauses

Excellect article by Erland Sommarskog about the various ways of doing SQL conditional where clauses. What sets this apart is that Erland has comparative performance figures, showing what way is best. Also kept up to date.