August 2004 - Posts

Does anyone remember Cairo? Cairo was, in the beginning of the 90's, supposed to be THE new operating system from Microsoft. This was the OS that would deliver on Bill G's "Information at Your Fingertips" vision. It was supposed to be version after NT 3.1. However as we know that didn't happen.

Is it just me, or does Longhorn start to smell like Cairo? Waking up this morning and reading quite a few blog posts (Chris Sells here, Mary Jo here) about cuts in Longhorn and delivery of Longhorn technologies in WinXP and W2K3, makes me wonder. So if you take away WinFs, and you deliver Indigo, Avalon and WinFX on XP and W2K3 - what's left?

Anyway, as Kent says, it's time to ramp up on Indigo!!

The following is something that has come up on a couple of the SQL Server 2005 beta news groups.

So, you have created a UDT and deployed it into SQL Server 2005. You have created a table with a column of the UDT type, and inserted some data. You're now testing it from SQL Server Management Studio (SSMS), and the chances are pretty big that you're running a query something like this:

select udt_col_name from table_name

or you may do something like this:

DECLARE @p udt_type

Notice that you are doing a select on the type itself, not ToString() or anything like that.

Anyway, you may be surprised when you get an error in both examples: "An error occurred while executing batch. Error message is: File or assembly name 'Your_Assembly_Name, Version=, Culture=neutral, PublicKeyToken=null', or one of its dependencies, was not found."

The error happens because SSMS acts like any other CLR client (SSMS uses ADO.NET under the covers); it retrieves the UDT and then on client-side (under the covers) tries to call ToString(). Before it calls ToString() it tries to load the assembly. So for this to work, the assembly needs to be located either locally together with the application, which in this case is SSMS or in the GAC. Bob wrote about this in his blog post.

So now you give the assembly a strong name, you install it in the GAC:

gacutil -i path_to_assembly

and you re-deploy the assembly. Now, when you re-run the query everything works fine!

However, what would have happened if you had initially given the assembly a strong name and then, after having received the error mentioned above, installed it into the GAC? Or alternatively, just copied the assembly to the path of SSMS? Note, without closing SSMS.

Well done to the ones of you that said you'll still get the error mentioned above. The follow up question is obvious: why?

Answer in the comments section or email.

No, not that sort of quickie :-). I'm on my way to New York this morning, but I thought I'd point out a couple of interesting blog posts:

  • Pat Helland discusses the differences between "Long Running Work" and "Long Running Transactions". Go off and read it!
  • Mr ASP.NET, Fritz Onion, re-publishes how to build ASP.NET projects in VS.NET, using class library projects instead of those pesky Web Applications projects. Oh BTW, what's up with this Wiki stuff anyway - what's different with Wikis from, say, newsgroups (apart from HTTP vs NNTP etc)?
  • Bob reveals some stuff we found out about serialisation in UDT's and UDA's. Which reminds me, I need to do a write-up about my perf tests comparing CLR UDA's and doing it with T-SQL cursors.

Now I gotta go. The taxi's soon here.

Darshan over at YukonXML posted this about SQL Server keyboard shortcuts. Cool!

Wow, that was a lot of acronyms in the title :-)! So, this post is about CLR methods used as Table Valued User Defined Functions (TVF). In other words, a User Defined Function that returns a table, instead of a scalar value. You are probably aware that if you want to create a method to act as a TVF, that method has to have a return type of ISqlReader as in the code snippet below:

public static ISqlReader MyTvf() {
//do some stuff
//get a ISqlReader instance - ir
return ir;

What makes this not as straight-forward as it may seem, is the fact that you can not just instantiate, for example, a SqlDataReader (which implements ISqlReader), and fill it with data. Instead you need to have a class that implements ISqlReader, instantiate it, and fill it with data.

When you implement ISqlReader, you also have to supply implementations for ISqlRecord and ISqlGetTypedData, IGetTypedData and IDisposable. This may seem to be quite a few methods to implement, but depending on what you want your reader class to do, you may not have to supply implementation code for all methods. There are however a couple of methods you have to implement:

  • ISqlRecord.FieldCount
  • ISqlGetTypedData.GetSqlMetaData
  • ISqlReader.Read

In addition to the above methods you also need to implement some of the methods from ISqlGetTypedData interface. This is the interface which gives you methods to retrieve the values of the data you return. Methods like:

  • GetSqlInt32
  • GetSqlChars

So dependent upon what data types your class can handle, you implement the necessary GetSqlXXX methods. As your class holds the data you "serve up" you have to somehow load the data into the class, and supply meta data. This is typically done in the constructor of the class, and below is an example of a constructor for a class that accepts arrays as input:

public InProcReader(Array ar, SqlMetaData[] schema) {
m_Resultset = ar;
  m_Schema = schema;

In the example above the user supplies an array plus an array of SqlMetaData. The constructor parameters are then assigned to local class variables, which are used when reading the data. An example of a CLR method using this particular class and constructor can look like so:

public static ISqlReader TvfTest() {
  string[,] sar2 = new string[2, 2];
  SqlMetaData[] smd = new SqlMetaData[2];
  smd[0] = new SqlMetaData("Col1", SqlDbType.NVarChar, 256);
  smd[1] = new SqlMetaData("Col2", SqlDbType.NVarChar, 256);
  sar2[0, 0] = "Test";
  sar2[0, 1] = "of";
  sar2[1, 0] = "Table-Valued";
  sar2[1, 1] = "Functions";
  InProcReader ir = new InProcReader(sar2, smd);
  return ir;

In the example above, the array suppled is a string array. Obviously the array data does not have to be of the same data type, you can send in an object array if you want, with different data types for each element.

You can read more about TVF's and UDF's in general in Chapter 3 of our book, and if you install the SQL Server Engine samples you can find the code for a class that implements ISqlReader. That sample is however a bit convoluted, so I have written a somewhat simplified class. You can download the code, basic documentation and test scripts from here.

As always, comments are most welcome!

So our book, "A First Look at SQL Server 2005 for Developers", has now been out for about a month. However we've not had that many reviews (even though the ones we've had are good), so it was nice to come across this review the other day (especially as the review was good).

Anyway, the purpose of this post is about what the reviewer writes about the code examples:

One thing I did not do with the examples in the book was to go through the examples on a current beta and verify that everything worked as advertised. I will be surprised if all the examples work exactly as presented when run in Beta 2, and shocked if the examples all end up working with the final release of SQL Server 2005.

We've tried our best to make sure the code examples work in Beta 2. It is however hard when you have to hand in the final manuscript a couple of months before the product becomes public. Inevitably there are changes between the manuscript being handed in and release which may cause examples not to run. Therefore we have a web site where we post "stuff"/errata that we, the book authors, as well as the readers comes across. So if you have the book:

  • please post a review
  • if you come across errata things that doesn't work, please email us and we'll add it to the site.

I talked to a friend in Sweden on the phone the other day and he mentioned how they in his company are using Skype more and more.

So I down-loaded Skype and installed it the other day, and today I went out and bought headphones and a mike. I have only made a couple of calls using it, but so far it exceeds all my expectations!!

If you want to get in touch with me using Skype, my Skype name is niels_berglund.

There has been quite a few discussion on some of the beta newsgroups about how to debug CLR methods, running in SQL Server 2005 (S2k5) as stored procedures, functions, etc. (actually there are some questions about how to debug T-SQL code as well). I thought therefore that it'd be a good blog post, discussing debugging.

[DISCLAIMER - This is local debugging only, and it works on my machine - YMMV]

So, to begin with; IMO there are three different ways of doing debugging against SQL Server 2005 (here I do not take into account debugging the "guts" of SQL Server using Windbag etc):

  • SQL Server Project Debugging
  • Class Project Debugging
  • Server Explorer Debugging

let's have a look at all three of them.

SQL Server Project Debugging - This is probably how you will debug (at least initially) if you create your .NET procedures, functions etc. by using the SQL Server Project in Visual Studio 2005 (VS2K5). This project type allows you to do F5 debugging. In other words:

  • you choose the database connection you want to use,
  • you create your .NET code, adorn the methods with the deployment attributes (SqlFunction, SqlProcedure, SqlTrigger, etc),
  • you set your breakpoints (BP's) and you hit F5.
  • the project will be compiled, the assembly deployed to SQL Server, the stored procs, functions etc, created  and your method will be executed in SQL Server.
  • theoretically your BP should now be hit. 

However I bet that the first time you do this it won't happen. This is because you need to indicate that you want to do SQL/CLR debugging. You do this in the Server Explorer (NOT Solution Explorer):

  • right click on your database connection
  • choose "Allow SQL/CLR Debugging"

Now when you hit F5, the BP will be hit and you can step through the code. If your CLR code calls into "normal" T-SQL code, you'll step from CLR to T-SQL and back, Sweet!! In the Beta1 build there is an "issue" however; if your CLR code executes a reader with dynamic SQL, something like so:

SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "Select id, name from emp";
SqlDataReader dr = cmd.ExecuteReader();
//do something else down here

In this scenario when you execute the reader, your debugging session will be taken to a Dynamic Sql page, and you'll not be able to exit from there! You'll not hang, the code will execute OK, but you won't see anything beyond the Dynamic Sql. As I said, this is a Beta 1:ism (sometimes I've been able to pass the Dynamic Sql, but most of the times not). If you execute a NonQuery or call into a T-SQL procedure, it'll work OK.

The final thing worth noticing with this type of debugging is that it works due to a sql script-file (Test.sql), which is used to execute your proc/function. This is not dynamic, so if you change the method name, add parameters etc, you need to change the file as well.

Class Project Debugging (a.k.a Attach to Process) - What if you do not use the SQL Server Project type, or you do not use VS2K5 as your IDE, how do you debug then? Well, you use the "old fashioned" way of attaching  a debugger to the server process (if you don't have the VS2K5 IDE, the .NET Framework ships a CLR GUI Debugger):

  • you create your .NET code and compile
  • you deploy the assembly to the database
  • you catalogue the CLR methods as stored procedures, functions etc.
  • you attach to the SQL Server process and you set the BP's in your code  (note that initially the debugger tells you that the BP won't be hit as no symbols have been loaded - you can ignore that).
  • execute the CLR proc from SQL Server Management Studio (SSMS) and the bp will be hit and now you can step through the code.

In this scenario, you will not have the same issue as above with a DataReader. This is because the debugger has no real knowledge of the T-SQL code, so you will never step into the Dynamic Sql. In addition you will not be able to step from CLR code to T-SQL code either.

By now you probably say that this is all good and well, but what about debugging T-SQL code. In SQL 2000, in the Query Analyzer, I could right click on a stored proc and choose Debug and step into the proc.  How do I do it in S2K5? Well, at the moment, the SQL Server Management Studio does not have the capabilities of debugging, so that's where the third way of debugging comes into play: Server Explorer Debugging.

Server Explorer Debugging - In this type of debugging you use, as the name implies, the Server Explorer pane from within the VS2K5 IDE. If you want to debug a pure T-SQL procedure/function you:

  • Go into the Server Explorer and choose the database connection you are interested in.
  • Expand the connection and drill down into the Stored Procedures/Functions folder.
  • Choose the procedure/function you are interested in, right click and choose "Step Into Stored Procedure" ("Step Into Function" if it is a UDF).
  • this takes you now into the proc/function and you can step the code!!

If you want to debug from T-SQL into CLR you need first to set the "Allow SQL/CLR Debugging" option on the connection as I mentioned above. You can also debug pure CLR code this way. Load the project and choose the CLR procedure/function from the Server Explorer and do "Step Into ...", Cool!!!

OK - so that's just a little bit about debugging. As mentioned in the disclaimer: this is local debugging only, it works on my machine BUT: YMMV.

If you have comments etc, please leave them here or email me!

Quite a few have blogged about Lookout, Yves being one of them. I down-loaded it a week ago, but didn't install it until yesterday.

It ROCKS big time!!! Only downside, not a major, is that it's not stand alone, but needs Outlook. I'd have loved to be able to install it on a VPC partition where I don't have Outlook installed.