I usually show folks the SQLCLR streaming table-valued functions and using SqlDataRecord with SqlPipe.SendResultsStart/SendResultsRow/SendResultsEnd on the same day. This sometimes brings up the question "which is better"? Since you can synthesize rowsets with both mechanisms and they both stream rows back to the client one buffer-worth at a time, its an interesting choice. However, with SqlPipe/SqlDataRecord, you can synthesize multiple rowsets in the same stored procedure. Just cruft up two different SqlDataRecord With multiple SendStart/SendRow/SendEnd blocks. Can't do that with any kind of table-valued function. You must "finish" one rowset (SendResultsEnd) before "starting" the next (SendResultsStart), though, you can't have interleaved output any more than you would with an ordinary cursorless rowset. Example follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MultiRowset()
{
SqlMetaData[] m = new SqlMetaData[1] {new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");

SqlMetaData[] m2 = new SqlMetaData[2] { new SqlMetaData("another", SqlDbType.NVarChar, 5), new SqlMetaData("number", SqlDbType.Int) };
SqlDataRecord rec2 = new SqlDataRecord(m2);
rec2.SetSqlString(0, "Goodbye");
rec2.SetSqlInt32(1, 42);

SqlPipe p = SqlContext.Pipe;
p.SendResultsStart(rec);        // first rowset
for (int i=0;i<10;i++)
 p.SendResultsRow(rec);
p.SendResultsEnd();

p.SendResultsStart(rec2);       // next rowset
for (int i = 0; i < 10; i++)
    p.SendResultsRow(rec2);
p.SendResultsEnd();
}

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.

I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).

Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm...

The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.

SQL Server 2005 doesn't support assembly versioning in SQLCLR. Recently in a talk I restated this as "SQL Server 2005 will not allow two versions of the same assembly to be cataloged into the same database using CREATE ASSEMBLY". Someone promptly pointed out that they could indeed run CREATE ASSEMBLY on two instances of the same code, if one was strong-named and the other was not strong-named. That is the case, but that is not support of assembly versioning. The .NET Framework Developer's Guide states "Versioning is only done on assemblies with strong names".

As an example, if I run CREATE ASSEMBLY on an assembly without a strong name, the query "SELECT name, clr_name FROM sys.assemblies" produces the following output:

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil

Note the version in clr_name is "0.0.0.0" regardless of the version number that is stored in the [assembly:AssemblyVersion] attribute in the file AssemblyInfo.cs. Attempting to catalog another version of the unsigned assembly, code or AssemblyVersion attribute change or not, produces the error:

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

If I have strong named assemblies, I can only have one strong named version that differs by version. Running create assembly (on a strong-named "version 1") gives the following output using "SELECT name, clr_name FROM sys.assemblies"

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
v1                  versioningtest, version=1.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil   

So I DO have "two copies of the same" assembly in the database at a time. However attempting to run CREATE ASSEMBLY on a strongly typed version 2.0.0.0 yields the following error.

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=2.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

So assembly versioning is not supported. Note that you can have assemblies with different 4-part names that differ only by culture. Assemblies that include a culture other than "neutral"; these are resource-only assemblies such as localized strings for different cultures. How does SQL Server 2005 decide which culture to use? SQL Server itself really doesn't decide, but that's another story for another day.

Been on the road lots lately, not much blogging, I'm afraid. I'll be making up for that...

I "found" a new command line utility today in SQL Server 2005. Maybe everyone but me already knew it was there. Reading up on replication, I came across mention of the "tablediff" tool. You feed it and source and destination table and it will spit out a report of data differences (the table definitions must be identical), on a row level or column level. It's meant to allow you to fix problems if you perform an operation that gets databases involved in transactional replication get out of sync. But it can be used outside of replication just as well. It lives in the ...Microsoft SQL Server\90\com directory.

Nice diagnostic and synchronization tool. My favorite option is -f which generates TSQL statements to get the tables back in sync.

Although I'm away from home this week (teaching in Hyderabad) I've heard that the first advance copies of Dan Sullivan's and my new SQL Server 2005 book, A Developer's Guide to SQL Server 2005, have made their appearence. Earlier than expected. Although we RTM'd before SP1, printing takes about 3 weeks and the service pack just beat us out the door. Lost a bet on that one... It's got much enhanced, detailed information, including a chapter on SMO, the SQL Server Management Objects API. Hope you like it. Look for an updates/code page on the SQLskills website soon.

Another item on my TechEd Boston dance card that slipped my mind is that I'll be doing book signings at the Addison-Wesley booth. See you there.

Thanks to my co-author, all of our many excellent reviewers, and the folks at A/W who made this possible.

As you can see, I'm trying to catch up on my blogging. Here's one I missed. I'll be at TechEd 2006 in Boston in June. It will be good to return to the location of my first-ever data processing job (yes, it was called data processing then, in the year 1977. and no, I'd don't count programming in grade school in my resume, I was 24 then). My TechEd dance card is filling up as I write this. So far, there will be:

A Breakout Session on SQL Server 2005 Error Handling: T-SQL, SQLCLR, and clients
Chalktalk on SQL Server 2005 Event Notifications, Database Triggers, and WMI
Chalktalk on SQL Server 2005 Plan Guides and Plan Forcing
Chalktalk on Controlling the new SQL Server features: Setup, Security, and Monitoring
DLINQ BOF with the amiable and outspoken Sahil Malik
New England SQL Server and Boston .NET user group meeting on a panel with Adam Mechanic (hopefully) and others

Whew, that ought to keep me busy. See you there.

This one ought to be titled "Your error message is such a tease".

I've always liked the fact that SQL Server 2005 error messages are verbose (modulo security considerations for login error for example). I always point out at least one during classes, start to read the message...and about 50 words or so later, tell students to "check out the entire text of the message offline". Excellent! Of course message boxes in applications that display these verbose errors must be made bigger or scrollbars added. Such is life.

So, in SP1, my perfectly working WMI alerts stopped working. Looking at the job history, I found the following:

Unable to start execution of step 1 (reason: The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run.). The step failed.

Great. Except what's the magic macro??? What a tease, everything I need to fix things except for the most vital piece of info. Downloaded the April Books Online update, which had a TON of great new and revised content. But nothing about this problem (well they can't catch everything right away). Then I remembered RTF-readme file. Went to the latest "KB 913090 - A List of bugs that have been been fixed" and found IT had been updated with a reference to "KB 915845 - SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1". Yep that's the one!! The magic macros are:

$(ESCAPE_SQUOTE(token))
$(ESCAPE_DQUOTE(token))
$(ESCAPE_RBRACKET(token))
$(ESCAPE_NONE(token))

There's even a nice script that catalogs a procedure to update your existing jobs. What could be easier? Except, attempting to catalog the procedure yields "A fatal scripting error occurred. Incorrect syntax occured while parsing '. Huh? That's too sparse of an error. What now, the saga continues....

Turns out this error occurred because I had SSMS working in SQLCMD mode by default (see "What's fixed in SP1 blog entry") and because there was a token $anything in the code, it was being processed by SQLCMD. Ohhhh. Turned off SQLCMD mode and it worked fine. Jobs fixed and working. The jobstep SQL now looks like this:

INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N'$(ESCAPE_NONE(WMI(TextData)))')

So that's "the whole story". Hope it saves you some grief. That said, I REALLY like the WMI and Event Notification idea a lot. It's like having the database "tell me" when there's a problem that I want to know about. In real-time or queued and saved as I sleep for resolution "at my leisure". My leisure...ha, how about "the next morning"? I'll take that.

Lately I've been working on using WMI events in conjunction with SQLAgent alerts and jobs to notify operators, queue up notifications for the DBA's resolution offline, and other uses. Setting up the alert and the job is fairly straightforward, where I stumbled at first is referencing WMI variables (and even knowing what variables were available) in the job that processes the WMI event. The varaibles are doc'd in BOL in conjunction with event notifications, which is really what the WMI provider for Events uses underneath.

You use a WQL query that references the event to set up the WMI Alert, using "SELECT * FROM <my_event>" syntax (BOL example is SELECT * FROM DEADLOCK_EVENTS). And reference in job by using a WMI variable, like so:

-- BOL example
INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N''$(WMI(TextData))''')

So, when you set all this up, why doesn't it work? It always produces "TextData not available". Hmmm...The "magic switch" to success is found under SQL Server Agent "Properties" menu entry under SSMS. You need to turn on (in the Alert System tab, at the bottom of the page) "Replace tokens for all job responses to alerts". They aren't WMI variables, they are WMI tokens. Success! Took me a while to find that one...

But, in SP1 it "breaks" again. Sigh. More about this in next blog entry.

You've likely heard by this time that Service Pack 1 for SQL Server 2005 was released earlier today/yesterday depending on what timezone you're in. Rather than go into how to get it, there's two KB articles that are really useful, KB 913090 - A List of bugs that have been been fixed and KB 916940 - A list of the new features and improvements. I noticed a couple tiny ones in SSMS that weren't listed in either document, so I'd bet there are likely more unlisted improvements and fixes.

Displaying the word "NULL" for a NULL instance of a user-defined type in "Results to Grid" mode works now, without having to install the UDT in the GAC or SSMS directory. This is even improved over the fix in SP1 CTP, where it worked in the UDT was installed. Prior to the SP, you could display it in "Results to Text" mode, but Grid mode threw an exception. Fixed now.

If you choose the "By default open new queries in SQLCMD mode" in Tools/Options Query Execution, the setting "sticks" now when you close SSMS and reopen. I know at least one person that will be happy with that.

That's all for now.

Since we were on a roll the SQL Server 2005 discussion, Richard and Carl had me back this week on dnr-TV. It's a combination of podcasting and demonstrations using Visual Studio, and I obliged with an hour demonstration of using SQLCLR with Visual Studio. I also talk about some of the things to watch out for in your SQLCLR coding...hope you find it useful. Should be available tomorrow...

I had the good fortune to be able to spend an hour or so (may have been a bit longer chatting with Richard and Carl last week on .NET Rocks! about SQL Server 2005 and the state of the world in general. This episode should be available on Tuesday, Apr 18 at the usual places, Franklin's.NET and MSDN. Although the topic was originally to be SQLCLR, things quickly shifted into a discussion of snapshot isolation (aka versioning), Service Broker, XML support, statement-level recompilation and other topics database folks enjoy talking about. Hope you'all enjoy it.

I just finished my second talk in the “designing and managing scalable systems with SQL Server 2005” webcast series. I got to talk about one of my favorite features in SQL Server 2005, the Service Broker. Some DBA may still think that Service Broker is a developer-only feature, but after you've seen what you can do with Event Notifications (specifically the blocked process event) today, I know you're convinced it also is a substrate for some of the more powerful tools in the DBA toolbox. As well as a great way to help acheive a reliable, scalable system that supports scale out.

I'm posting the broker configuration scripts here, but if these aren't sufficient to walk you through all the points I made in the webcast, feel free to write. I've also given seminars on advanced Service Broker concepts and implementation from a developer and DBA perspective; write if you're interested in learning more.

I referred in the webcast to Roger Wolter (one of the “fathers of Service Broker” 's) weblog, this is listed in the Blogroll at the left. In addition Rushi Desai, Jesús Rodríguez, and Dan Sullivan's blogs have some good info, too. Rushi and Jesús have written some amazing utilities around Service Broker.

Roger's “Where does Service Broker fit in?“ (with respect to MSMQ, Biztalk, and WCF/WWF) blog entry is the Feb 28 entry, but each of these blogs is worth subscribing to...

Here's the scripts from the presentation:

A frequently asked question since SQL Server 2005 shipped is "how do I expose my custom data source to SSIS/SQLRS/your app here". If you have a data server that you must connect to, and it accepts commands and returns resultsets, its a nature fit for an ADO.NET data provider-based mechanism. Having worked with OLE DB provider writers for a few years, I'm always surprised that its quite straightforward to write an ADO.NET data provider. As an example, the ATL OLE DB Provider Wizard uses a skeleton starter provider implementation in atldb.h that weighs in at over 12000 lines of code. A similar skeleton for a ADO.NET data provider is a hundred or so lines.

I originally built an ADO.NET data provider for version 1.0 that simply accepted a command that consisted of a file path and returned file information as a rowset. I'd upgraded the provider for ADO.NET 2.0 without adding much, just restructuring to use base classes instead of interfaces. Today I added enough of the "additional classes" to make it useable in SSIS. Haven't tried much else yet. I had to add two more skeleton classes: a ProviderFactory and ConnectionStringBuilder.

The provider factory connects is the glue between the machine.config info that is used as a "provider enumerator" and the provider itself. You need to:
1. Either register the provider in the GAC or make it available to each client.
2. Add "provider configuration" entry for it, either in machine.config or in each client's application configuration file. The console app has a specific entry in app.config, for SSIS its probably easier to put it in the GAC.

So that SSIS would accept the connection string (which the provider really doesn't use), I added a dummy property, DataSource. SSIS doesn't like empty connection strings. The provider, test console app, and test SSIS project (data connection only) is attached.

Although the provider doesn't do much (many of the methods are simply stubs) it may be a useful "starter" providing the scaffolding. Let me know if this is useful and I might do some work to expand it further. (Apr 17: Updated the provider based on feedback in this blog thread)  Enjoy!

A common question on the newsgroups is "what will happen if a SQLCLR procedure allocates a huge chunk of memory or enters a tight, endless loop" by mistake? DBAs are concerned about any language with a looping construct (they've likely coded the tight, endless loop in T-SQL to see) or a malloc or equivalent. The BOL suggests any loop should call Sleep(0) but sleeping is not always the same as yielding.

SQL Server 2005 does respond to memory pressure in a concerted manner releasing buffers and ending procedures if needed. So you might see an error similar to this under memory pressure or if you allocate "too much memory":

Msg 6532, Level 16, State 49, Procedure MyProc, Line 0
.NET Framework execution was aborted by escalation policy because of out of memory.

However, SQL Server doesn't consider "being busy" an error condition. Suppose there was a batch process that actually did run for a long time? In the case of the endless loop, if the server has other work to do, SQL Server 2005 will force a CLR thread to yield and "punish" it (timeslice-wise). This is visible using a dynamic management view, sys.dm_clr_tasks, in the field "force_yield_count". When in an endless loop, this field increases, but SQL Server does not kill the task.

Well, not at my house... but at the Portland (Oregon) SQL Server Users Group meeting. I'll have to admit that I've not attended many meetings of the users group (OK, I've not attended a meeting in a long time) but its because I've not been at home on a day that corresponded to a meeting day. It's a nice change to go speak somewhere that's not at least 2 hours away. 

I'll be speaking on SQL Server 2005 - enabled features in the various updated data access stacks (ADO.NET, OLE DB, ODBC, JDBC) with a drilldown into query notifications.

See you there.

Today was the first of my two presentations as part of the TechNet Webcast Series for the ITPro, about SQL Server 2005 security. The session went a bit long, as there are so many new security-related features to cover, wouldn't you agree? I got some really great feedback on the key management portions of the talk, some folks suggested that the key management features alone would make a good follow-up talk. Agreed..but I didn't want to leave out the rest.

If you've received your link to this blog through email and really want the key management scripts (and password changing program), they're here, the link is at the end of this entry. If you haven't visited this blog before there are quite a number of security-related entires here for your enjoyment, and almost all the entries are SQL Server 2005-related.

In case didn't get them from the slides, the blogs on SQL Server 2005 security by security team members Laurentiu Christofor and Raul Garcia are excellent.

I'll be doing one more talk in the series, on Service Broker operational considerations. I think Service Broker is one of the most interesting (and important) features in SQL Server 2005. See you there.

A couple of weeks ago, I published an entry about using System.Configuration.dll in SQLCLR procedures, along with a sqlserver.exe.config file. Since then I've received information that, although reading configuration information in SQLCLR procedures does work, that *technically* its not officially supported, and the fact that it works might change in future releases. Just thought I'd let you know. Don't do this.

As with the use of undocumented stored procedures or undocumented DBCC commands in code, I think its better to be safe than sorry in these cases. Speaking of undocumented stored procedures, folks that depend upon the undoc'd (at least it was last time I looked) xp_regread procedure might be happy to know that it trivally simple to read the registry (modulo security considerations) from a SQLCLR program. That's one undoc'd procedure that you don't need anymore.

I'm writing this just before the start of the Dutch Devdays conference in Amsterdam. The conference promises to a good one, with local speakers as well as guest speakers. I'm going to be presenting four  different in-depth topics, all related to database client applications and/or SQL Server 2005 applications, and how ADO.NET 2.0 and SQL Server 2005 will affect the coding landscape. This will lead right into the talks on language intergrated query (LINQ) an up-and-coming data access technology built into the next release of the C# and VB.NET languages.

I'll try to write back on how things are going but the next two days are going to find me pretty busy. When I'm not giving presentations and available for questions at the forums, I'll be hanging out at the Class-A booth; stop by and say hi, if you're in the neighborhood. Anko Duizer and Astrid Hackenberg are going to be doing some training at Devdays most of Tuesday, as well; knowing their work the sessions will be well worth a listen.

I'll be following up the Devdays conference with two intense (and fun) days on SQLCLR and SQL Server 2005 XML storage and query at the Class-A facility in Woerden. Now that I'm over my (fairly easy, this time) jetlag, I'm looking forward to some late-night discussions and coding in this one. If you like getting your feet wet with SQL Server 2005 by "drinking from a firehose", stop by the booth, there may be a few spots left.