Today we’ll look at an example where sgen did not solve the problem with a dynamically loaded XmlSerializers assembly. This issue was found by a customer and filed as a bug on the Microsoft Connect site for SQL Server: http://connect.microsoft.com/SQLServer...

Simple question: what security permission level is required to allow dynamic loading of assemblies? Trick question: dynamic assembly loading is always disallowed under SQL CLR, even under UNSAFE. This is mentioned twice in BOL: CLR Integration...

Usually, talking about the differences between running code under the CLR vs. running under SQL CLR focuses on functionality that either doesn’t work or is difficult to use in a safe and reliable manner. However, one feature that SQL Server actually adds...

There have been some questions about the sysadmin requirement of SQL Server 2005 Debugging, and I’d like to explain it in some details. When you debug T-SQL or CLR code in SQL Server 2005, there are two users involved: user running the debugger and user...

Naveen covered the TSQL exception handling in his posts on sqlprogrammability blog site. How does the TSQL exception handling mechanism interacts with SQLCLR exception handling mechanism? We will cover this topic in this post. When SQL server execute...

SQL Server 2005 ships with a new debugging engine that supports debugging of any T-SQL and CLR code running in the server, including batches, stored procedures, user defined functions/aggregates/triggers, etc. You can use Visual Studio 2005 to debug against...

Very often, you may wish to factor out your code into separate assemblies in your application. For example, you separate your type in one assembly because it gets used by multiple areas in your application. And you save your proc that uses that type in...

In part 1 we created a nice user defined aggregate. Now we are going to make it more sophisticated and let its value depend on two parameters ShipCountry and ShipShipCity. You might try having two parameters in Accumulate function of the aggregate but...

SQL Server 2005 allows creating of User Defined Aggregate in any of the .NET languages such as C# or VB. For simple cases like SUM or MAX you probably want to use built-in aggregates, however there are cases where build-ins are insufficient. In such cases...

When the user code will try to access a resource outside of the SQL server it will run in the security context under which SQL Server is running. But this is not the ideal situation since it could become a security issue when the logged user that calls...

Assembly redirection is not supported in SQL CLR. Assembly redirection can be done using publisher, application configuration and machine configuration files. SQL Server 2005 uses application configuration to redirect assembly call after Alter Assembly...

This is a sample on how to register satellite assemblies in SQL Server 2005. Based on the CultureInfo on the executing thread, CLR will try to load the respective resource assembly. It should not be a difference between satellite assemblies inside...

As we saw in the last post, cooperative clr code that explicitly yields during long-running computations outperforms clr code that gets forcibly rescheduled. How should one discover sql/clr code that is considered 'runaway' by the server and could be...

Perhaps you've seen the following note in BOL advising SQLCLR users to explicitly yield in their long-running CLR functions: "Long-running managed code that accesses data or allocates enough memory to trigger garbage collection will yield automatically...

There are two important memory considerations you may want to track when using SQL CLR functionality: 1) How much memory is SQL CLR using? And 2) How much memory is SQL CLR allowed to use? The answer to the first question is pretty easy to answer...

Perhaps you’ve heard all about the power and flexibility .NET framework integration provides in Sql Server, read the news articles, seen the example code on blogs, but yet you are still unsure on what benefits it can provide. Some of the best demonstration...

The required cases for execute permission on UDFs and UDPs are pretty understandable, however the situation for a UDT is not quite so clear-cut. What does “Execute” really mean in the context of a UDT anyway? Simply put, execute permission is required...

From the query I described yesterday, here are the different possible appdomain states and what they represent: Appdomain initialization: E_APPDOMAIN_CREATING – Creating the appdomain Appdomain in use: E_APPDOMAIN_SHARED – A Runtime...

The dmv sys.dm_clr_appdomains is a great way to find information about the appdomains currently loaded in the system, but it doesn’t help if the appdomain in question has already been unloaded. For those DBAs who want to track their appdomains like their...

Using a CLR trigger we can register all user defined functions, procedures, types, aggregates etc., contained in an assembly when it is created in the database on "CREATE ASSEMBLY ... ". This saves us the time of registering all these ourselfves. We skip...

SQL Server provides the ability to find the week of the year in which a given date lies. For example: declare @d datetime set @d = '13 november 2005' select datepart(ww, @d) The result of this is 47. Now, the problem with this is that the...

Some people peeked (and poked) under the covers and have been surprised at how SQL Server is twiddling their UDT bits during serialization. Let's peek together. Consider the following fragment of a UDT: [Microsoft.SqlServer.Server.SqlUserDefinedType...

Restrictive world of SqlClr computed columns and indexability With Yukon entering the world of managed code, the world of indexes has just become a tad bit complicated but hey we are talking about smart/passionate Sql Server customers here – I am...

Last time we saw an example of using a powerful functionality, Regular Expressions, from a supported .NET Framework library and how you can leverage it in your database applications. Today we will talk about another such functionality that is enabled...

One of the many benefits of using CLR Integration in your database applications is the availability of rich .NET Framework libraries. SQL Server provides you with a set of built-in functions that can be used in your queries but with CLR Integration you...