[WiX-users] SQL Extended stored procedures

[Albert van Peppen]

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:

 

<!-- codesnippet -->

 

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

 

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

 

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

 

   <!-- Uninstall / rollback install -->
   <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'"
     />

 

   <!-- After unregister, before delete xp_D4WProc.dll -->
   <!-- After this the xp_D4WProc.dll can be deleted / replaced -->
   <SqlString Id="Free_xp_D4WProc"
     Sequence="2"
     ContinueOnError="yes"
     ExecuteOnInstall="no" ExecuteOnReinstall="yes" ExecuteOnUninstall="yes" RollbackOnInstall="yes"
     SQL="DBCC xp_D4WProc(FREE)"
     />

 

   <!-- Register on install / reinstall -->
   <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>

 

<!-- /codesnippet -->

 

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