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
<> ""</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