Chris McKenzie's Blog

This week has been an exercise in working around holes in other people's tools.

I am using InstallShield 10.5 Pro to create an msi to deploy an intranet ASP .NET application and the associated database.  I want the installation to be smart enough to remove an existing installation of the database if it is already present, and rebuild it from scratch.  Alternately, I'd like to upgrade an existing database.

Here are some of the issues I've run in to.

1) InstallShield fails to execute sp_droplogin correctly. Everything else runs correctly but this one thing. I end up having to manually delete the login via Enterprise Manager just to test my installation again. argh!  I tested the script via osql and isql and  Query Analyzer and it runs flawlessly.  The command is simply not being executed from the installer.  Opening a customer support issue with them on this issue has been pointless: the advice I got was to create a custom action and run my script using osql from the command line.  Then why did we pay $ABUNCH for this tool that supposedly will allow us to build and execute our scripts in an easy manner.  On top of everything, I was unabled to find a single tutorial or sample project that illustrates a database installation.

2) Here's a neat one from SQL Server. At the beginning of my upgrade installation, I create a function "dbo.fnVersion()" that returns the current database version.  In this way, I can wrap my SQL commands in an IF block that checks the version before executing.  A previous developer had created a bit column called bDeleted on just about every table in the system. Instead of deleting rows from the tables on a DELETE command, he was just setting a flag.  I can imagine situations in which this might be a good idea, but it isn't necessary in our system.  So, as a first step toward removing the column, I've written SQL like this:

IF dbo.fnVersion() = '1.0.0'
DELETE
FROM MyTable
WHERE bDeleted = 1
GO

IF dbo.fnVersion() = '1.0.0'
ALTER TABLE MyTable
DROP COLUMN bDeleted
GO

This logic is fine the first time you run it, but Query Analyzer screams at you if you run it again because bDeleted no longer exists a a column on that table.  Nevermind that it's encapsulated in an IF Block.  To get this to run correctly I've had to modify the script as follows:

IF dbo.fnVersion() = '1.0.0' BEGIN
   DECLARE @SQL varchar(500)
   SET @SQL = '
   DELETE
   FROM MyTable
   WHERE bDeleted = 1'
   EXEC (@SQL)
END

IF dbo.fnVersion() = '1.0.0'
ALTER TABLE MyTable
DROP COLUMN bDeleted
GO

There are two HeadScratching items in this: the first is the fact that the ALTER TABLE statement doesn't have a problem with the fact that the column name doesn't exist.  The second is that if I'm dropping a table that doesn't exist, Query Analzyer doesn't have any problem with that. (e.g., IF dbo.fnVersion() = '1.0.0' DROP TABLE MyUnNecessaryTable GO.

Argh!  Which is it? Am I supposed to have valid references to everything in my scripts or not?

3)  Now I'm back to Installshield 10.5 Pro again.  This time it has to do with Zipcodes.  I found a table of Zipcodes and wanted to include them as part of my installation.  I used Excel to generate a SQL Script of INSERT statements for the 42,000+ ZipCodes in the file.  I included that script in my Installshield project and ran it.  At first everything looked fine.  I checked the table and sure enough it had Zipcodes in it. YAY!  Further investigation however revealed that only the first 455 zipcodes had been installed. ????  I checked my script thinking that perhaps there was a syntax error at that line, but no--everything was okay. Not even sure of the reasoning that was pushing me in this direction, I added a "GO" statement to line 455 of the script and ran the installer again.  This time I had 910 rows.  hmmm. 910 / 2 = 455.  ARGH!!! Obviously they're buffering 455 lines of SQL Commands to execute in one shot. That's probably a good idea, but it'd be nice if their application was smart enough to resume where it left off.

I had to go back to Excel and generate logic to insert a GO statement every 400 rows or so in order to get around this.

Note to self: make sure features I've added work as advertised and follow a consistent use model.

Okay, here's one I'm sure a bunch of you have encountered. Where can I find a free list of zipcodes and associated city/state information?

As an aside, why do you think that such information, presumably in the public domain, is so hard to come by?

Our small .NET Development team is currently using Visual SourceSafe as the SCM of choice, but the rest of my company uses PVCS.  I have installed PVCS 7.5 on my test machine with a view to migrate our SourceSafe installation to PVCS. So far, my efforts have been in vain.  Opening projects from PVCS has basically resulted in locking my machine up.  Adding projects to PVCS from Visual Studio has resulted in a crashed IDE. When I attempted to modify the project bindings in Visual Studio, the Visual Studio bindings editor showed that I had established a valid connection, but on closing the editor I got an "unspecified error."  I haven't had any of these difficulties with PVCS.

I tried using the vss2pvcs.exe utility provided with PVCS, but it failed to copy all of my files, and some of the ones it did copy I'm not able to retrieve to my disk.  So that ended up being a blind alley.

My company places a high value on everyone using the same source control repository, so I've really been trying to make this work  I've already burned a week on this.  Is anyone else using PVCS 7.5 with Visual Studio? Has anyone else had to migrate from SourceSafe to PVCS?  What pitfalls did you come across? How did you get around them?

I'll be posting your answers to these questions on this blog.

I'm feeling a little overawed by the task that's before me right now.  I have until the end of the month to develop two installation projects for our ASP.NET/SQL SERVER software, one as an upgrade, and the other for a fresh installation.  I've been given the InstallShield Pro package to do this with.  Aside from the web application, I have about 25,000 lines of SQL Server Scripts to run for the upgrade piece.  I've never developed with InstallShield before, so I'm just a wee bit intimidated.

...

I don't guess it'll get done if I don't get started.  :)  Taking a deep breath... dive...

Here is more of my conversation with Peter Torr on VSTO Security:

ME: The document should prompt the user [that the code isn't trusted], warn them of the dangers of running untrusted code, ask if the the assembly should be granted FullTrust [...] and then set the appropriate security policy automatically.

PT: Great in theory (that's what VBA's "Medium" security mode does) but horrible in practice. Outlook warned users about the dangers of opening VBS or SCR or EXE attachments, but we still had all those e-mail viruses. Now all those extensions are just blocked by Outlook.  IE used to warn people before auto-installing ActiveX controls; now you have the "Gold Bar" experience (which is an interesting solution when combined with Authenticode signed code, but not a good enough mechanism by itself). Unfortunately, users just don't make good security decisions, especially when it is interrupting their workflow and they just want to "get their job done." Modal prompting is a BAD idea when it comes to security (emphasis added).

As for the setup project idea... yes, that is one way to do it, although unfortunately we don't ship with a setup project out-of-the-box. (One drawback to local installs is that you can no longer update the assembly in-place if you find a bug).

I really want to comment on the "users just don't make good security decisions" part.  There is a part of me that response "they deserve what they get if they don't make good security decisions."  However, there is also the part of me that recognizes that a company with Microsoft's visibility is often blamed for things that aren't their fault. I've seen a degree of that even in my little corner of the world.  Often when I'm supposed to be interoperating with 3rd party components or tools and the 3rd party tool fails, the failure has been blamed on my app solely because my app is the one that is visible.  Nobody interoperates with more 3rd party software than Microsoft.  Even though Microsoft isn't technically at fault when a user makes a dumb security decision, Microsoft might be perceived to be at fault by that user. Right or wrong, Microsoft has to guard against that perception.  So, in that light, I can sort of see where Peter Torr is coming from.

That said, I still believe there must be a simpler way to manage VSTO security.  I don't know what it is yet, but I'll put more mental effort to it when next I have to work with VSTO.

If I haven't said so already Peter, thanks for the time you spent discussing this issue with me.

Graphically, all I can say is wow!  I've never seen a game so pretty.  When the game first put me in control, I still thought I was in the movie.  The quality of the graphics in-game is as good as that in the movies.  Wow.

Don't know about game-play yet.  We'll see.