PDA

View Full Version : Fun with SQL Scripts



blankenbush
06-29-2006, 02:35 PM
KEYWORDS: RAISERROR; SQL Scripts; SQLRTComponentInstall; HACK

If you have several SQL Scripts to run and you would like to return your own errors you may have some problems getting things to work. I have two scripts I use im my project. The first one does a version check and the second one does a database upgrade. The second script should never run if the version check fails.

The problem is that there is no way to raise a 'user defined' error from an Installscript Project. Also, I want to fail the first script but use a more friendly message. I may even decide not to abort the install.

So here is what I do...and there are two parts to this HACK.

In the first script put your check:

if not exists ( SOME SQL HERE )
RAISERROR(50000, 16, 1) WITH SETERROR
GO

This will cause your script to fail because message 50000 won't be found. If for some reason you have message 50000 defined then pick some other number that will fail. The message that is retuned to the Installscript Project is "RAISERROR could not locate entry for error 50000 in sysmessages." Now all you have to do is look for '50000' in the returned error message. This is done in the second part of the HACK. The OnSQLComponentInstalled function.

NOTE: Make sure that your have On Error, Abort Installation or
On Error, GoTo Next Script set on the Runtime Tab.

Next modify the OnSQLComponentInstalled function. I put some intervention code just prior to

{...}

// Do some intervention
if (StrFind (szError, "50000") > 0) then
Sprintf(szMessage, "Your message here", szScriptName);
endif;

// Display error
MessageBox( szMessage, SEVERE );

// Close the current dialog.
EndCurrentDialog();

// Rollback SQL Scripts
SQLRTDoRollbackAll();

{...}

You could also set a global variable here and check it before SQLRTComponentInstall is executed.

{...}

if ( bCheck = false ) then
nResult = SQLRTComponentInstall( szComponent );
endif;

{...}

This should give you the general idea and there are certainly a lot of other things you can do with this technique.

PetSch
07-11-2008, 09:42 AM
... but it does what we want!

Don't you agree with me, that the whole SQL part of the InstallShield script language is a total desaster? How can someone design a scripting language in such a manner?!

Anyway, thanks for the hint.
Peter