SP2 must haves and SP2 compatibility

SQL Server SP2 was released last Monday. The links were posted on many blogs, so I won't repost any but the main one here. SP2 is a "major" service pack because there are a few "must have" features in addition to some rather useful improvements and quite a few bug fixes. The ones I'd consider […]

SSIS Connectivity Whitepaper is Live on Microsoft website

My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live on the Microsoft web site today. For anyone who is confused by all of the whitepaper announcements lately, here is a list of titles and direct links. This one is about SSIS: Connectivity and SQL Server 2005 Integration Services http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc […]

The Internals and Troubleshooting whitepaper is available

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have […]

Some questions and answers on plan guides

Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry. Q. Can you use a plan guide on an encrypted procedure? I want to put […]

Does sp_dropuser “do the right thing” with schemas?

Here's another blog posting to answer a question from over a month ago. With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA […]

Using SQLCLR functions in indexed views

It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago. What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)? Some of the requirements came from […]

One Service/Queue per Query Notification, please

Last week at TechEd I was showing off Query Notifications. When I showed using the preprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor), a delegate asked about using the same queue with more than one subscriber. He repeated the question when I showed the low-level SqlNotificationRequest. I did some tests over the weekend and the […]

Just Add 50000

I've been doing my talk on try-catch in T-SQL for a while and whining about not being able to "rethrow" (via RAISERROR) a system error. Even went as far as writing a RegEx decoder so that I can copy the error into a user error message text and parse the text back on the client. Two […]

SMO and Powershell: Better Together, The Scripts

Sorry, I know that slogan has been used already. Thanks to everyone who showed up for the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, are the demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showing up and showing me some neat shortcuts… as I typed. Many of the scripts were […]

SMO scripting at TechEd Europe

I'm in Barcelona in TechEd and tomorrow I'm going to be doing a chalk talk on SQL Server Management Objects (SMO). Although I'd usually done my SMO coding in C#, I decided (based on my co-author Dan Sullivan's blog post on the subject) to try out using Powershell along with SMO. So, although when most folks […]

Another behavior that follows schemas, query plan reuse

When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users […]

SQL Server service accounts and privileges

When installing previous versions of SQL Server, I'd always keep a list of the exact privileges that a SQL Server service account would need. I'd make up a new user and give them exactly these rights. The SQL Server installation program would grant the appropriate permissions during the install, and I'd be set. Principle of […]

Clustering Whitepaper for SQL Server 2005 available now

I've noticed that, as more companies want to run SQL Server applications 24x7x365 interest of clustering has increased dramatically. One of the most asked-for papers about SQL Server 2005 is an update to the clustering whitepaper. Well, its available NOW; an over-100 page treatise by Allan Hirt and Dave Whitney. It covers not only clustering […]

SQL Server 2005: an overriding theme to the new features

Even now, although SQL Server 2005 has been for about 3/4 of a year, when I teach people about the new developer features, I'm still asked about the zen of the new features. Do they follow a common theme? Is there a usage pattern that emcompasses all of the things like Service Broker, SQLCLR, XML […]

What’s that stuff good for, anyway?

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and […]

Federation Enhancements/Changes in SQL Server 2005

SQL Server 2005 implements quite a few technology pieces to help database developers and administrators acheive scale-out while acheiving the appearance of (or actually accomplishing) application or server federation. The information is scattered throughout the books online, but you can read a nice whitepaper by Roger Wolter on how to choose among the features here. I'll be […]

At TechEd: Event Notifications and scope creep

Started my part of the festivities today with a chalktalk mentioning SQL Server DDL triggers and Event Notifications. But explaining Event Notifications meant explaining Service Broker services, queues, and contracts. And the fact that both Event Notifications and DDL Triggers use a XML format. That you can change to a rowset format using the XML.nodes […]

DLINQ and FLWOR

I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of […]

Finding data differences between SQL Server 2005 tables

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 […]

Using WMI and SQL Agent Alerts (part 2)

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 […]

Using WMI and SQL Agent Alerts (part 1)

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) […]

What’s fixed in SP1?

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 […]

No HTTP endpoints on SQL Express

I've been intruiged for a while with the idea of using SQL Server Express edition as a gateway from HTTP messaging to Service Broker messaging. That is, receiving web service messages through HTTP endpoints and turning them into Service Broker messages to take advantage of the robustness of the Service Broker protocol. Went to set […]

I hate to wait (doesn’t everyone)

I don't know anyone that likes to wait for an application to respond. This is especially a concern with web applications; you start to wonder if the web infrastructure has a problem. Should you hit by "Order" button again? Because waiting is subjective it's nice to be able to know, when a user calls, how […]

SOAP Protocol officially supported on SQL Server running on XPSP2

Got the word today. Officially supported. Error in readme file. Reference this blog entry.  Good to hear this.

Extracting the smdl for an already-deployed model

I'm sure there's something about this in BOL somewhere, but this cost me a few more than a few ticks yesterday. Hoping to save you a few ticks. This one deals with semantic models used by Report Builder (part of SSRS 2005). Suppose you have a semantic model deployed on server A that you want […]

Service broker and database master keys

Service Broker security is the subject of confusion even among people who think they know how it works. Some of the confusion occurs because security was tightened up in the last few CTPs. I've read in two different places that Service Broker conversations always need to have a master key in the database(s) where the […]

For all you XML-phobes that lke DDL triggers and Event Notifications

After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you’ve deposited EventData into a […]

Seeing errors in callers, SQLCLR error 6522, and T-SQL TRY-CATCH

I've been going over how errors are raised in SQLCLR against the SQL Server 2005 RTM version. If you remember (search the blog on "6522" if you don't) how errors made their way from the managed to the unmanaged stack evolved throughout the beta. My goal in investigating this was to determine if you can […]

An interesting tidbit from the readme file

I like to read the readme files. There's often juicy little tidbits of info in there that won't show up by searching the BOL (that's why there's a readme in the first place). In SQL Server 2005 there 3 “readme” files: requirements, readme, and “addendum to the readme” (available on web via a link in […]

Two cool add-ins for SSMS

Now that SQL Server 2005 has been released, I'm becoming more "at one" with the tools. I've commented on a couple of cool features as things evolved, but having worked with the product since it's early beginnings I've also learned a lot of DDL. And learned early on that, "just because you can't do it […]

It’s here

And I'm only the fiftieth person to blog about it. That's SQL Server 2005 and Visual Studio 2005/.NET 2.0, of course. I considered not blogging and saving network bandwidth 'cause everybody already knows, but…naaaahh. CONGRATULATIONS folks! Must be quite a party going on in Redmond. The MOST exciting set of products to come out in quite […]

SQL Server 200x wish list – wrapup and explanation

The last five blog entries were a lot of “off-the-top-of-my-head” list writing. Mostly remembering what people had said or the “I wish there were” comments. Just so no one misunderstands the tone…. The SQL Server 2005 version of SQL Server has more new features by order of magnitude (at least from a developer perspective, though I […]

SQL Server 200x wish list – part 5, SQLCLR

OK, SQLCLR wish list comes last. Mostly because I'm tired of being accused of being a SQLCLR bigot; I think it was because our book covered those topics first. Oh well. Default parameter values in SQLCLR procedures when called by T-SQL. You obviously can't/shouldn't support these when called from-CLR-to-CLR. Right now, they work, but the […]

SQL Server 200x wish list – part 4, T-SQL and Engine

Transact-SQL has been around for a lot longer, the list will be a bit smaller. But the T-SQL enhancements in 2005 were numerous but, of course, left some folks wanting more. As always. More error handling improvements – TRY-CATCH is VERY nice. For next time, how about FINALLY and RETHROW? The ability to rethrow system […]

SQL Server 200x wish list – part 3, Service Broker

This one is about SQL Server Service Broker. It's not necessarily a cool or sexy feature, but may be the most revolutionary feature in there. The problem with Service Broker is that most people don't get it. They think YAQS (yet another queuing system). It's more than that. It's already used for 3 features inside […]

SQL Server 200x wish list – part 2, XML and XQuery

OK, because it was Michael that asked originally, XML/XQuery wishes for SQL Server 200x. In no particular order. Full compliance with "standard" W3C XQuery. If the standards committee finishes before SQL Server 200x ships. Else it will become a "standard" no one follows. Hmm…like SQL. Reason for this is twofold. 1. Folks who get a […]

Hello again. and SQL Server 200x wish list part 1

It's just a few weeks until the launch of SQL Server 2005. I'm been hiding under a rock since returning from Hong Kong. TechEd there was a great time, biggest attendence they've ever had. Security and SQL Server 2005 were the hot topics. Yes, even after PDC in September. It's been over three years since […]

Security in SQL Server 2005 – unsafe assemblies in Sept CTP

After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now. In the September CTP version on SQL Server 2005 (I think […]

Security in SQL Server 2005 – “non-traditional” logins and users

This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement […]

Security in SQL Server 2005 – execute as…with cookie

You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this: EXECUTE AS USER=’fred’ – some T-SQL here REVERT This is meant to replace the SETUSER verb because you […]

Security in SQL Server 2005 – unsetapprole

One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is. You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an […]

Security in SQL Server 2005 – Encryption

In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of: EncryptBy (Key/Certificate/PassPhrase) and DecryptBy (Key/Certificate/PassPhrase) But there's also the less well-known: SignByAsymKey/SignByCert VerifySignedByAsymKey/VerifySignedByCert HashBytes and also the aptly-named: DecryptByKeyAutoCert and […]

Security in SQL Server 2005 – Logins

While working on the new edition of our book for after RTM, I've been looking around for security features that I missed or that didn't exist when we wrote it (May 2004 and before). Here's one: In SQL Server 2005, you can DISABLE a LOGIN by using ALTER LOGIN. You can also change the name of […]

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 […]

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, […]

What’s a truncation exception?

I commonly do a demo when teaching SQL Server 2005 where I write a SQLCLR UDF that's returns the string "Hello World". The define it, sans VS autodeploy, like this. CREATE FUNCTION somefunc() RETURNS NVARCHAR(4) AS EXTERNAL NAME MyAssembly.MyClass.MyFunction When invoked, it returns "Hell", silently truncating the string the CLR sent it. UNTIL Apr CTP. […]

How DO you change your SQL Login password?

SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd […]

XML Schema for the truly lazy

Here's something I've noticed you can do with SQL Server 2005 and Visual Studio 2005. Take a set of XML documents with the same basic structure. Load one into Visual Studio 2005. Click on the XML menu, Create XML Schema. Make any refinements to the XML schema that is produced that you want, based on […]

Combined provider: transactions and the return of 6522

With April CTP came the new combined managed provider replaces System.Data.SqlServer with a new improved, works in-process or out, System.Data.SqlClient. I just call it “the combined provider” now. People that didn’t work on the betas will look at me funny when the product RTMs; “was there ever anything other than System.Data.SqlClient?”. Why yes, Virginia… With […]

Go ahead, make a validation mistake…

One of the nice surprises in the April CTP is that SQL Server 2005 XML schema validation errors come with a location now. That's handy. Here's an example: Msg 6926, Level 16, State 1, Line 1 XML Validation: Invalid simple type value: '1134'. Location: /*:Invoice[1]/*:LineItems[1]/*:LineItem[1]/*:Sku[1] Even better would be a line and column number that […]

How to make a DBA smile

Speaking of SQL Profiler brought this to mind. The number one feature that brings a smile to every DBA's face: GRANT ALTER TRACE TO [somedev] No longer do you have to listen to developers ask “make me SA so I can run the trace”. Actually, brings a smile to devs too, no longer do they have […]

Mailing the graphic showplan to a friend

I've always liked the graphic showplan in SQL Server query analyzer. The biggest hassle with it came when you wanted to send the plan to a friend. Or maybe MS support, but support is your friend too… right? You could send screenshots (which had the annoying habit of never displaying those hover-over stats) or go back […]

A first demo of the combined data provider

When I did my first demonstration with the combined SqlClient and SqlServer provider in the April CTP version of SQL Server, I was a bit surprised. I wrote a simple stored procedure to run in the server, exactly the way I've always written it to run on the client (modulo sending results back to the […]

What do you mean by “unsupported”?

The last blog entry brings up the question of what I mean when I say something is "unsupported" in SQLCLR. Becuase I've said that J# is "unsupported". This doesn't mean that it won't ever work or that you couldn't actually get technical support for it, given enough time, energy, and money. Technically, its possible to […]

DROP ASSEMBLY change – just a convenience?

Looking at the Feb CTPNotes file again, there's another change that caught my eye. DROP ASSEMBLY has changed with respect to dependent assemblies. In past, you had to drop assemblies one at a time, so if assembly A called assembly B, you first dropped A then B. Now dropping A drops B automatically if B […]

SQL Server unified provider info on DataWorks’ team blog

The data access team (known as DataWorks) has started up a team blog. Some of the individual team members, like Angel Saenz-Badillos and Sushil Chordia, have been blogging for a while, this one has posts from all members. One of the first posts was information about the upcoming SqlClient and SqlServer provider unification by Pablo Castro. […]

Service Broker’s new poison message handling

In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling? A poison message is a fact of life in transactional messaging. When a […]

New Service Broker features in the Feb CTP

I've been doing some experimenting with the new SQL Server Service Broker features in Feb CTP. You can read about them in the CTPNotes.doc file; I won't repeat the information here. The features are: 1. Improved Endpoint Security – authentication option NONE is not longer supported 2. DEFAULT message type and contract 3. Poison conversation […]

FOR XML…XMLSCHEMA and schema validation

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http://schemas.microsoft.com/sqlserver/2004/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for […]

About the new SQLCLR TVFs

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple […]

First release of Service Broker Explorer

My cohort, Dan Sullivan, has released the Service Broker Explorer on his Service Broker Developer's Spot website. It a graphic user interface for Service Broker that has some “topology map” features and configuration features and some management features for Service Broker objects. According to Dan: “It lets you drill into Sevice Broker and add and […]

Query plan guides in the SQL Server 2005 BOL

Just catching up on my blogging before a little vacation next week. Browsing through the SQL Server BOL from the December CTP, I came across some information on something called "plan guides". There is info on some stored procedures that create and manage plan guides, a database option (in ALTER DATABASE) and a system view […]

UDM data access – is it all done with mirrors?

I had a few spare cycles to do some reading recently, and thought I would check out the new Unified Dimensional Model (UDM) that can be used with Analysis Services 2005. I started by listening to a webcast by Amir and Ariel Netz. Interesting stuff about datamarts, data warehouse, and specialized metadata model proliferation. And […]

Don’t try this at home…yet

In the last blog entry I talked about using System.Transactions in SQLCLR code. But don't try this yet, the keyword here is *will* be used. I base this on a few bugs that I filed on System.Transactions/SQLCLR being closed as “this will be fixed in beta 3”. And a statement on a public newsgroup by […]

System.Transactions, promotable transactions, and composition

There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points. The first point of interest is that you will use System.Transactions to use transactions in SQLCLR procedural code in SQL Server 2005. In the beta 2 implementation of the SqlServer data […]

Tracking the pesky appdomain

People (especially DBAs) want to see what those pesky appdomains are doing in SQLCLR. Back in beta1 there was a system function, master.sys.fn_appdomains(), that showed which appdomains were running and which assemblies were loaded in the appdomains, number of bytes used, etc. In beta2 this view stopped working and, although you can watch appdomains being […]

Fun with static XQuery evaluation – 4 – answers and wrapup

Here’s the answers to the question from Fun With static XQuery evaluation – 2 – start with a schema collection CREATE XML SCHEMA COLLECTION ages AS ‘<xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema” targetNamespace=”urn:ages” xmlns:tns=”urn:ages”> <xs:element name=”age” type=”xs:int”/> </xs:schema> ‘ GO DECLARE @x xml(ages) SET @x = ‘<age>12</age>’ – fails ??! SELECT @x.query(‘string(/age)’) GO This fails because there can be […]

Fun with static XQuery evaluation – 3

After the last two entries, you might be thinking "I guess I can never use text() as a node test with typed XML again". Not so. The error message reads: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements. So what's left? Mixed content, for one thing. Mixed content consists of a mixture of […]

Fun with static XQuery evaluation – 2

Reference back to the previous entry. Now that we know the rules, let's try them out: – snip – Data(),text() and string() accessors XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. […]

Fun with static XQuery evaluation – 1

There's been lots of puzzled faces lately when I try to explain doing XQuery against strongly typed XML (XML typed by a SCHEMA COLLECTION) vs untyped XML. The largest FAQ is why when I have this document: <person>   <name>bob</name>   <age>51</age> </person> using the value function (after assignment to @person) @person.value('/person/age', 'int') returns my […]

Password policies on object passwords

A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 […]

Service Broker object names are case sensitive

One of my students last week noticed that using a Service Broker object name (like a CONTRACT, SERVICE, and MESSAGE TYPE name) with the wrong case caused an error message. That's because Service Broker object names are case sensitive by deisgn. Because these identifiers can go over the wire, and you can't predict the collation […]

XQuery and the useful XML index

Haven't been blogging as much recently, as I've been teaching and travelling quite a bit. Last week though, I received an interesting query on XQuery query plans (that's more uses of the word query in one sentence than I've seen before). We were looking at SQL query plans when XQuery is involved. I'd remembered that: […]

How do I see all the tables again?

I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 […]

HTTP Endpoints and FORMAT=NONE

HTTP Endpoints in SQL Server 2005 are normally a way to support SQL Server clients that speak the SOAP protocol. It turns out that, although the HTTP endpoint requires a SOAP request, the response need not necessarily be SOAP or even XML. You can return something other than the vanilla document-literal SOAP format; the way […]

New functions to use with try-catch

I'm still assimilating little tidbits of information about the Nov-Dec CTP build. Put this one in your pubs database in the CTP: create procedure deletejob (@id int) as begin try  delete jobs where job_id = @id end try begin catch  – you knew about these  print error_number()  print error_message()  print error_severity()  print error_state()  – these are […]

Merging SqlClient and SqlServer providers

It turns out that the week before last, the SQL Server and ADO.NET teams (they may be part of the same team) made public the decision to merge the SqlClient data provider and in-proc SqlServer provider code into a single provider. Details are sketchy currently, but the reasoning behind this is that it's easier for […]

SqlContext syntactic changes

Syntactic changes are coming to the SQL Server in-proc provider. Compiling a stored procedure with the latest Dec CTP bits yielded the following message every time I used a method that started with "Get" from SqlContext: warning CS0618: 'System.Data.SqlServer.SqlContext.GetPipe()' is obsolete: 'Will be removed soon' Looking further with Reflector, it appears that SqlContext will now […]

Another improvement in Dec CTP

Another little thing I'd found had changed in Dec CTP. I'd reported a bug on this one and knew it was gonna be fixed eventually, but better sooner than later. I came up when Dan Sullivan suggested his "universal web service" based on SQL Server's XML data type and XML Schema Collections. Here's the prototype: […]

Using inheritence in SQLCLR UDTs

I answered a question on the newsgroups on how *exactly* inheritence works when you use it implement UDTs in SQL Server 2005. Also wanted to record the explanation here…. It doesn't work like you'd expect inheritence to when to use T-SQL, because SQL Server is blissfully unaware of the inheritence relationships (they're not recorded in […]

A First Look at Dec CTP

OK, I couldn't help myself. The Dec 2004 CTP-specific readme had quite a number of things that are new. There are many improvements in SSIS, for example. The “real“ readme and the known issues file (sqlki.chm) still have July dates on them. But there are always a few little things I have queued up to try with a […]

Schemas, Users, and Objects – III

Now, back to our regularly scheduled technical content. About schemas, users, and owners. Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways. 1. Someone with authority can alter the table's owner 2. Someone with authority can […]

Schemas, Users, and Objects – II

The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following: 1. Alter the authorization on Ed's table so that it is owned by Ed.    (Interesting aside, can Ed do […]

Schemas, Users, and Objects – I

UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow. […]

Some useful articles on MSDN online

A few weeks ago, I'd posted links to ADO.NET 2.0 articles I'd written for MSDN online. At that time, I mentioned there would be one more article in a series, but I'd save the subject as a "surprise". Well, the article just appeared: Tracing Data Access in ADO.NET 2.0. Did you know there was built-in […]

SQLCLR assembly owners and dependencies

I was demonstrating SQLCLR appdomain usage (see previous post) to a class last week.Later on, I mentioned a different concept, that of dependent assemblies. This brought up the following question: If user A owns assembly A and B owns assembly B, what happens if B contains a routine that calls A? Are two versions of […]

SQLCLR appdomain usage

A little-known behavior of SQLCLR (although we did mention it in our book) is that SQLCLR creates one appdomain per assembly owner, not one appdomain per database. One appdomain per database was the observed behavior in beta 1, although its important to remember that the algorithm for appdomain creation/destruction in SQLCLR is technically undocumented and subject […]

How do I get (N) rows from a SQL Server XQuery?

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they […]

What’s my (Windows) identity?

Looking for some cool SQLCLR and/or security-related features to show off. Before I hit the road again Friday, for a long stint in “airplane-land“ on way to Europe. Browsing through the SqlServer provider in Community Preview beta version of SQL Server 2005, I think I've found one. Came across SqlContext.GetWindowsIdentity. With this call you can […]

I have CREATE TABLE privilege but can’t create tables

More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate. I always explain this in terms of […]

More on ownership chains

People always ask…if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains? Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string […]

Users, Schemas, Objects, and Owners

Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED. FRED is the owner of a schema named FRED FRED is a memeber of […]

.NET-based library for Service Broker

I was browsing through some of the SQL Server 2005 code samples today (the ones that come with the product) and came across one that was a really nice idea. It's a library to encapsulate SQL Server Service Broker T-SQL calls in an object model, called ServiceBrokerInterface. Some of the other Service Broker samples are […]

Formatting XML/XQuery in SQL, and caveats

And now, for something a little technically lighter… I've taken to using a convention when writing statements that involve XML/XQuery and SQL together. I put the XML input or XQuery program on separate lines than the SQL part of the statement, this seems to make both query languages and also inline XML data easier to read when they're […]

XQuery Schema Validation Clarification

Rereading the post about this from last night, it appears that I may have used an ambiguous analogy when attempting to figure why this feature works the way it does, and given folks the wrong idea. It has to do with whether the results of the query would be wrong or the query itself is “incorrect”. Using […]

Early XQuery schema validation

Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature. If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will […]

Thought on Microsoft client XQuery

I've known for a couple of weeks now that XQuery and the new XML-SQL client mapping have been dropped from System.Xml in .NET 2.0. The XQuery implementation over the XML data type in SQL Server 2005 is NOT going away, of course. Just the client-side bits. Folks are encouraged to keep using XSLT 1.0 and […]

One more thing about 6522 error

One more thing about getting an error 6522 wrapper from SQLCLR procedures. the workaround I spoke about last week (dummy catch block) works to propagate SQL Server errors to the client without wrapping them in the 6522. But it doesn't seem to work if you want to throw your own user error (error > 50000). […]

Intersect and Except….almost

There's a new Community Technology Preview of SQL Server 2005 available on MSDN for universal subscribers. I'm sure you've all heard of it by now, I was out of the country last week with limited bandwidth and just downloaded mine yesterday. I was meandering through the BOL, looking for interesting things, when it dawned on me […]

Least privilege and HTTP endpoints

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as […]

Error 6522:the reprise

About a month ago, I'd written about unhandled errors in .NET procedural code always causing error 6522 to be returned to the (T-SQL) caller (see "Error 6522 and You"). Because the "real" error (the one that caused the .NET code exception) is wrapped in a 6522 error, retrieving the value of @@error will always give […]

OLE DB/ODBC, SNAC, and the new “MAX” data types

After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. […]

How SQLCLR works when its disabled

I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest […]

Using FX assemblies outside of “the list”

It turns out that you can force SQL Server to use FX libraries that are not on the approved list. Deploying user code that uses these through Visual Studio produces: Error: Assembly 'system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. But that's because Visual Studio uses CREATE ASSEMBLY "from bytes", passing in […]

More about SQLCLR approved assemblies

After reading Niels’ comment about the framework class library list, a quick check proved he was right, System.Runtime.Remoting.dll was not on the approved list. Adding a reference to this library (using his cool Object Browser trick) and writing a one-line stored procedure: [SqlProcedure] public static void SomeTestProc() { TcpChannel t = new TcpChannel(); } proved […]

SQLCLR – List of approved framework class libraries

Back from vacation, drove from Ashland back to Portland Saturday, then Portland to Redmond Sunday. A lot of driving. The Michael Brundage XQuery book was even better the second reading. Joe Celko's book was excellent (what else?) also. So was OSF presentation of Henry VI, Much Ado About Nothing, and King Lear, but my favorite was […]

Peaches en Regalia and XML Schemas

Back to XML and schema collections in SQL Server 2005… After my experiences with Office 2003 schemas yesterday (thanks for getting me going, Dan), last night I went out onto the Internet, prowling around at 1-2am looking for XML schema repositories. The most likely candidates are mostly gone or inactive. Microsoft's http://www.biztalk.org now links to […]

What’s all that stuff in sys.assembly_files?

I just answered this question on the "public/private" sqlclr newsgroup, but now I'm wondering myself. What IS all that stuff doing in sys.assembly_files? If you've done autodeployment of a SQLCLR assembly lately, take a gander as the metadata view sys.assembly_files. There's not only the binary assembly code and the debug symbols (pdb file) but also source code. […]

Office 2003 XML schemas and SQL Server 2005

After lots of interesting (but fruitful) experiences with the XQuery engine in SQL Server 2005, I decided that I'd rather have an XML schema collection for everything than put [1] (that's the numeric predicate meaning "item 1 using 1-based ordinals") everywhere in the XQuery that I needed a singleton. It's a good idea to use "typed XML" anyway. Dan […]

XML and SQL Server: stream, column, or string?

With the introduction of the XML data type (using an XML data model) comes there's a choice of pulling XML out of the database as a stream or column. Using an XML data type as a column type will get you a column of type XML. That's SqlXml to you in .NET. SELECT … FOR […]

HTTP Endpoints and Windows XP SP2

SQL Server 2005's HTTP endpoint functionality uses http.sys, the HTTP stack in the kernel, to do it's processing. Until lately Windows Server 2003 was the only OS to implement http.sys and HTTP endpoints only worked when SQL Server 2005 ran on this OS. Windows XP SP2 shipped with http.sys and HTTP endpoints are now enabled […]

Using the data() function in XQuery

Some folks have asked why I appear to use the XQuery data() function on occasion, but not always, when I'm doing demos. If you want the atomic value of an attribute, rather than the attribute node itself, data() is your friend. data() makes an atomic value from any XQuery item, but with attribute nodes it's […]

Checking for dangerous attributes at catalog time

There are a number of different ways that SQL Server 2005 checks to see whether on not you're trying to catalog or execute dangerous .NET code. This checking is enforced by SQL Server at CREATE ASSEMBLY time and also at execution time. During CREATE ASSEMBLY, SQL Server reflects on the assembly and check for certain coding practices […]

Using “ordinary” class libraries with SQL Server 2005

If you're developing procedural code, UDTs, or UDAggs in SQLCLR for SQL Server 2005, Visual Studio 2005 has these nice auto-deploy projects called SQL Server projects that you can use. Comes with templates for each SQL Server 2005 item you can develop, just choose a SQL Server and database at project creation and hit “Deploy” menu […]

SQLCLR Optimizations – 2

To squeeze every ounce of performance out of the SqlServer data provider, the rule is to allocate the least number of ancillary object instances possible. In SQLCLR Optimizations 1, I mentioned a mechanism to keep from creating a SqlCommand and associated objects, just to initialize a SqlDefinition. Another performance increase comes by not allocating/using extra buffers. […]

Error 6522 and You

What's error 6522? It's the error that you always get from an unhandled exception in a SQLCLR stored procedure. If I write a SQLCLR error that throws an unhandled exception (let's write one on purpose): [SqlProcedure] public static void ErrorExecute() {     SqlCommand cmd = SqlContext.GetCommand();     // everyone knows Bob can't type     cmd.CommandText = […]

SQLCLR Optimizations – 1

Good T-SQL programmers are always looking to optimize their procedural code. Little optimizations can end up as big savings if the code will be executed many times or if it's used in many places in the application. Good SQLCLR programmers will be doing this too. Recently I ran into a micro-optimization that has promise. Everyone […]

HTTP endpoint session support in SQL Server 2005

I've been trying out the new session support in HTTP endpoints. You manipulate this through SOAP headers, and looking through the list of SOAP headers supported allowed me to understand this feature from an entirely different perspective. It looks like the session support makes this into more of an alternate client stack than a mechanism […]

UDTs and UDAggs – Serialization and Construction

I was talking with Dan Sullivan this week about UDTs in SQL Server 2005 and he's made an interesting discovery. About a week ago, I'd happened to mention to Dan that UDTs and UDAggs (user-defined aggregates) are supposed to have a pattern of "deserialize-do action-serialize". He thought I'd misunderstood. Then he went off to write […]

SqlTypes.SqlXml and XmlReader.Create

As Mark Fussell will tell you, one of the coolest things in System.Xml 2.0 is the static creator methods on XmlReader and XmlWriter. It made his top 10 list, albeit at number 10. I've been looking into how the client side handles SQL Server 2005's XML data type and was bemoaning the fact that System.Data.SqlTypes.SqlXml […]

Fun with displaying UDTs in SSMS

Came across something recently that I thought was odd (or a bug) when dealing with UDTs in SQL Server Management Studio. If I have a UDT, say ComplexNumber, I deploy it to SQL Server 2005. Use it as a column in a UDT table, complextab. Insert a row: INSERT complextab VALUES('1:1i') Then attempt to SELECT […]

Where did the netlibs go?

I’m a person who always likes to know where things live in the OS, database, or whatever product I’m dealing with. Being able to point to a specific DLL or configuration file or registry entry gives me something tangible to hang my hat on, rather than think that things happen “by magic”.   So I […]

SMO and object-relational mapping

I've been looking at the SMO (SQL Server Management Objects) model recently. Because the amount of data they retrieve is potentially large and potentially expensive to produce (say sp_spaceused on every database), they use most/all of the *data transfer optimization* techniques: both sparse and eager population, lazy instanciation, caching, deferred/chunky writeback, and locality of reference. […]

On SELECT statements and SqlDependency

There have been a number of questions recently (well OK, three) on the beta newsgroups about SqlDependency problems. It's been suggested that SqlDependency doesn't work in Whidbey Beta 1. The reason for this is that SqlDependency (and, of course, its lower-level cousin SqlNotificationRequest) requires a “valid” notify-able SQL query to work. What's a “valid”  notify-able query […]

Killing a pesky query notification subscription

Just when I thought I'd found all the new cool features. An Ascend Phase 1 participant once asked me “can the DBA get rid of unwanted query notification subscriptions”? In Beta2 you can. SELECT * FROM sys.dm_qn_subscriptions – pick the ID of the subscription that you want, then – say its ID = 42 KILL […]

No more Microsoft.VisualStudio.DataTools.SqlAttributes.dll

Sometimes you have to hit me over the head to make me aware that something's changed. I've been working with Visual Studio 2005 beta 1 for about 10 days now, and just noticed that creating a SQLCLR project (that's [Language of Your Choice]/Database/SQL Server Project) no longer includes a reference to Microsoft.VisualStudio.DataTools.SqlAttributes.dll. This was the DLL that […]

“Rite of passage” programs and SQLCLR

When you get a brand new tool, programming language, or API, the first thing most programmers use it for is to write their favorite "rite of passage" program. This is an example that is so simple, it's only purpose is to illustrate the that compiler or tools are installed and working correctly and that "the […]

About Web Services and “Schema + Any”

About a month ago, there was another chapter in the long discussion about DataSets and web services. An old cohort Scott Hanselman fired off the first salvo, which was taken up by Ted Neward. Doug Purdy responds that Purchase Orders are root of all evil and lots of other chimed in. I'm just catching up… This comes […]

Have a SNAC

If you've recently installed the SQL Express version of SQL Server 2005, you may or may not have noticed SQL Native Client (SNAC). If you want to use the new features of SQL Server 2005 like multiple active resultsets or snapshot isolation from OLE DB, ADO, or ODBC, you're going to need SNAC. SNAC is […]

A Comment About Comments (XQuery Comments)

I was checking some of the SQL Server 2005 features on the way over on the plane. The latest build that I have is SQL Express build, fairly recent. Many of the XQuery features have been updated to Nov 2003 XQuery spec. Very cool. But comments wasn't one of them. A nit, I know. The {– […]