Well, originally we were going to implement a sexy Delphi.Net CLR Based Stored procedure now that we are using MSSQL 2005 and hearing all the good things about this highly publicized feature. Sadly, we hit the wall after initial testings showed us the big amount of limitations on the assemblies that we could use and all the exceptions we must made to make it work on MSSQL 2005. For example:
* First, you need to enable CLR on the database. (Thats ok.)
* Classes must be public and static. (Makes it harder, but we can manage it)
* Avoid namespaces with anything that is not the list of "safe" name spaces approved by Microsoft. (mmm)
* "Safe" assembly is simply a way of saying "non-microsoft built" assembly. You have a couple more "modes" on how to install your assembly on MSSQL, but as you can read below (MSSQL Online help), it gets touchy, and probably complicated. (yeah, I tried it, got ugly)
“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “
* MSSQL will complaing about the CodeGear assemblies being unsafe, some tweaking is necessary.
* Third party assemblies are not welcome on MSSQL. i.e. RemObjects.
Well, in summary, after playing with it, adding our assemblies required a lot of code trimming and removing any namespace that has any reference to anything visual (makes sense) even if it is not being used at all. Finally, the detail that just finish helping us to dropped the idea was that we don't have the source code for those "extra" assemblies (RemObjects) that we required and the amount of trimming just got out of control.
With one option out we went back to the drawing board with the plan to extend and update an old implementation of "Old School" extended stored procedures, as soon as I open that old project I realized why I never liked working with it, parsing the parameters, checking their types, defining data columns to return a proper record set was simply a big painful and slow process.
Thankfully, I was able to find in the vault of memories a great presentation by Berend de Boer made in Inprise DevCon '99 where he explains the general steps to implement them and even included a great piece of source code of the mighty "TSQLXProc" object that simply takes over the painful implementation and takes it to the Delphi way. After that finding Berend's website was easy.
For anyone needing this, check this link.
My Kudos to Berend,
* First, you need to enable CLR on the database. (Thats ok.)
* Classes must be public and static. (Makes it harder, but we can manage it)
* Avoid namespaces with anything that is not the list of "safe" name spaces approved by Microsoft. (mmm)
* "Safe" assembly is simply a way of saying "non-microsoft built" assembly. You have a couple more "modes" on how to install your assembly on MSSQL, but as you can read below (MSSQL Online help), it gets touchy, and probably complicated. (yeah, I tried it, got ugly)
“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “
* MSSQL will complaing about the CodeGear assemblies being unsafe, some tweaking is necessary.
* Third party assemblies are not welcome on MSSQL. i.e. RemObjects.
Well, in summary, after playing with it, adding our assemblies required a lot of code trimming and removing any namespace that has any reference to anything visual (makes sense) even if it is not being used at all. Finally, the detail that just finish helping us to dropped the idea was that we don't have the source code for those "extra" assemblies (RemObjects) that we required and the amount of trimming just got out of control.
With one option out we went back to the drawing board with the plan to extend and update an old implementation of "Old School" extended stored procedures, as soon as I open that old project I realized why I never liked working with it, parsing the parameters, checking their types, defining data columns to return a proper record set was simply a big painful and slow process.
Thankfully, I was able to find in the vault of memories a great presentation by Berend de Boer made in Inprise DevCon '99 where he explains the general steps to implement them and even included a great piece of source code of the mighty "TSQLXProc" object that simply takes over the painful implementation and takes it to the Delphi way. After that finding Berend's website was easy.
For anyone needing this, check this link.
My Kudos to Berend,
2 comments:
I may be late, but the MSDN documentation states at This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible:
"This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible"
But thanks for the
Hi Ralf,
Yes indeed, I noticed the depracated warning on the MSDN documentation but it seems that it will be still available on SQL Server 98 and we have a very slow cycle of migration on the database area, we will probably wait at least a year (or one service pack) after SQL Server 98 is released (third quarter of 98) to attempt migrating.
Hopefully by then, MS will improved their CLR Extended Stored Procedure implementation to the point we can use more third party stuff on it.
Post a Comment