CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

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 the “CREATE PROCEDURE …”, “CREATE AGGREGATE …” steps usually necessary after registering…

6

CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

This code is provided “AS IS” with no warranties, and confers no rights. I’ve made some small modifications to comply with standard naming conventions on 11/30/2005. Save the following into AutoRegisterTrigger.cs ——————————————————–using System; using System; using Microsoft.SqlServer.Server; using System.Data; using System.Data.SqlTypes; using System.Data.SqlClient; using System.Xml; using System.Reflection; using System.Text.RegularExpressions; namespace Microsoft.SqlServer.Sample { public class AutoRegister {…

1

Best Week Ever

SQL Server provides the ability to find the week of the year in which a given date lies.  For example: declare @d datetimeset @d = ’13 november 2005′select datepart(ww, @d) The result of this is 47. Now, the problem with this is that the correct result according to ISO 8601 is 45, not 47.  Why? …

11

Serialization and Ordering: An Integral Example

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(Format.Native)]public struct Simple : INullable{  private bool isNull;  public int val;   public static Simple FromInt(SqlInt32 i)  {    if (i.IsNull)      return Null;   …

5

Tutorial on SqlClr Computed Columns and Indexability

  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 sure if we show them the right direction outlining the restrictions and…

1

Using Webservices and Xml Serialization in CLR Integration

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 by CLR Integration. CLR Integration allows you to consume results from webservice calls. As you…

26

Working with Regular Expressions

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 can enhance this set to any functionality from the list of supported…

8

IS NULL vs. IsNull

When using CLR UDTs, there has been some confusion regarding the difference between the T-SQL IS NULL construct and the required UDT property IsNull. Admittedly, this is not well-documented, so I thought I’d try to clear this up. To avoid having to store null UDTs, once an instance of a UDT has been found to…

1

Locales in SQL Server

Introduction Writing locale-safe external code in SQL Server has always been important due to the global nature of many businesses running on SQL Server. Doing it correctly requires a good knowledge of the different mechanisms used to store that locale information, and understanding which is the relevant locale in each context. In order to explain…

3

Yet another TVF: returning files from a directory

In my previous BLOG entry dated May 5th, I promised another sample that retrieves multiple files from a directory based on a specified name pattern. Here it is, this time in VB.NET. This is a more traditional Table-Valued-Function in that it actually returns multiple rows, each with two columns (file-name and contents). Please refer to…

5