SQLCLR, transaction nesting, and TransactionScope

Ahh…enough shameless self-promotion (for a while), let's talk transactions. I worked in the past with folks who like to compose transactions. SQL Server supports nesting of transactions and named savepoints but not autonomous transactions. So CREATE PROCEDURE X AS BEGIN TRAN — work here COMMIT calling it standalone means the work is in a transaction. […]

I’ll be on a SQL Server 2005/Visual Studio 2005 Panel

About how they work together. The panel is being held as a webcast, and they’ll be some slides too. This is all taking place on September 22, and they’ll be two separate iterations. If you’ve even wondered what the point of using the exactly coordinated CTPs together was (and whined about it), come and attend this panel […]

We win SQL Server Magazine Readers’ Choice

I received email today that it was OK to talk about this. Our book “A First Look At SQL Server 2005 For Developers” was selected as “Best SQL Server Book” by the readers of SQL Server Magazine. You can read a short blurb about it here. And if you’re a subscriber, you can read the […]

IsNull != IS NULL, the reprise and the solution

About a week or two ago, there was a LONG discussion on the SQLCLR beta newsgroup about the fact that the IsNull property that you use on CLR UDTs won't return TRUE or FALSE inside the server. It returns FALSE or NULL. Turns out that, although you use this property to *indicate* to the engine […]

SqlTypes.SqlXml and impersonation

I've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing. You can do impersonation using the .NET SqlClient data provider using code roughly like this: WindowsIdentity w = SqlContext.WindowsIdentity; WindowsImpersonationContext c = w.Impersonate(); // do something here c.Undo(); The rule is that in the "do something […]

Dude, where’s my table?

In answering a question about schemas, users, and objects (search on "schemas" to see the blog series I, II, III), I realized I never posted the portion about object resolution. Here goes. When SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure. If batch or […]

What are the unsolved problems

I’ve spent the last few days talking with the data access folks at Microsoft. They’re working on the next set of features and I’ve given input based on feedback I got from clients and students in the past two years. There may be some things I forgot about, however. If you have an ADO.NET feature […]

Whatever happened to…

I answered a question on the beta newsgroups last week about OSD and RSD (remember them?) by saying that those features had been pushed out into the future. The person then asked if the material in chapter 14 of our first look book had any practical value. I must admit that you can't cut and paste the examples and […]

Why my cat can’t use a SQLCLR proc to read files

I'm known for my vivid imagination when making up test/exposition examples. I have a cat named Sam. So, once upon a time, I wrote: CREATE CREDENTIAL myuser  WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z' GO CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3' GO ALTER LOGIN sam WITH CREDENTIAL = myuser GO The DDL works. Now, […]

The demise of the dispatcher proc

I've been wondering what happened to the QueryNotification dispatcher proc that's used by SqlDependency in ADO.NET (and in ASP.NET with SQL Server 2005). The one that I wrote about in one of my MSDN articles. Lately, although the dispatcher proc and assembly didn't show up in MSDB, the function kept working. I wondered why, how, […]

SQL Server book-of-the-month club pick

Another fantastic book for your reading pleasure this month. “Customizing The Microsoft .NET Framework Common Language Runtime” by Steven Pratschner. In-depth coverage of .NET 2.0 hosting API. But why is this relevent? Because most every bit of it is used by SQL Server 2005, as its hosting the .NET runtime. That’s why. For example, as you read […]

It’s already tomorrow in Australia

They always get a bit of a jump on us WRT “current events” because of the timezone difference, especially if you live in the land of Pacific Time. I always come home on new year’s evening and by before it’s even dinner time, I’ve already missed new year in Sydney. We do get “Saturday Afternoon […]

I’m not sysadmin, I just play one on TV

I really like the feature known as “All Permissions Grantable” in which any permission can be managed using the GRANT verb, because there are no “magic” logins or users anymore. The coolest thing is that all server roles are now defined in terms of the permissions they have; this chart is in the BOL. In it, […]

Combined provider status

Just was looking through my reported problems last night to see what the resolutions might be. Looks like the two having to do with managed provider were fixed. But not yet, ie, the fix is not in June CTP according to the report. Calling Connection.Dispose() and Command.Dispose() directly in-proc should now work, reported fixed. 6522 Error Anomaly […]

The Times They Are A-Changin’

Today I embark on a new venture, a new part of my career. I’m joining SQLskills as Director of Developer Skills. I’ve worked with SQL Server since version 4.21 off and on, taught classes about SQL Server at least part of the time since version 6.0, and, for the past three/four or so years, spent […]