For folks that have been asking...my latest whitepaper "SQL Server 2005 Security Best Practices - Operational and Administrative Tasks" was posted on the Technet website this week. It also covers the nuances of security when using SQL Server SP2 and Vista. Enjoy!

Next week (28-29 Mar) I'll be doing some talks on SQL Server 2005 and ADO.NET vNext at DevDays Belgium in Ghent. On 28 Mar, I'll also be doing a special additional talk on SQL Server 2005 Event Notifications (including a cross-database notification demo) for the Belgian SQL Server user group at the event. If you're a registered member of the user group, I'll see you there. You can also sign up to be a member of the user group at the user group website. 

Thanks to the user group for inviting me.

Since SQL Server 2005 was introduced with .NET programming support, folks have been trying to push the boundaries of what can be used in SQLCLR or at least trying to determine where those boundaries are. Here's two things that, as far as I know, can't be done in SQLCLR.

1. Use dynamically generated code. The canonical example of this is dynamic serialization assemblies generated when you use "Add Web Reference" in the generated web service proxy code. The way around this is to use the sgen utility. But lately, it's been brought up that dynamic programming languages such as Iron Python always generate dynamic code. SQLCLR forbids using this, even in UNSAFE assemblies. No dynamically generated languages.

2. Use the SMO libraries. A combination of SMO not supporting partially trusted callers and using a special type of connection result in SMO being unusable even in UNSAFE assemblies. The obvious workaround is to use SQL DDL, but SMO encompasses more than DDL, for example, configuring service settings via WMI. If you really want to use SMO, it would be possible to call out to a web service or better yet, a Service Broker-based service that uses external activiation, does the SMO calls and returns the script and/or results.

One thing that I thought was particularly interesting in the Orcas Mar CTP was support for stored procedures. This support exists in LINQ to SQL and EDM ObjectServices; I thought I'd start with ObjectServices. There almost no documentation on this topic at this point, about half a page with an incomplete mapping schema example. That's to be expected at this point, though.

In Mar CTP, you can specify stored procs for insert/update/delete, but not for EDM queries yet. You need to change the SSDL (store schema definition language) and MDL (mapping definition language) files. With the correct mapping filechanges, AcceptChanges just calls the sprocs automatically. I started with a VS-generated set of mapping files and used XSD-based intellisence and error messages with line numbers to guide me along. Although you may have gotten rid of the XSD/XML errors or compile-time errors, your mapping files can fail to agree with one another. This occurs as a runtime error when you "new-up" an instance of your model class. Try-catch is your friend here.

SSDL changes were easier (just add the Function and Parameter elements for the proc) with one gotcha. My SSDL Namespace attribute was called "people"; if you specify "dbo.myproc" as the store name, EDM looks for [people].[dbo.myproc] at execution time. That's invalid in SQL Server. Specifying "myproc" as the store  name (or letting it default to the value of the "Name"attribute) causes the runtime to look for [people].[myproc]. The name of my sproc was actually [dbo].[myproc]; I had to change to SSDL Namespace attribute to "dbo" to make it work. Doing so made me change the MSL to match, but had no global ill effects.

The MSL was a little more interesting. I needed to put an "EntityTypeMapping" and "TableMappingFragment" in between my "EntitySetMapping" and "ScalarProperties" elements. Then I could add my ModificationFunctionMapping and Insert/Update/DeleteFunction elements as children of EntityTypeMapping. Because the XML schemas require elements be defined in order, I almost thought this feature was masked out in this CTP. Although I could see it in the schemas. It was there, of course, you just have to hit the intellisense in exactly the correct place in the document to show the element you're looking for.

OF COURSE, this is going to sound like complete gibberish without an example. I started with the easiest possible example: one table, few columns, primary key (its required) but not even an identity column. I also have two projects, one with the "vanilla" tool-generated CSDL/SSDL/MSL so I could refer back if I had problems. I did have problems. Coding three files of XML by hand, even with XSD-base intellisense, is right up there with [insert your least favorite chore here].

Three hours later, thanks to perseverance and SQLProfiler, I had a functioning prototype. It's posted here. I'm trying to decide which undoc'd part of this interesting set of mappings, models, and query lanaguages to try next. Or what tool to create to make this less of a chore. Enjoy!