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 to accomplish this is to use FORMAT=NONE in your WEBMETHOD declaration.

Usually HTTP endpoints return an XML complex type, SqlResultStream; you get this behavior with FORMAT=ALL_RESULTS, the default. FORMAT=ROWSETS_ONLY produces only SqlRowSet complex types, this shows up in .NET clients as a DataSet. FORMAT=NONE is the alternative. When you specify NONE, SQL Server doesn't even bother to wrap the result in a SOAP packet. You can use this for sending *anything* back to the client. Although your response can be a nicely formatted SOAP packet, it can also be any XML, more complex SOAP (e.g., additional SOAP Headers). The response doesn't even have to be XML at all, anything can be streamed back to the client, although this has a potential for surprising the receiver.

You use FORMAT=NONE with a stored procedure that has no input or output parameters. It must return a resultset with a single "column" with the special name "XML_F52E2B61-18A1-11d1-B105-00805F49916B". This name is actually an indicator to the underlying protocols that the result should be streamed back to the client, rather than formatted into columns and rows. You might be wondering where you've seen this column name before; if you use SELECT...FOR XML queries, this is the “column” that these queries return. You can also return raw SELECT...FOR XML output using FORMAT=NONE.

When I first heard of this option, the use that immediately came to mind was support of advanced web service procotols. Although I can *output* non-vanilla SOAP using FORMAT=NONE, there doesn't currently seem to be a way for a "webmethod" stored procedure to get ahold of the raw *input* message to an HTTP Endpoint. So, right now, I can't send complex input and have the stored procedure respond to it. Hmm...

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 undoc'd but work in the CTP
 print error_line()           -- line number
 print error_procedure()  -- procedure name
end catch
go

execute deletejob 2
go

I've been using Visual Studio 2005 for over a year, since before the alpha version came out. Recently, I've had occasion to use VS 2003 and confirm what I thought I'd noticed. At first I thought it was an "alpha anomaly" that would be fixed, now I'm not sure.

Has anyone else noticed the autocomplete is quite "agressive" in VS 2005. I'm not the fastest typist, but no sooner does the letter "a" leave my fingertips but the word Application (or something similar) appears. I can't even use "conn" as an variable for Connections anymore, because the only way I can get autocomplete to leave it alone is to let it complete and backspace over the autocompleted class name. Going back to VS 2003 confirmed to me that it's new/different with VS 2005.

Does anyone notice this beside me? Does anyone know a way to turn down the "autocomplete promptness" setting? Autocomplete is normally one of my favorite VS features.

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 programmers to use a single coding style. You'll be able to distinguish whether you're running in-server by either a bit switch or a connection string parameter. Even though I expressed my preferences to the teams, here's a couple of things I wanted to reiterate.

1. Keep SqlDefinition and SqlExecutionContext for the in-proc provider if at all possible. It's a nice way to optimize execution on the server, even if folks do tend to deduce (incorrectly) that it's related to prepared statements.

2. I've gotten to like SqlCommand's ExecuteSqlScalar method. This currently exists on the SqlServer provider but not on SqlClient. It should exist on both/merge. Here's why:

If I have an aggregate or scalar that can return NULL, it takes something like this code to use this with ExecuteScalar.

// this return NULL if no rows in table
SqlCommand cmd = new SqlCommand(
 "select max(id) from test", conn);

SqlInt32 i;
Object o = cmd.ExecuteScalar();
if (o.GetType() == typeof(System.DBNull))
  i = SqlInt32.Null;
else
  i = new SqlInt32((int)o);

Here's the code using ExecuteSqlScalar in SqlServer provider:

SqlCommand cmd = SqlContext.CreateCommand();
cmd.CommandText = "select min(id) from test";
SqlInt32 i = (SqlInt32)cmd.ExecuteSqlScalar();

Much cleaner, yes?

One of the most amazing things that happened at the class last week was Wednesday night, when Dan Sullivan and the SQL Server Service Broker team did “Night of the Service Broker”. Dan did a couple of back-to-back broker talks. Then, the entire SQL Service Broker team, lead by Gerald Hinson and Roger Wolter, participated in a panel discussion on how to effectively write apps that used broker and what made Service Broker unique.

Then Dan announced his new SQL Server Service Broker Developer's Spot. The site will host discussion forums, articles, tutorials, and also host cooperative development of some interesting service broker apps. It's open now, and they'll be sample applications (including the Service Broker client object model, courtesy of the team) up there shortly. So go on up and sign up now, if you're at all interested. Dan, me, Niels and others will see you there.

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 expose read-only static properties for Connection, Command, Pipe, etc, rather than using static methods named GetXYZ. So:

GetCommand -> replaced by CreateCommand
GetConnection -> Connection property
GetPipe -> Pipe property
GetTransaction -> Transaction Property
GetTriggerContext -> TriggerContext Property
GetWindowsIdentity -> WindowsIdentity Property

The old methods are still there (for now), but there may need to be a "mass rewrite" of beta code, samples, etc, soon. Just to let you know...

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:

CREATE PROCEDURE some_ws (
 @somexmlinout   OUT xml(someothercoll))
AS
-- your code goes here
-- or your external name does

In previous versions, the HTTP Endpoint that defined this web service generated WSDL that, when uses with VS-proxy-generator, looked (coersed into objects) like this:

some_wssomexmlinout i = new some_wssomexmlinout();
some_wsResponsesomexmlinout j = new Responsesomexmlinout();

Object[] o = endpoint.some_ws(i, out j);

Representing the SQL OUTPUT variable as two variables not only made this more complex, but freaked out VB.NET proxy which doesn't really have a built-in concept of output-only parameters (though its possible to do with an attribute).

In Dec CTP, the "universal web service" builds proxies that look like this:

some_wsTypesomexmlinout i = new some_wsTypesomexmlinout();

Object[] o = endpoint.some_ws(ref i);

That's better. Even VB.NET likes it. Now if only I could provide a schema collection (or indicate in the endpoint definition) so that the WSDL would reflect (the only) two types in the namespace. Rather than it's (completely correct) definition of this as "any" from a collection of namesapces (those defined by the schema collection).

Well, unitl that happens, there is always custom WSDL generation...

Notice that this MUST have been a fix to HTTP Endpoint WSDL-generator, because my version of Visual Studio web service proxy generator (Oct CTP) was unchanged. Cool.

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 the system views).

So if the class Tiger inherits from the class Cat. Passing an instance of Tiger to the T-SQL stored procedure FeedTheCat:
create procedure FeedTheCat (@thecat Cat) .... -- T-SQL here --
wouldn't work.

-- (Msg 206: operand type clash dbo.Tiger is incompatible with dbo.Cat)
declare @t tiger
execute feedthecat @t

-- or this (Msg 529: explicit conversion ... is not allowed)
declare @c cat
set @c = cast(@t as cat)
execute feedthecat @c

If the Cat class has a public instance method called FeedMe and a public field called pawcount (both are inherited by Tiger), this wouldn't work in T-SQL:

-- this wouldn't work
declare @t Tiger
print @t.FeedMe()
print @t.pawcount

-- this would
declare @c Cat
print @c.FeedMe()
print @c.pawcount

You could, however, access these fields/methods on Tiger from .NET code, As in:
// works fine
Tiger t = new Tiger();
int paws = t.pawcount;
// so does this
SqlString s = t.FeedMe();

Calling it in .NET code *through SqlCommand.ExecuteReader* (CommandText = "select sometiger.pawcount from zootab") wouldn't work.

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 new build. Here's some that took 5 minutes after installing to test:

1. "Smiley face" XQuery comments still don't work. {-- comment --} still does.
2. INTERSECT and EXCEPT still not there. They are however, still in BOL.
3. FOR XML still does not work with UDTs. However, convert UDT to XML type DOES.

create table ctab (cnum complexnumber)
insert ctab values('1:1i')
insert ctab values('2:2i')

-- works! 2 rows returned with nicely formatted XML in them
select convert(xml, cnum) from ctab

-- fails, Msg 6865, FOR XML does not support CLR User Defined Types
select * from cnum for xml auto

I wonder why one works and not the other. Should use almost the same codepath for serializing UDT. Oh well. That's all for now. More later.

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 give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There are two ways to tell who owns a table. If you know who the schema owner is, "select * from sys.tables" produces a column named principal_id. If the principal_id is NULL, then the table is owned by the schema owner. If the principal_id is not NULL, the table has a specific owner. The other way is to use the OBJECTPROPERTY function and look for the property 'OwnerId'. This gives the exact owner, whether or not it's the schema owner.

This matters because, if you change the schema owner, the owner of the tables with NULL in prinipal_id changes. The owner of "specific-owner" tables does NOT change. So if the schema owner changes, say, to DBO, then DBO owns all the tables in the schema. BUT does not own Ed's table.

This whole thing is made possible because of the interesting meld that had to happen between a SQL Server-specific feature (ownership chains) and a new SQL2003-compliant feature (separation of users and schemas). Is this clear as a bell, now?

Just in case you don't believe it, code below (picks up where other code left off):

-- snip (when I left off, I was Ed)
-- ed cannot get ownership of table
-- this fails
alter authorization on object::fredstuff.edtab to ed
go

-- back to dbo
setuser
go

-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go

-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go

setuser 'ed'
go

-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go

-- now ed can SELECT the table
select * from fredstuff.edtab
go

-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go

-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

setuser 'fred'
go

-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go

setuser
go

alter authorization on schema::fredstuff to dbo
go

setuser 'fred'
go
-- no access for fred on this table
select * from fredstuff.edtab
-- access for fred on this table
select * from fredstuff.table1
go

setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go

select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change

select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

-- snip --