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.

While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be the most XML-phobic, to learn XML and XQuery/XPath in order to deal with these in their daily jobs. In fact, I once wrote a SQL statement using XQuery to coerce a common variant of EVENTDATA() output into tabular format.

After spending the last few months wallowing in query plans, deadlock graphs, and the like, I've actually become convinced that XML and XQuery in SQL Server is *primarily a DBA tool*. I knew that would catch your attention. Besides event and query notifications, blocked process events, query plans, deadlock graphs, and EVENTDATA(), Database Tuning Advisor and new bulkload format files also use XML format. XML query plans can be studied by using XQuery/XPath and can then be modified (slightly) for use in "plan forcing" where the XML plan is used as a "query hint", USE PLAN. And there's probably some uses that I missed. SSIS files and Reporting Services RDLs are XML format. I'm becoming convinced that T-SQL error messages should be in XML format too.

So DBAs: if you haven't yet learned XQuery/XPath and the SQL Server XML methods, its not too late. It's there just to make you more productive (well, maybe "just" is an overstatement, but you get the point).

Just to whet your appitite, here's a little stored procedure that uses dynamic management views, the new CROSS APPLY operator and XQuery to look for physical operations in query plans and correlate it with the SQL query. Could be useful, perhaps sorted by execution count. Cheers.

CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO

EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'
EXECUTE LookForPhysicalOps 'Table Scan'

If I store XML in an XML data type column its sometimes useful for performance to be able to hoist the value of a particular item to its own persisted computed column. The column can then be used in SQL queries without having to recalculate the XQuery each time its accessed. For example, if I want to use an attribute at the root named orderid:

CREATE TABLE dbo.xml_order (
  id INT PRIMARY KEY IDENTITY, -- to enable XML Index creation
  order_doc XML,
  orderid -- this should be obtained from XML document
)

I must first define a SQL Server UDF to calculate the value.

CREATE FUNCTION dbo.getorderid (@data XML)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value('/*[1]/@orderid', 'INT')
END

and then use it:

CREATE TABLE xml_order (
  id INT PRIMARY KEY IDENTITY, -- to enable XML Index creation
  order_doc XML,
  orderid AS dbo.getorderid(order_doc) PERSISTED
)

The "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column. The persisted computed column requires the function to be deterministic (returns same output give same input).

This got me thinking...which types of XQuery statements are deterministic and what determines determinism in an XQuery statement? Turns out that ALL XQuery statements are deterministic, because the current SQL Server XQuery dialect doesn't contain non-deterministic XQuery functions like current-dateTime() and friends. Note that this could change as SQL Server XQuery adds (I hope) more of the standard XQuery functions and operators in future. But for now, all is safe and determnistic.