Enabling CLR Integration

Hi All,
I'm new to SQL Server Express. But I need to find out how easy it is to create custom types with SQL Server Express because we have to represent house numbers as a custom type in the database. After enabling the CLR integration with the SQL Server Surface Area Configuration tool, I created an SQL Server Project with VS 2005 Pro., created a custom type called HouseNo, deployed it to a database and finally created a table with a column of that custom type. But when I tried to use the option 'Show Table Data' for that table, the following errors always appeared

SQL Execution Error

Executed SQL statement SELECT NP.ToString() AS NP From Table1
Error Source : .NET SqlClient Data Provider
Error Message : Execution of user code in the .NET Framework is disabled. Enable "CLR Enabled" configuration Option. -- I think I've already enabled it.

Does anyone know how to solve the problem? Just one more question, for a custom type implementing the IComparable interface, would SQL Server Express uses the CompareTo method to perform sorting?

Thanks!

Regards,
Nathan
-
The SQL Server Epxress is newest and the following is code for the custom type.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
public class HouseNo : INullable, IComparable, IBinarySerialize
{
// Private member

private String number;
private String letter;
private bool m_Null;

public HouseNo()
{
Number = "0";
Letter = "0";
}

public HouseNo(String number, String letter)
{
Number = number;
Letter = letter;
}

public string Number
{
get { return number; }
set { number = value; }
}

public string Letter
{
get { return letter; }
set { letter = value; }
}

public int CompareTo(Object obj)
{
if (obj is HouseNo)
{
HouseNo hno = (HouseNo) obj;
int numCompare = Number.CompareTo(hno.Number);

return (numCompare == 0 ? Letter.CompareTo(hno.Letter) : numCompare);
}
else throw new Exception("Obj is not a HouseNo");

}

public override string ToString()
{
// Replace the following code with your code
return Number+Letter;
}

public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}

public static HouseNo Null
{
get
{
HouseNo h = new HouseNo();
h.m_Null = true;
return h;
}
}

public static HouseNo Parse(SqlString s)
{
if (s.IsNull)
return Null;
HouseNo u = new HouseNo();
// Put your code here
return u;
}

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)
{
Number = r.ReadString();
Letter = r.ReadString();
}

public void Write(System.IO.BinaryWriter w)
{
w.Write(Number);
w.Write(Letter);
}

#endregion

}

hi,

Nathan Lai wrote:

Hi All,
Error Message : Execution of user code in the .NET Framework is disabled. Enable "CLR Enabled" configuration Option. -- I think I've already enabled it.

Does anyone know how to solve the problem?

verify the clr is really enabled...

SET NOCOUNT ON;
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled';
-- EXEC sp_configure 'clr enabled', 1;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;

Just one more question, for a custom type implementing the IComparable interface, would SQL Server Express uses the CompareTo method to perform sorting?

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
public class HouseNo : INullable, IComparable, IBinarySerialize
{

public int CompareTo(Object obj)
{
if (obj is HouseNo)
{
HouseNo hno = (HouseNo) obj;
int numCompare = Number.CompareTo(hno.Number);

return (numCompare == 0 ? Letter.CompareTo(hno.Letter) : numCompare);
}
else throw new Exception("Obj is not a HouseNo");

}

yes.. it follows the ICompare implementation

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)
{
Number = r.ReadString();
Letter = r.ReadString();
}

public void Write(System.IO.BinaryWriter w)
{
w.Write(Number);
w.Write(Letter);
}

#endregion

}

but you have to be aware on how the sting serialization is performed.. it prefixes data with the lenght of the string to be serialized and then the bytes of the serialized string.. if you directly sort on the row byte data, it will be perfomed from the shortest data, as the lenght is the first "set" of data of the serialized string

the first byte indicates the presence of null, the next one indicates the overall lenghts, then all the bytes storing the actual data..

you can workarond that padding all strings to be serialized to the same lenght with null chars, that's to say

w.Write(this.Letter.PadRight(lenght, (char)0));

and deserialize it like

this.Letter = r.ReadString().TrimEnd(new char[] {(char)0});

this will always pad the string to be serialized to the defined lenght so that byte order is the same as the semantic ordering of the column and thus can even be correctly indexed in it's raw form, and, used in an ORDER BY clause it will perform like a corresponding char(lenght) equivalent column..

regards

|||Hi,

I've checked that the CLR integration has actually been enabled in the database as it is possible to execute the following query using the 'sqlcmd' utility :

> use GIS; -- use the GIS database;
> select NP.ToString() as NP from Table1

This query returned an empty table as a result and reported no errors. But when I did the same thing in VS 2005 Pro., I continued to receive the same error message as stated in the previous post -- Execution of user code in the .NET Framework is disabled. Enable "CLR Enabled" configuration option.

The .NET Framework Data Provider for SQL Server is used to perform the connection. And the following connection string is used :

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GISDB.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"

Is there any problem with the connection string?

Thanks!

Regards,
Nathan
|||

Hi Nathan,

Your connection string is fine, but it is for a User Instance, not the main instance. User Instances are completely independent from the parent Instance of SQL Express and you need to configure them separately. When you work in Management Studio, you're working against the parent instance, not the user instance. Check out the User Instance white paper for more information.

Typically, all configuration of a User Instance would be done at runtime, use a SqlCommand to run the configure statement to turn the CLR on for the User Instance.

sp_configure 'clr enabled', 1

You can also use SQLUtil (available in the MS download center) to run queries against the User Instance or connect to a running User Instance from Management Studio or SQLCmd using it's pipe name. To get the pipe name, you'll need to run

select * from sys.dm_os_child_instances

and then make the connection using the pipe name returned for the User Instance you want. There are already a number of posts in this forum explaining User Instances in more detail, just search on my name and 'User Instance' to find them.

Regards,

Mike Wachal