SQL Server Project (4)

I have to close this series articles on SQL Server Project. The final part will cover some special issues related to SQL Server Project.

The first issue is the connection to SQL server. As I mentioned in SQL Server Project (2), it is recommended to use the context connection since the CLR assembly is already in a SQL server running process. However, this connection can only be created once. You cannot create another context connection for other executions. Normally, you don't need another one in one stored procedure for example. However, if you create several SQL procedures, functions, and triggers in one dll, you might get exceptions if one calls another since only one context connection is allowed. You don't have control who is going to run these stored procedures, functions, or triggers.

Therefore, I think it is better to create one SQL item (SP, function or trigger) in one dll. You could create a normal SQL connection with catalog for a db table, user name, and password information in a connection string if you have to, and that connections can be created more than once. In a SQL server context, it does not make sense to do that unless you need to connect to another SQL server or Oracle server.

An related issue is that always to handle exceptions in your assembly and close any opened connections. As I mentioned before, the assembly is loaded to SQL server running process, and it would not unloaded automatically when your SP exits. The assembly may still in memory. If you don't handle exceptions, the opened connection will block the same SP being called again.

The second issue is that some assemblies may not work in SQL server project. I tried NHibernate and some other dlls as my references. What I found is that some assembly reflection functions are not working in SQL server. For example, I found that there always exceptions when these dlls try to load another assembly file to get class or property information. All these kind calls cause exceptions. This is very unfortunate and I think this is very bad limitation for SQL server project. I have no idea why and how exceptions would happen.

If you are going to write your SQL server project in C#, all the parameters in a SP have to be specified whey the SP is called, unlike MS Transact SQL SP could have default values for parameters.

The installation and deployment process for a SQL Server Project is a complicated one. As I mentioned in my previous articles, some asymmetric keys, log in permissions have to be created, and dependency assemblies have to be registered. The deployment of SQL server project is one click process if you have source codes and Visual Studio avalailbe. However, that click-only-once deployment may hide some SQL calls to register and set up all SPs, function and triggers. If you want to create an automation process in SQL, you have to keep all the assembly files available somewhere even they are not referenced after the installation and deployment. To uninstall it, the process is reverse. You have to remove all the SPs, functions, and triggers first, and then to delete assemblies, and then other dependency items. Since there are many things involved, anything wrong may cause your assembly not being functional.

I had a case that my CLR assembly SP did not work one day. It says about some permissions to load assembly failure. I tried to remove all the assembly and to re-install again, but I could not remove them as well. I was stuck in the middle. Finally I found it is the case SQL server was in low virtual memory. The error message actually was misleading. I restarted the SQL sever and reloaded files I did in the middle, then everything worked fine. Therefore, be prepared to handle all the uncertainties, document all the procedures in a well organized way and save all the source codes and dependency files in repository.