I've answered a few questions lately on setting up SqlDependency or ASP.NET SQL Server dependency with SQL Server 2005. Folks have gone by the instructions on the DataWorks Weblog posting and still receive the error "either schema dbo does not exist or you do not have permission to access it". This is caused by the separation or users and schemas in SQL Server 2005.

The instructions don't show creating 'startUser' (the principal that creates procedures, queues, and services), so folks create it, using the new DDL, like this:

CREATE LOGIN startUser WITH PASSWORD = 'SomeStrongPW1'
CREATE USER startUser FOR LOGIN startUser

Problem is, CREATE USER doesn't assign a default database schema (its not supposed to) and when startUser attempts to create database objects, it creates them in the "default default_schema" which is dbo. The quick fix is to create a schema for (owned by) the user and make that schema its default schema.

CREATE SCHEMA startUserSchema AUTHORIZATION startUser
ALTER USER startUser WITH DEFAULT_SCHEMA = startUserSchema

A better alternative might be to create a database role for this function and create the default schema owned by the role. Then add startUser to the role. You still have to alter the user's default_schema in this case, because database roles cannot have default schemas. Roles cannot have default_schemas themselves because if one user was a member of 3 different roles and each role had a different default_schemas which one would "win"?

A less attractive (actually unattractive) alternative is to give startUser CREATE (actually ALTER) privilege on the DBO schema.

GRANT ALTER ON SCHEMA::dbo to startUser

DON'T do this, you've just given startUser much more privilege than it really needs.

Three more comments:
1. If you used sp_adduser instead of CREATE USER, you "got lucky". For backward compatibility sp_adduser actually does:

CREATE USER startUser WITH DEFAULT_SCHEMA = startUser
GO
-- create schema must be first statement in the batch
CREATE SCHEMA startUser AUTHORIZATION startUser

When/if that backward compatibility mode is removed, your luck runs out.

2. With the new separation of users and schemas granting CREATE TABLE permission doesn't give the user enough to create a TABLE, nowadays. The user also needs a *container* to create tables (or other database objects) in. The user needs a database schema. Resist the temptation to make a schema for the user; rather make a schema for a role the user is a member of. The fact that schemas can be owned by a role is one of their best features.

3. Technically, Service Broker SERVICEs (as well as MESSAGE_TYPEs and CONTACTs) don't live at schema scope. They live at database scope, so you don't need a schema for them. CREATE privilege is enough. But QUEUEs (and most database objects) do live at schema scope. QUEUEs are just tables with special semantics, after all.

I've been confirmed to present some talks at DevDays Netherlands on March 7-8. I'll be talking about my favorite subjects, SQL Server 2005 and ADO.NET 2.0. It will be good to catch up with the folks from Class-A. Anko, Astrid, and Alex will also be presenting. I'm trying to line up some advance copies of A Developer's Guide to SQL Server 2005 , but that may be pushing the scheduled publication date a bit.

After DevDays I'll be giving an Immersion Event in the area covering SQL Server 2005 in depth, from a development perspective. I'll even be talking about best practices now that folks, especially the early adopters who developed products to be released along with the SQL Server release itself, have had a chance to practice.  If you've not had a chance to explore SQL Server 2005, now's your chance. See you there.

I've been getting some mail messages that the sample chapter of our latest book that we uploaded on the SQLskills website is not readable. Or in some cases, not downloadable. The error messages indicate that you need the latest version of Acrobat Reader (downloadable from Adobe) to read the chapter. The “not downloadable” message may be an indication of not having the latest version of the plugin. If you still have problems after getting the latest version, please let me know.

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 one of these up today...but...HTTP endpoints are not supported on Express edition. Or on Workgroup edition for that matter, you must have Standard edition to define and use them. So you must have at least Standard edition to setup such a gateway.

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 long they have actually been waiting. Or at least how long the database has been waiting. SQL Server 2005 will tell you, at least when locking is involved.

There is a configuration parameter "blocked process threshold" that controls if and when to send notifications whenever a process is blocked for N seconds. You set this through sp_configure; its an advanced option so show advanced options must be on too. It's set to 0 (never notify) by default. You can catch these notifications through WMI/SQLAgent alerts or the new event notifications. Very cool.

So now when a user reports "I've been waiting for this database query for 10 minutes" but the query response is immediate when you check it (the "works for me" answer that users hate), you can see (after the fact) if its lock contention that caused the waiting. “Well, you weren't waiting for locks” might be a better answer. Maybe. 

Dan and I have been permitted to post a sample draft-quality chapter and the table of contents our upcoming book, "SQL Server 2005 Developer's Guide". They're available on the SQLskills website as links from the Resources/Books menu entry on the homepage. The sample chapter discusses using the SqlClient data provider in SQL Server procedural code. Feedback appreciated. Book becoming available on shelves (relatively) soon. Enjoy.

Special thanks to our astute technical reviewers.

Now, back to work for me...

With apologies to Johnny Winter. Long time, no postings. You might have guessed why I disappeared, but Dan and I just finished and handed in the sequel (pun intended) to the "First Look" book, entitled "A Developer's Guide to SQL Server 2005". How I spent my winter "vacation"... Whew.

We've got everything updated to RTM, LOTS of new/updated material on every topic (especially on security) and a new chapter on SMO. Those SQL Server 2005 folks were busy since beta2. Now we'll see how long copyedits, production, etc, takes. Should be on shelves by late Q1-early Q2. We'll be posting a sample chapter shortly.

Now to figure out what to do with the rest of this year. Started to look at some different ways to "stretch out". Took a quick glance at the new JDBC driver last week. More content coming soon...feel free to send suggestions.

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

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 to move to server B. You don't have the source code. Perhaps it "arrived" pre-deployed on a VPC, as an example. You need the .smdl file. But it doesn't exist standalone, its been deployed to the Report Server. Or at least I didn't find it. I could probably write a program to extract it using the admin web service interface. But I'm too lazy.

So, eventually, I brought up the web interface, selected the model, then, under "Model Definition" chose "Edit". A nice dialog box asked me where I wanted to put the .smdl file. Great, just what I wanted.

BTW, bringing up SSMS, and browsing to the model in Object Browser, the model had a nice dropdown menu item, "Script As...". Problem was, it didn't seem to script anything. I tried all three script options, nothing happened.

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 services run. Not so. You don't need a database master key (and this is in RTM) if:

1. Both services (initiator and target) live in the same database
2. You begin the conversation using ENCRYPTED = OFF in the BEGIN DIALOG statement

ENCRYPTED = ON is the default, and you do need a database master key in this case, hence the confusion.

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 table that looks like this:

CREATE TABLE ddl_log (
 id int primary key identity,
 data XML
);

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
INSERT ddl_log VALUES(EventData);

This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I'd seen this before, but can never seem to ever have found it. Now that I've done this, ....learn XML why don't 'ya... There can only be more of it in future. Cheers.

SELECT id, 
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 Tab.Col.value('./SPID[1]','nvarchar(50)') AS  'SPID',
 Tab.Col.value('./ServerName[1]','nvarchar(50)') AS  'ServerName',
 Tab.Col.value('./LoginName[1]','nvarchar(50)') AS 'LoginName',
 Tab.Col.value('./UserName[1]','nvarchar(50)') AS 'UserName',
 Tab.Col.value('./DatabaseName[1]','nvarchar(128)') AS 'DatabaseName',
 Tab.Col.value('./SchemaName[1]','nvarchar(128)') AS 'SchemaName',
 Tab.Col.value('./ObjectName[1]','nvarchar(128)') AS 'ObjectName',
 Tab.Col.value('./ObjectType[1]','nvarchar(50)') AS 'ObjectType',
 Tab.Col.value('./TSQLCommand[1]/CommandText[1]','nvarchar(4000)') AS 'CommandText',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS','nvarchar(3)') AS 'ANSI_NULLS_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT','nvarchar(3)') AS 'ANSI_NULL_DEFAULT_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING','nvarchar(3)') AS 'ANSI_PADDING_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER','nvarchar(3)') AS 'QUOTED_IDENTIFIER_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION','nvarchar(4)') AS 'ENCRYPTED_OPTION'
FROM ddl_log
CROSS APPLY
 data.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

-- standalone trigger

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @x XML
SET @x = Eventdata()
SELECT
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 -- rest of columns deleted for brevity
FROM @x.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

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 make SQLCLR acts the same way that T-SQL acted with respect to errors. Then, you could replace T-SQL functions and procedures with SQLCLR without changing every caller.

6522 (that's a general error that means "an error in the SQLCLR stack occurred) is still with us. You get one if there is an unhandled exception in ExecuteNonQuery or ExecuteReader inside a SQLCLR proc.If you want this error to go away and want only the original SQL error (e.g. 547 referential integrity error) to be returned to the caller, the only way to do this is to use SqlPipe.ExecuteAndSend in your SQLCLR procs. If you have no catch block, both the 6522 and the "original" (e.g. 547) error will be returned. If you're called from client code, the error number is e.g. 547, the 6522 follows afterward. If you want to "lose" the 6522 altogether use ExecuteAndSend and use a dummy try-catch block in your SQLCLR code. It would look like this:

try {
...
...
SqlContext.Pipe.ExecuteAndSend(cmd)
}
catch { // no code here
}

In this case you only get the original error e.g. the 547. Note that if you use SqlCommand's ExecuteNonQuery or ExecuteReader with the dummy try-catch concept, you lose the error entirely.

Bear in mind that if you use T-SQL's TRY-CATCH in SQL Server 2005, there is no way to raise *exactly* the original error either. You can come close, but the is no RETHROW. You can use RAISERROR, but RAISERROR doesn't work with system errors. You can reformat the original error message in a user error.

Upshot of this is that if you convert to either SQLCLR or T-SQL TRY-CATCH and depend on seeing the original error number at the caller (normally the case even if you have error handling in your procs), you're going to be changing the callers' code. Or using only ExecuteAndSend.

I'm headed out on the road. For quite a while. Wanted to settle this before I went.

Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and a Service Broker Dialog Timer. Every 120 seconds, the timer fires, which activates the procedure, which sets the timer again. The first time I saw this, I thought the procedure was polling. Actually its more like its trauling. But its not polling (whew).

The way SqlDependency works now is that requests for query notifications set up their own broker services to catch the notification message. The activation proc (also set up for the notifications) relays the notification message back to the client. Which calls your event handler, or in the case of ASP.NET's SqlNotificationRequest, invalidates the cache.

So what's the timer for? The timer is trauling to find services and procedures (they have GUIDs in their names) set up by SqlDependency instances and will clean them up if it finds any. This is NOT on an individual SqlDependency instance basis, but on a *per-listner* basis. That is, once for every client that calls Start.

Take home message is, now more than ever, you need to limit the number of clients that call Start. Maybe only ASP.NET (or other) caches???

I'm glad that's settled. See you on the road I'm teaching/talking about this more in person...c'mon over and say hi.

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 the readme). In the requirements file (ok, so I'm not done yet), this caught my eye:

“Native Web Service (SOAP/HTTP) support is only available for instances of SQL Server 2005 running on Windows 2003”

Reason this interested me is that, AFAIK, the technical requirement for HTTP in SQL Server 2005 is an OS that supports HTTP.sys, that is the kernel-mode HTTP stack. But Windows XPSP2 supports this too. During the beta, it was listed as OK, and the devs even answered newsgroup questions on problems with that configuration.

So I deduce what this means is, although SOAP/HTTP would technically work on XPSP2, its not officially supported, i.e. don't call tech support with your problems when using this configuration.

Or am I reading too much into one sentence in a readme file?