The SQL Server 2005 Integration Services team has posted a draft of my SSIS Connectivity Whitepaper on their connectivity wiki at http://ssis.wik.is (note the change in the address of the wiki site). It addresses the details of using OLE DB, ADO.NET, and others with SSIS and the database/data source of your choice. Check it out.

If you've had success (or issues) with using SSIS and any type of data, I'd like to hear about it.

Enjoy...

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 a hint on encrypted vendor-supplied code.
A. No, per books online you cannot create an OBJECT plan guide on any object that is itself encrypted or references an encrypted object. You get error message 10512 if you try.

Q. Can you create a plan guide on the SQL statement (rather than an OBJECT plan guide) on a SQL statement that I know exists as part of an encrypted procedure and have the guide be used?
A. You can create a SQL-type plan guide on a statement in a procedure, but it won't be used in any case; you need to create an OBJECT-type plan guide for it to be usable inside a procedure. You can confirm its "non-use" by looking at the showplan output from an un-encrypted procedure with a SQL-type plan guide; you can't see an XML showplan for statements in an encrypted procedure.

Q. Can I have more than one plan guide? For example to ENABLE/DISABLE different guides at different times of the day? Can I have two guides enabled at the same time?
A. You can't create more than one plan guide on a single statement/module-or-object combination, whether they are DISABLEd or not. You get error message 10502 if you try. You'd have to DROP the first plan guide before you can create the second one.

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 named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"

Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so...

create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  -- bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  -- bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

-- creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

-- drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:

EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)

Hmmm....

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 the books online, but can be verified with a simple sample, but there were a few surprises.

1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement of an indexed view, but usually when creating VIEWs WITH SCHEMABINDING that reference UDFs, the UDF must be defined WITH SCHEMABINDING as well. SQLCLR UDFs can't be explicitly be defined WITH SCHEMABINDING, but they can be used in views defined as WITH SCHEMABINDING.
2. The VIEW can't use a SQLCLR derived column as part of the index key unless the derived column is declared as PERSISTED in the base table.
3. The SQLCLR function must be declared Deterministic and Precise, and do no data access using SqlCommand, etc. DataAccess = None, SystemDataAccess = None is the default in SQLCLR functions anyway. And no external access is allowed.
4. An indexed view cannot contain a SQLCLR user-defined aggregate (UDA) function.

To demonstrate, I wrote two functions AddOne in SQLCLR, TSQLAddOne in TSQL. You can use AddOne in an indexed view. You can use AddOne as a key column in an indexed view if its defined as persisted in the base table. Note that the TSQLAddOne can be used as an index only if its defined WITH SCHEMABINDING.

This is fairly similar to the restrictions for TSQL functions and indexed views, except that you can access data in TSQL function and use it as the key, only if the TSQL function is defined with schemabinding. SQLCLR functions can't be defined with schemabinding, because there is no way to figure out which tables, etc, it's accessing. So because you can declare a TSQL function with schemabinding, you can probably make it part of the key if you access data. But, as when using a SQLCLR function in a VIEW WITH SCHEMABINDING, this shouldn't be an issue when your SQLCLR function does no data access. Hmmm...

Check out "table3 and view3". It uses SQLCLR function in a persisted computed column, and then as the KEY in an indexed view derived from the table.

I'm at ITForum in Barcelona, day 2. I've got the 17:00 talk and its on Troubleshooting Service Broker. It's warm and sunny outside, especially for November, and I'll need to think up something extrordinary to keep people inside and at the talk. How about having Service Broker make the sun shine inside programmatically..you can do anything with a database, right? Unfortunately, the .NET assembly System.Sunshine.dll is not on the "approved assembly" list for SQLCLR. Must not have done that reliability testing that SQLCLR folks require.

For everyone that was at my Key Management talk yesterday and said they couldn't live without the demo scripts I was using, the scripts will be, along with the Broker Troubleshooting and the XML for DBAs scripts, on the SQLskills website when the conference ends. Look under the "Past Events". By next week, this will be a "past event" and the sunshine a distant memory. Sigh.

I'm at ITForum in Barcelona this week. I'll be speaking on three topics that are:

1. Completely or almost completely new in SQL Server 2005
2. Take some DBAs a bit out of their comfort zone

If you've been avoiding these features until now, its time to take the plunge and let these features help you, rather than avoid them and do without the beneifts they provide. The topics are:

1. XML for DBAs - Showplan, DDL triggers and lots of things other things YOU use are XML.
2. Key Management - this is useful even if you don't do data encryption. Linked servers and SQL Agent use encryption keys.
3. Service Broker troubleshooting - Event Notifications and Database Mail (among others) use Service Broker.

See you there.

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 answer is no. You need a separate queue for each subscriber, otherwise the SqlDependency listener gets "confused". If you use the dynamically created queues like most people do (SqlDependency.Start with only a connection string), you get a new queue and service for each subscriber. So you won't notice this. I thought I'd have better luck with the SqlNotificationRequest because I'm reading the queue myself, but without going to the system tables (and perhaps introducing race conditions) its not possible there either.

Queue are cheap to create, so there is really no reason not to create a separate pre-provisioned queue and service for each notification subscriber. But, at least for the time being, its a requirement.

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 weeks ago in a class, and again this week at TechEd, someone suggested the simple, relatively low-tech solution; "Just add 50000". RAISERROR to rethrow and subtract 50000 at the client. Unless you have already defined user errors in the range that can't be rethrown + 50000 (up to 13000), this should work fine. Thanks for the suggestion.

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 written "on the fly" and really need better argument validation and error checking, but they seem to do the job. Some of these come from the SMO chapter in Dan Sullivan and my "A Developer's Guide to SQL Server 2005 " book. Enjoy!

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 think of SQL "scripts" being lines of code written in T-SQL, with SMO and Powershell the term "SQL scripts" takes on a somewhat different meaning. If you're at TechEd, stop by and we'll write some new-fangled "SQL scripts". Although some things that SMO does actually generate registry changes, the "real" T-SQL equivalents for most things SMO are still available using the SMO scripter.

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 is query plan reuse with 1-part object names. Create two users:

-- logins already created
CREATE USER bob FOR LOGIN bob
CREATE USER mary for LOGIN mary
GRANT SELECT ON authors TO bob, mary

The query plan for the following query (executed by bob or mary)

USE pubs
GO
SELECT * FROM authors -- note the 1-part object name

would not normally be reused for both bob and mary. In SQL Server 2005, it can be reused if bob and mary have the same default schema. BTW, this is adhoc SQL, rules differ in procedural objects like stored procs. To see the reason for the query reuse, you can obtain the plan handle (reference this blog entry), but leave out querying the plan itself because you don't need it here) and pass it in to the sys.dm_exec_plan_attributes dynamic management function. This function shows the attributes of the plan and includes a second column, is_cache_key. If the cache_keys match, the plan is reused.

One of the rows produced by sys.dm_exec_plan_attributes is named user_id. Interestingly, for the query plan for bob or mary against SELECT * FROM authors, the user_id is 1. DBO. Hmmm... User_id is a misleading name for this column. It's not the user_id of the user who executed the query (and caused the plan to be created) but that user's DEFAULT_SCHEMA's owner's user_id. Looking at the CREATE USER DDL statements above, because I didn't specify a DEFAULT_SCHEMA, both users' DEFAULT_SCHEMA is DBO. So, for adhoc SQL, all other things being equal, the plan will be resued for two users as long as they have the same DEFAULT_SCHEMA. The rules for query plan reuse follow the rules for object resolution, which makes sense, once you think about it.

Of course, now that you know this, ALWAYS use 2-part names. User_id with 2-part names' plans has a special value of -2 which ensures the plan is resued regardless of DEFAULT_SCHEMA of the user who executes it.

Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question.

LINQ and Entity SQL are QUERY languages. Although they might be able to encapsulate resultset output from stored procedures, much of their value is in the query language itself. NOT having them be useable in SQLCLR would encourage folks that want to use these languages to move AWAY from stored procedures and put their data access query code in client or middle-tier programs, rather than in the database. I don't think DBAs and database developers really want to encourage that.

On the other hand, some of the value of stored procedures is being able to restrict table permissions and find errors at compile time. SQLCLR procedures don't have either of those attributes built-in, although you can restrict table permissions if you use EXECUTE AS OWNER. And coding with LINQ/Entity SQL can allow compile-time type metadata checking. Other benefits of stored procedures, such as allowing the code to be shared among database apps, and consolidation of SQL code in database objects, do apply as well to SQLCLR.

And its a given that, with either of these languages (and with SQLCLR in procedures in general) you'll be giving away procedure execution speed for (*arguably* better, meaning you'all can argue about it) code maintainability. Unless you only use LINQ/Entity SQL to process T-SQL stored procedure resultsets.

What do YOU think?

Haven't written in a while. I've been back from Hong Kong for a few weeks (had a fantastic time, thanks folks), and, a few days later, contracted the (American) flu that's been going around here. Just getting over it. Also, I've been busy working on some whitepapers that will come out (relatively) soon. I'll announce it here when they are available.

I'm headed off to Barcelona in a few weeks to TechEd and ITForum. I'll be speaking about SQL Server 2005, on some topics that folks ask about in class often (now that they've heard of the features). But there usually isn't time to go over these in as much detail as I'd like. These talks are an effort to make the time. At TechEd, I'll be speaking on:

Plan Guides and Plan Forcing
Query Notifications / Cache Sync
Error handling on server and client
Implementing a Service Oriented Database Architecture application
MARS, transactions, and SQLCLR (for a preview of who uses MARS, read back a few blog entires)

and a chalk talk on maintaining your system with SMO. Ought to be fun.

For the IT Professional folks at ITForum, they'll be:

XML for the DBA (because the list of SQL Server DBA items in XML format is about a mile long now)
Troubleshooting Service Broker (even DBMail uses broker)
Security Account and Key Management

Hope to see you there. Stop by and say hi.

I did a talk yesterday at TechEd Hong Kong on ADO.NET vNext. Most of them just wanted to know what ADO.NET vNext was about. Short and sweet. Here goes...

There are two main new features:
1. Entity data model (EDM) - a mapping of objects over relational database to bring data access to a higher level of abstraction. No longer do you need to code to the DBMS schema, only to the entity schema. Think of it as a new data source.
2. Entity SQL - a new query language, SQL plus extensions to query against the entity data model (EDM). A bit richer than SQL, but SQL-like.

And some integration pieces:
1. An ADO.NET provider over the EDM (the MapProvider). Uses ESQL queries.
2. An ObjectServices layer over EDM. Reminiscent of ObjectSpaces (at least to me). Uses Query<T> and an ObjectContext.
3. An integration piece that allows LINQ over the EDM. "LINQ to Entities".

LINQ for Entities joins:
  LINQ to Objects
  LINQ to SQL (was: DLINQ)
  LINQ to XML (was: XLINQ)
  LINQ to DataSets
  PLINQ (parallel LINQ)

That's all folks.

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 least privilege, run SQL Server and associated as the minimum privilege account needed. If I thought about changing the service account, I'd call up knowledge base article Q283811 and sigh at the list of work items. And make sure that I used Enterprise Manager, as the article recommends.

Things are a lot simpler in SQL Server 2005. When you install SQL Server, the process creates Windows groups with exactly the minimum privlege that you need for the appropriate services. The user you specify during the install (which should be a "normal", low privilege user and NOT administrator or LocalSystem, is plunked into the appropriate group. There is a Windows Group for each SQL Server-related service, all nicely decked out with least privileges (file system DACLs, security policies, registry key access, etc). NO more privilege lists to keep around, although they are doc'd in BOL should you need to refer to them.

If you should need to change the service account, things are even better. Just put your new Service Account user in the appropriate Windows groups, and voila, no long list of tasks. Very cool. Or use Configuration Manager.

Twas' not all sweetness and light, however. an FAQ during the early days was "why does SQL Server setup "clutter" my directory with groups?" That's why. First time you need to change service accounts, you'll appreciate the "clutter".

There was a newsgroup discussion recently about the intricacies of Multiple Active Resultsets in SQL Server 2005, and one of the questions that came up was "Who actually needs/uses MARS anyway"? I've been taking a much closer look at ADO.NET vNext lately, one reason being that I'm doing a talk on it at TechEd Hong Kong the week after next. Guess what? Both the supplied ObjectServices and LINQ for Entities samples have "multipleactiveresultsets=true" in their connection strings. Why? Without taking MARS out and seeing where the samples fail (I'll get around to it...), I'd bet they use both the "multiple resultsets on the same connection" and "issue an UPDATE statement while reading a rowset on the same connection" patterns. That's what MARS is supposed to address (the very precise ODBC error message for this is "Statement in Use").

BTW, MARS has some interesting effects on transactions and session settings, too. I'll be doing a talk on this at TechEd Europe in Barcelona in Novemeber. Don't forget to stop by and say hi if you're at either of these excellent events.

Thanks to those who came to my webcast today on SQL Server 2005. Between this webcast and the other upgrading webcasts that are available on the Technet event website and the MSDN event website, you should have a pretty good idea about why you need to upgrade to SQL Server 2005. If you haven't done that already.

As promised, here are the demos and scripts from the session, on the SQLskills website. I'll post a Q&A blog entry and a pointer to the webcast as soon as I have the information.

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 but also Analysis Services 2005. A MUST read. Look for "Failover Clustering for Microsoft SQL Server 2005 and SQL Server 2005 Analysis Services" at Microsoft download site.

I've been honing my slides and polishing up my demos for TechEd 2006 Hong Kong at the end of September. I'll be doing some breakouts there on SQL Server 2005 and ADO.NET vNext, but there will also be a "Day of SQL Server 2005 for Developers", as a preconference presentation. There's so much that's new in SQL Server 2005 that it's impossible to fit it all in a single day, but I think that even if you were at last year's TechEd, you'll be pleasently surprised at some at the newer in-depth material from a slightly different perspective. It will be a FULL day.

By the looks of the speaker list, I'll be in good company in the SQL Server space, with experts such as Paul Randal (check out his latest amazing entries on the SQL Server Storage Engine blog) and Thierry D'Hers. And my old cohort Jon Flanders will be there too, captivating attendees with Windows Workflow Foundation and Biztalk, no doubt.

It will be good to see old friends again, too. Stop by and say hi. See you in Hong Kong!