Re: [WiX-users] SQL Extended stored procedures

Albert van Peppen Tue, 27 Jun 2006 08:23:21 -0700

Great! Thank you verrrrry mucho!

The [D4WSQLSERVER] property was indeed unknown during uninstall; after reading it from the registry that part was solved (eg. the error that is ;) )
And the SqlDb part did the other trick ..
I also dropped some more unneccesary stuff to make it more general and easier to read.

So the code now looks like: (so anyone who needs it can use the idea)

<?define guidDatabaseComponent = "YOUR-GUID-HERE-ETC..." ?>
<?define strMyXPProcDll = "C:\Program Files\MyDir\xp_MyProc.dll" ?>

<DirectoryRef Id="TARGETDIR">
<Component Id="DatabaseComponent" Guid="$(var.guidDatabaseComponent)">
<Condition>MYSQLSERVER <> ""</Condition>

<SqlString Id="UnRegister_xp_My_Func1"
     Sequence="1"
     SqlDb="MyDatabase"
     ContinueOnError="yes"
     ExecuteOnInstall="no" ExecuteOnReinstall="yes" ExecuteOnUninstall="yes" RollbackOnInstall="yes"
     SQL="if exists (select * from sysobjects where id = object_id(N'dbo.xp_My_Func') and OBJECTPROPERTY(id, N'IsExtendedProc') = 1) EXEC sp_dropextendedproc 'xp_My_Func'"
     />

<SqlString Id="Free_xp_MyProc"
     Sequence="2"
     SqlDb="MyDatabase"
     ContinueOnError="yes"
     ExecuteOnInstall="no" ExecuteOnReinstall="yes" ExecuteOnUninstall="yes" RollbackOnInstall="yes"
     SQL="DBCC xp_MyProc(FREE)"
     />

<SqlString Id="Register_xp_My_Func"
     Sequence="3"
     SqlDb="MyDatabase"
     ExecuteOnInstall="yes" ExecuteOnReinstall="yes" RollbackOnUninstall="yes"
     SQL="if not exists (select * from sysobjects where id = object_id(N'dbo.xp_My_Func') and OBJECTPROPERTY(id, N'IsExtendedProc') = 1) EXEC sp_addextendedproc 'xp_My_Func', '$(var.strMyXPProcDll)' GRANT EXECUTE ON xp_My_Func TO public"
     />
</Component>
</DirectoryRef>

Maybe something like this can be added to the tutorial?

Albert

Van: Michael Osmond [mailto:[EMAIL PROTECTED]
Verzonden: dinsdag 27 juni 2006 0:09
Aan: Albert van Peppen; wix-users@lists.sourceforge.net
Onderwerp: RE: [WiX-users] SQL Extended stored procedures

Hello,

When doing the uninstall how is the property [D4WSQLSERVER] resolved, it could be that this has no value set and hence the installer can't locate the database server when uninstalling.

Also its trying to uninstall the master database because its part of a component. Try putting the <sqldatabase> element under <product> not in the component and then use the SqlDb="xxx" attribute to link to the database. That way the installer is just referencing master not trying to install it.

Michael

-----Original Message-----
From: [EMAIL PROTECTED] on behalf of Albert van Peppen
Sent: Mon 26/06/2006 11:09 PM
To: wix-users@lists.sourceforge.net
Cc:
Subject: [WiX-users] SQL Extended stored procedures

Hello all,

I am currently trying to get some extended stored procedures registered in SQL.
I did not found any samples for this. So maybe my code can be of any help in the tutorial ;-)

Altough, I'm having some problems with extended stored procedures for SQL.

I use the following piece of code in my WiX script:



<?define strD4WXPProcDll = "[D4WBINDIR]xp_D4WProc.dll" ?>

<DirectoryRef Id="TARGETDIR">
<Component Id="DatabaseComponent" Guid="$(var.guidDatabaseComponent)">
<Condition>D4WSQLSERVER <> ""</Condition>

<SqlDatabase Id="DrumisDatabase" Database="master"
     Server="[D4WSQLSERVER]"
     CreateOnInstall="yes" DropOnInstall="no" DropOnUninstall="no" DropOnReinstall="no">

   
   <SqlString Id="UnRegister_xp_D4W_Func1"
     Sequence="1"
     ContinueOnError="yes"
     ExecuteOnInstall="no" ExecuteOnReinstall="yes" ExecuteOnUninstall="yes" RollbackOnInstall="yes"
     SQL="if exists (select * from sysobjects where id = object_id(N'[\[]dbo[\]].[\[]xp_D4W_Func1[\]]') and OBJECTPROPERTY(id, N'IsExtendedProc') = 1) EXEC sp_dropextendedproc 'xp_D4W_Func1'"
     />

   
   
   <SqlString Id="Free_xp_D4WProc"
     Sequence="2"
     ContinueOnError="yes"
     ExecuteOnInstall="no" ExecuteOnReinstall="yes" ExecuteOnUninstall="yes" RollbackOnInstall="yes"
     SQL="DBCC xp_D4WProc(FREE)"
     />

   
   <SqlString Id="Register_xp_D4W_Func1"
     Sequence="3"
     ExecuteOnInstall="yes" ExecuteOnReinstall="yes" RollbackOnUninstall="yes"
     SQL="if not exists (select * from sysobjects where id = object_id(N'[\[]dbo[\]].[\[]xp_D4W_Func1[\]]') and OBJECTPROPERTY(id, N'IsExtendedProc') = 1) EXEC sp_addextendedproc 'xp_D4W_Func1', '$(var.strD4WXPProcDll)' GRANT EXECUTE ON [\[]xp_D4W_Func1[\]] TO [\[]public[\]]"
     />
  </SqlDatabase>
</Component>
</DirectoryRef>



Installation goes 100% ok.
But uninstalling results in an error:

Failed to connect to SQL database. (-2147467259
master   )

Can anyone help me with this one? Is there a way to debug the uninstall?

And another problem with the above code is when an error does occour during installation the rollback attempts to drop the master database. Which is reported as not possible (and so it should!).
But how can i add a extended stored procedure to SQL without the need of creating a database? (XPs are usualy registered in the master database)

Thanks in advance,

Albert van Peppen

Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642

_______________________________________________
WiX-users mailing list
WiX-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/wix-users

Re: [WiX-users] SQL Extended stored procedures

Reply via email to