SQL Server Reporter

Get Top Posts and Jobs Weekly via Email:

Subscribe Unsubscribe

215 posts from: Bob Beauchemins Blog

---

LINQ to SQL and Entity Framework: Panacea or evil incarnate? Revisited.

(Indexed 2010-07-01):

I received an interesting question today that I started to answer in email, then realized it would make a good blog post. It went something like..."Do you know if EF4 addresses the Bob Beauchemin’s criticism in his “LINQ to SQL and Entity Framework: Panacea or evil incarnate?” blog entries?" First off, the series was not a blanket criticism of LINQ to SQL or Entity Framework, but a set of concerns, purely from a "database programmer's" point of view. That is, I'm comfor...(truncated)...

Updated serialization formats for SQL Server spatial types

(Indexed 2010-06-21):

Everyone's on the lookout for info about features of SQL Server VNext. The docs folks may have given us some insight in the publication of an update of the "Microsoft SQL Server CLR Types Serialization Formats [MS-SSCLRT]" spec. This includes updates that "apply to SQL Server Denali CTP1" according to footnotes in the spec. A message about this spec update was posted on the Technet SQL Server spatial forum. The spec is publically posted here. But remember, its just a spec...so far. Besides a...(truncated)...

SQLCLR debugging and VS 2010 revisited

(Indexed 2010-06-21):

In my blog entry from about a month and a half ago, I summarized my finding on using Visual Studio 2010 with SQLCLR, and the partial support of SQL Server 2008 new features. One feature (with the disclaimer that's it's not preferred/supported) that I thought I'd lost was "attach to process" style debugging with SQLCLR. And that I hadn't discovered the "secret sauce" that makes this work. Here's the secret sauce. Robert Bruckner's blog entry (about debugging SSRS add-ins) ...(truncated)...

Thanks for a great TechEd!

(Indexed 2010-06-16):

Last week I was at TechEd in New Orleans, presenting, manning the SQL Server Security and Managability station, meeting with friends and business associates, and, of course, partying. The heat and humidity took a little getting used to, but otherwise it was a great time. Thanks to everyone who attended the sessions; the demos are posted on the SQLskills website as promised. To all of my old friends, as well as the new friends I met in New Orleans for the first time, I hope you enjoyed it as much...(truncated)...

Getting error 2337 installing SQL Server on a VPC?

(Indexed 2010-05-09):

I usually have a number of different versions of SQL Server around, installed on Virtual PCs. Usually works fine. So I was a bit surprised when installing the RTM version of SQL Server 2008 R2 from the downloaded .iso file to receive error message 2337 in the middle of the install. Thinking it was something wrong with my download (IIRC 2337 is a some kind of IO error during install), I re-downloaded the .iso. Same error. I then tried installing SP2 on my virtual Windows Server 2008 OS, then inst...(truncated)...

Recompiling the StreamInsight CTP3 samples for 1.0 - Part 3

(Indexed 2010-05-04):

This posting is a journey through StreamInsight 1.0 APIs, undertaking by porting the CTP3 samples. Part 3. In the StreamInsight CTPs, there existed three methods to code applications. My original post describes the current state of Observable. That leaves us with "Explicit Server" and "Implicit Server". Coding using the "Implicit Server" method, you'd make (basically) two main calls. You'd create a CepStream with input provider information. Then use stream.ToQuery(...) specifying output...(truncated)...

StreamInsight 1.0 RTM: Installation and Samples

(Indexed 2010-05-04):

StreamInsight V1.0 RTM'd a few weeks ago. Although I worked with it, looking especially at the infrastructure and programming models, I just installed the RTM version a few days ago. One thing I noticed immediately upon install is that it requires a license key. This is because there are different versions of StreamInsight based upon which SQL Server SKU you'd installed. See the licensing explanation, versioning, and SKUs information here. Because I hadn't yet installed SQL Server 20...(truncated)...

Visual Studio 2010 and SQLCLR 2008 new features

(Indexed 2010-05-02):

There's a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were told that support would be forthcoming in the next version of Visual Studio for these features.The problem with features not working with autodeploy or not bei...(truncated)...

SQL Server security precon at TechEd 2010.

(Indexed 2010-04-19):

This blog posting is meant to bring attention to the fact that I'm doing a preconference talk, "A Day of SQL Server Security" at TechEd 2010 in New Orleans in June. OK, the TechEd folks asked me to publicize it. I'm also doing two breakout sessions, one on "Entity Framework and LINQ2SQL vs. Stored Procedures", and the other on "Integrating Microsoft SQL Server Event Tracing with OS-Level Events and Database Client Events".Although I've done individual topics of SQL Server Security be...(truncated)...

DAC support SQL Azure and vice-versa. It's live.

(Indexed 2010-04-19):

Last week I did a talk at SQLConnections on SQL Azure Database and Data-Tier Applications (DAC). At the time (it was the day of Visual Studio 2010 launch), I explained that conference abstracts had to be submitted 6 months ago. At the time, because of some coincidental feature correspondence (e.g. the DAC whitepaper suggests only using DAC deployment on databases of 10gb or less; 10gb is the current maximum size of a SQL Azure database) I'd actually thought that DAC and Azure were "joined at...(truncated)...

Geocoding and AdventureWorks 2008 data

(Indexed 2010-04-19):

I did a few demos in the spatial visualization talk at SQLConnections using the AdventureWorks 2008 data. The Person.Address table is all geocoded and so I did a quick map in SQL Server Reporting Services 2008 R2 using individual addresses. Followed up later in the talk with addresses using ESRI's MapIt product, their Spatial Data Service and nice Silverlight controls. Both demos were projected over maps; I used both Bing Maps and a simple ESRI ARCGIS Server map. As I zoomed and panned over ...(truncated)...

Trouble seeing "Birth of Venus" image in the SSMS spatial results tab?

(Indexed 2010-02-28):

Michael J Swart posted a SQL query on his blog that produced a rendition of Botticelli’s Birth of Venus, in the spatial results tab of SQL Server Management Studio when executed. But, when everyone rushed over in anxious anticipation, some folks could execute the query but couldn't see the picture on their 64-bit editions. Here's why... It's an obscure problem with rendering spatially invalid results that surfaced around SP1 timeframe. The map control that produces the tab cann...(truncated)...

Transactions, isolation, and SQL Azure

(Indexed 2010-02-11):

I was trundling through my SQL Azure database today, looking for interesting behaviors. Found one. A "select * from sys.databases" reveals that both "snapshot_isolation_state" and "is_read_committed_snapshot_on" return 1 (on) for all databases. Because ALTER DATABASE isn't supported, these cannot be changed.So READ COMMITTED SNAPSHOT is the default transaction isolation level, which may come as a surprise to those applications that depend on the read committed locking behavior of SQL Server...(truncated)...

A SQL Server 2008 R2 change for Service Broker

(Indexed 2010-02-05):

There's another change to the SQL Server database engine in SQL Server 2008 R2. This concerns poison-message handling behavior in Service Broker applications. Service broker's messaging is always transactional. A RECEIVE SQL statement is transactional and can be combined with other database operations as part of a transaction. For example, you can code a RECEIVE for a Service Broker queue combined with an INSERT of a database row, based on the information in the message. If the transacti...(truncated)...

Filestreams, Remote Blog Storage, and SQL Server 2008 R2

(Indexed 2010-02-04):

As long as I'm blogging about filestreams...Folks always want to know if the filestream information can be stored on a remote share. They look kind of disappointed when they hear that the filestream filegroup must exist on a local (to the SQL Server instance) drive. However... (isn't there always a however?)For SQL Server 2008, the SQL Server team released a companion feature to filestream called remote blob storage (RBS). RBS consists of a set a stored procedures and an SDK that allows ...(truncated)...

Filestreams, transactions and SQL Server 2008 R2

(Indexed 2010-02-04):

I was under the impression that the database engine/programming model changes in SQL Server 2008 R2 were minimal and could be counted on one hand. Today I was running an old demo that I had, having to do with filestreams on the R2 Nov CTP. Usually I run this one line-by-line, but I was in a hurry and ran the whole thing. Funny...I didn't remember this few errors caused by limitations when using filestream. Hmmm... In SQL Server 2008 R2, filestream storage now support snapshots transaction is...(truncated)...

Inside the StreamInsight Object Model - Metadata

(Indexed 2010-01-19):

The StreamInsight object model seems fairly complex at first. There's a series of choices as you progress down the development path which make things appear more complex than they are. One thing to keep in mind is, no matter how you populate it, there is only one object model that encapsulates the metadata.One way to get a handle of the model is to look into the metadata. The easiest way to do this is to use SQL Server CE to register your metadata. If you don't specify SQL Server CE at r...(truncated)...

DACPAC and pre-SQL Server 2008 R2 versions

(Indexed 2010-01-12):

I thought it was curious that in a DACPAC you can specify required version and edition of SQL Server as a deployment option. But DAC (Data-Tier Applications) is a new feature of SQL Server 2008 R2 and VS2010 data tools. So what versions and editions does it support? (or will it support?). The somewhat surprising answer came in today, as an answer to a forum question. Check this thread out... If I'm reading this correctly, and "No 1" means "No with a superscript of 1", the functionality may b...(truncated)...

Upgrading maps in BI Dev Studio projects

(Indexed 2010-01-12):

Folks that are on early betas of products (especially private betas) are not surprised when projects are not updatable between beta versions. These limits are usually listed in the release notes, sometimes you'll just stumble across them. I'm getting back into SQL Server 2008 R2 and related features in earnest (since my presentation in Portland in September) after a busy November and December and noticed early on that PowerPivot workbooks weren't updateable between CTP2 and CTP3. Lat...(truncated)...

SQL Azure: Adventures in connectivity

(Indexed 2010-01-06):

SQL Server Management Studio in 2008 R2 (and there's a standalone upgrade for SSMS) supports connecting to SQL Azure. And there were some tools on CodePlex that did too. And that's what I'd always used. But reading along in the latest docs I came across the following statement "Connecting to SQL Azure by using OLE DB is not supported". Later on, the docs talked about using SQLCMD (which is a SQL Server utility that uses OLE DB) and SSIS (which can use ADO.NET/ODBC, but uses OLE DB as...(truncated)...

The Azure cloud. It's live now.

(Indexed 2010-01-06):

Yesterday I signed up for my account to the official, live, RTM cloud. Of course it was all for the SQL Server, that is, SQL Server Azure. MSDN premium subscribers get a free 8-month trial, so I signed up for that and they transferred by CTP databases over (actually, my "server name" didn't change at all). See Roger Jennings' step-by-step walkthrough to make sure you get the MSDN plan if you're a subscriber.I'd be playing with Windows Azure and SQL Azure since CTP1. But its all r...(truncated)...

Two of the coolest demos in the last month

(Indexed 2009-11-23):

Everyone is still recovering from the November conference weeks (reminds me of sweeps week on TV). As a "SQL guy" the conferences in the last three weeks that would interest me include: SQLPASS-US, SQL Connections, TechEd Europe, PDC, and SQLBits. Why folks insist on holding all of them in the same three week period is beyond me. I've spent last week paving virtual machines with new CTP/beta versions of almost every software product I use. I'm sure there were some neat demos during "conf...(truncated)...

And the secret word for tonight is...

(Indexed 2009-11-23):

No, the secret word for tonight is not "mudshark", although it does begin with the letter M. It's modeling. SQL Server data can be modeled in a variety of ways and, after the last few weeks, there's even more.When I think of relational database modeling, I think of the IDEF1X language and visual modeling tools like ERWIN. Those database diagrams that took up two (and sometimes three) of my cubicle walls. Data defined using functional decomposition based on a series of normal form rules. ...(truncated)...

Guide to the Data Development Platform for .NET Developers whitepaper available

(Indexed 2009-11-18):

I've been busy for the last few weeks putting together a "manifesto" whitepaper about the .NET-based data access stacks and also the possibilities for .NET programmers in the SQL Server product itself. The whitepaper's direct link is here, although its also available via both the MSDN Data Developer Center and the SQL Server 2008 Application Development website. I don't see the arbitrary distinction between application developers who use databases and SQL developers that some do, so ...(truncated)...

My MSDN Column on Visualizing Spatial Data is available

(Indexed 2009-11-18):

My latest MSDN magazine column on Visualizing Spatial Data is out in the Novemeber issue. It covers three ways to visualize SQL Server 2008 spatial data:SQL Server 2008 R2 Reporting Services Map ControlESRI MapIt Version 1.0MapPoint Add-In for SQL Server 2008Interestingly because the lead times are rather long for the column, we've already seen new versions of some of these. SQL Server 2008 R2 November CTP was available last week with updates to the Map ControlESRI MapIt Version 1.1 shipped ...(truncated)...

TechEd Europe demos posted to website

(Indexed 2009-11-18):

I had a great time at TechEd Europe in Berlin last week. Spent a lot of time in the SQL Server booth, meeting, greeting, and answering questions. It was nice to see you'all again.Monday evening I had the occasion to be a part of the 20th anniversary of the Berlin Wall's falling. Wednesday night, I was able to attend a get-together of the PowerShell MVPs, who declared me an honorary PowerShell MVP for the occasion. My talks seemed to be particularly well received. And, as promised, the de...(truncated)...

SQL Server 2008 R2 CTP3 arrives (with MDS)

(Indexed 2009-11-09):

Last night (here in Europe) I started to receive notices about SQL Server 2008 R2 CTP3 being available for MSDN/Technet customers. Among the list of nice features (especially surrounding PowerPivot and SSRS see Teo's nice summary here) is the first release of SQL Server Master Data Services. I'm especially interested in the fuzzy matching and data cleansing features, but for a complete view reference the CTP3 BOL and the whitepapers on the SQL Server MDS site.

TechEd Europe Day 1

(Indexed 2009-11-09):

I've been remiss, October 2009 was the first month with no blog entries in a long while. I'm been heads down, working on some projects that will come to fruition soon. And there's also the freak "leaf recycle bin accident" that's left be on my back for most of last week. But this week...I'm traveling.I'm here in Berlin for TechEd Europe and the weather reminds me of home, overcast and drizzling. Heading off in a few minutes to seek the Berlin Messe via S-Bahn and U-Bahn. ...(truncated)...

Be careful with EMPTY/NULL values and spatial indexes

(Indexed 2009-09-22):

There are two slightly different ways of representing non-existing spatial data, the "empty geometry" concept and database NULL. You can read about the subtle differences between them here. If you're using a spatial index, either of these types have the ability to cause query perforamance problems. Let's take, as an example, a table with 10000 rows, all of which contain 'POINT EMPTY'. If we put a spatial index on those rows, we will have 10000 rows in our spatial index all with a...(truncated)...

See you in Berlin at TechEd 2009 Europe

(Indexed 2009-09-16):

I've found out that a few of my talks have been accepted for TechEd 2009 Europe, which will take place this year in Berlin, Germany, on Nov 9-13. I'll be doing some SQL Server performance-related talks. If you're in Berlin, stop by and say hi, it will be nice to see everybody again.

A "What's New" at the Portland SQL Server User Group

(Indexed 2009-09-10):

I'm speaking at the Portland SQL Server User Group meeting this month about What's New in the SQL Server World. I'll be talking about upcoming new features in SQL Server 2008 R2 and upcoming features and products (like SQL Server Azure and Entity Framework 4.0) that could affect the way that you work with SQL Server in the future. It's an overview out of necessity, but I'll do as many concrete examples and demos as time allows (demand-based), as well as try and leave time for...(truncated)...

Temporary table lifetime and SQLCLR stored procedures

(Indexed 2009-09-01):

People are sometimes concerned with the lifetime of temporary tables they create in CLR code. As an example, if I use a parameterized statement in .NET code, the SqlClient provider wraps the statement in exec sp_executesql. If the statement batch includes something like "CREATE TABLE #temp1" as part of the batch, the table #temp1 is no longer visible when the batch returns. This is because the lifetime of a temporary table created inside of a stored procedure is the stored procedure itself, ie. ...(truncated)...

How's about a map on your SSRS report?

(Indexed 2009-08-21):

When I downloaded SQL Server 2008 R2 CTP2 last week, I went right by (for now) the cool Gemini features and even Multi Server Management and went straight for the new SSRS Map Control. After explaining the SQL Server 2008 spatial data types, the spatial methods, and SRIDs to folks until they glazed over, and then watch their eyes light up when I showed 'em the Spatial Results tab in SSMS, I grokked this "power of visualization" thing right away. Maybe not the most earthshattering feature in...(truncated)...

Microsoft Complex Event Processing platform is called StreamInsight

(Indexed 2009-08-21):

And a CTP is available now.I first heard of the .NET Complex Event Processing engine at this year's TechEd. Until lately, all we had to go on was Torsten Grabs' TechEd presentation and a whitepaper that gave a fairly high-level overview of its purpose and architecture. And, oh, it was to be released as part of SQL Server 2008 R2. So last week when SQL Server 2008 R2 CTP3 was released, there was also an announcement that a CTP of StreamInsight was available. The CEP engine has a name. And...(truncated)...

Upcoming improvements in EDM T-SQL code generation

(Indexed 2009-08-07):

I'm always thought that what makes or breaks any ORM layer for use in any but the simplest of applications is the underlying SQL code that's generated. In fact, I'm a proponent of using stored procedures with any ORM for best performance. So it was with great interest that I came across the blog posting "Improvements to the Generated SQL in .NET 4.0 Beta1" by the folks that are working on the Entity Framework SQL Server provider at Microsoft. These improvements, along with some other...(truncated)...

Make KML documents from SQL Server's geography data type

(Indexed 2009-08-07):

Today I posted the code to a projcect I've been working on, a library that creates KML documents from SQL Server 2008 geography instances or queries. It's called SQLServerToKML and is available on CodePlex here. There's no releases as such yet, just the library and a rudimentary document showing how to us it.Hope it's useful, post comments/suggestions to the project's discussion page.

MapPoint Add-In For SQL Server Spatial shipped

(Indexed 2009-08-03):

Last week, Microsoft released the MapPoint 2009 Add-In for SQL Server 2008 Spatial. You can get it here. This add in allows you to add layers to the map from queries against SQL Server spatial geography columns. It's more of an entry level mapping program that an IT-level (like the ESRI offerings) mapping program. You can save the map and use it against without SQL Server having to be available. There's also a number of "utility" type functions including shapefile import and queries and ...(truncated)...

At the user group...auditing and sys.fn_get_audit_file

(Indexed 2009-07-24):

I really enjoyed speaking at the Portland SQL Server User Group meeting last night about SQL Server security...and I have an update.We were talking about the supposed inability of auditing to audit usage of sys.fn_get_audit_file, the system function that reads an audit log. Raul Garcia of the SQL Server team had the answer. "For the particular scenario in this bug (sys.fn_get_audit_file), the permission being exercised is SELECT, not EXECUTE, hence the apparent failure to audit usage."An databas...(truncated)...

Spatial Indexes and ANSI JOIN - ON syntax

(Indexed 2009-07-20):

During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN - ON syntax in spatial queries. I'd coded:SELECT * FROM geonames.dbo.geonames gJOIN Sample_USA.dbo.Counties c ON g.GEOG.STIntersects(c.geog) = 1WHERE c.ID = 1569 Later I learned that the question was prompted by the following verbiage from the SQL Server Books Online: "To be supported by a spatial index, these methods must be used within the WHERE clause of a query, and the...(truncated)...

ESRI User Conference and MapIt

(Indexed 2009-07-19):

Last week I attended my first-ever ESRI conference in San Diego. Although the "star of the show" was version 9.3.1 and the upcoming version 9.4 of the ARCGIS series of products, I most enjoyed the presentations about the new ESRI MapIt product. This product is a collection of tools and toolkits to allow import, preparation, and usage of spatial data in SQL Server 2008.The tools include:A data import and encoding tool, Spatial Data Assistant, that enables you to import data from .shp files and AR...(truncated)...

At the Portland SQL Server User Group this month

(Indexed 2009-07-08):

This month I'll be presenting a session for the Portland SQL Server User Group. I'll be discussing and demonstrating the new security features in SQL Server 2008 with a post-talk Q&A about SQL Server security in general. I've also got some swag to raffle off. See you on the fourth Thursday!

At the ESRI user conference next week

(Indexed 2009-07-08):

Just got a mail message from an old friend who asked why I'd dropped out of sight in the past month of so. I've been in "extended partial vacation mode" for the last month or so and haven't blogged in a while. But I'm "still alive and well" (Johnny Winter was at the Portland blues festival last week, couldn't help the reference). Next week I'll be at the ESRI user conference catching up on the latest and greatest in the GIS world. On Tuesday, I'll be doing a fairly sh...(truncated)...

Book review - Beginning Spatial with SQL Server 2008

(Indexed 2009-05-30):

About a month ago, I received a copy of "Beginning Spatial with SQL Server 2008" by Alastair Aitchison. I've become acquainted with Alastair through his frequent postings on the SQL Server Spatial forum (under the login Tanoshimi), where he's always been very helpful and patient with folks starting out with spatial data concepts and SQL Server practice. Although I'd been working with SQL Server spatial for a while now, I read the book from cover to cover. It doesn't disappoint. T...(truncated)...

L2S and EF parameterization problem fixed in .NET 4.0 - almost

(Indexed 2009-05-25):

One of the first things that I did after installing VS2010 beta this week was to check on a parameterization problem with string parameters, and Linq To SQL/ADO.NET Entity Framework generated code. In the original version, this L2S query: var x = from a in ents.authors where a.au_lname == "Smith" select new { a.au_lname, a.au_fname }; or this EF query: string name = "Smith";var x = from a in ents.authors where a.au_lname == name select new { a.au_lname, a.au_fname }; ...(truncated)...

Talking about spatial data on RunAs Radio

(Indexed 2009-05-01):

My interview with the folks at RunAs Radio is out. I'm discussing one of my favorite topics, spatial data and spatial in SQL Server 2008, with Richard and Greg. This wandered off into some interesting possible usages for this data. Catch it here.

Article about programming filestreams on MSDN

(Indexed 2009-05-01):

My latest article on out. It's in the May issue of MSDN magazine, and can be found here. It this article I explore the internals of programming with the filestream feature of SQL Server 2008 and some best practices around when and how to use it. Hope you like it.

On SQL Server and .NET 4.0

(Indexed 2009-05-01):

I finally got around to install .NET 4.0 CTP today on a SQL Server box to test a long-held theory. Didn't quite work out the way I'd thought. When .NET support was introduced in SQL Server 2005, there was a lot of interest in how SQL Server and .NET Framework would approach the versioning story. The story was that SQL Server would always load "the latest version of the .NET runtime installed on the machine". SQL Server 2005 shipped with .NET 2.0.50727.42 and since then, there's been ...(truncated)...

How to ensure your spatial index is being used

(Indexed 2009-04-25):

I've answered quite a few questions lately about how to make sure that your spatial index is being used. Use of a spatial index with a large or complex spatial dataset can mean the difference between a query that takes minutes to execute and subsecond execution. You also want to use an index hint as a last resort, the spatial index should not need a hint. Here's a few things to try, in order of importance.1. Apply SQL Server 2008 SP1!!! I can't stress this one too much. There was a c...(truncated)...

Spatial Indexes in SP1, almost no hints required

(Indexed 2009-03-07):

On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren't uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort to index hints for spatial indexes at the slightest provication. Here's some representative examples: -- find surrounding zipcodes-- no indexes used, 5 secs-- cost pr...(truncated)...

SQLConnections: I really AM doing a day of spatial data

(Indexed 2009-02-16):

I was browsing through MSDN magazine (the paper version) today and noticed an ad for the upcoming SQLConnections conference in Orlando. I'm doing three sessions there as well as a day-long pre-conference talk "All about SQL Server 2008 Spatial Data and writing Location-aware Applications". I'm REALLY looking forward to this one and have some interesting and novel demos and information. Almost everyone is really using spatial apps, but store the info in its "alternate" form; that is addre...(truncated)...

Watch the SqlNotificationInfo for query notifications

(Indexed 2009-02-16):

I've been back working with SQL Server Query Notifications again lately. This blog post stems from a problem where the query notification "appears to register correctly" but does not fire. Or registers but "fires immediately". In this case "register correctly" meant fires a QN:Subscription event to SQL Profiler. The firing of a QN:Subscription event to SQL Profiler means that you did attempt to register a query notification. The query notification can either register correctly or return an a...(truncated)...

My First MSDN column is live today

(Indexed 2009-01-28):

I've become a columnist for MSDN magazine and my first column is available in the online edition, Feb 2009 issue today. I'm doing a column every few months called "Under The Table", about database-centric development. The first column is about one of the most exciting (IMHO) new features in SQL Server 2008, spatial data type and indexes, and the spatial library that's part of Microsoft.SqlServer.Types. Hope you like it.

Added categories to a lot of old blog entries

(Indexed 2009-01-20):

If you seem to be getting tons of my old blog entries, it's because I've gone back through all of them and added categories. All the way to 2004. This doesn't produce updates/duplicates for my blog reader, but it may produce them for yours. Available categories are listed for easy search.I still get questions about some of this stuff (like SQLCLR error 6522 and Query Notifications) and thought it might make it more easily referenceable. I've gone through and deleted most of the "...(truncated)...

SQL Server Compliance Portal is now live

(Indexed 2009-01-14):

Today I came across the new SQL Server 2008 Compliance Portal. This portal has information and links to the new Compliance whitepaper and compliance scripts (the "sample files" at the bottom of the main page on the compliance portal). New features for ensuring compliance in SQL Server 2008 include Policy-Based Management, Auditing, and TDE, to name just some of the ones that come to mind.This information fits in well with some of the talks I'll be doing at SQLConnections in Orlando in March....(truncated)...

Spatial Methods: What's with the 'ST'?

(Indexed 2009-01-09):

When SQL Server's spatial data types were introduced, people asked "Why does every standard method begin with ST" (e.g. STIsEmpty). SQL Server docs always cite the OGC (Open Geospatial Consortium) specifications, but OGC's methods do not begin with ST. Where did it come from?As far as I can figure the "ST" before every standard method comes from the SQL Multimedia (SQL/MM): Part 3 Spatial spec. But SQL/MM prefixes standard methods with "ST_" not "ST". ESRI and IBM's DB2 spatial exten...(truncated)...

SQL Server Spatial: EMPTY vs. NULL

(Indexed 2009-01-09):

Yesterday a friend asked me if it was better to always use a value of GEOMETRYCOLLECTION EMPTY as an alternative to making a geometry/geography column nullable. OGC has its own concept of database NULL, that is [GEOMETRY] EMPTY, where [GEOMETRY] can be any valid geometry subtype (that is 'POINT EMPTY', 'LINESTRING EMPTY', etc). When I asked why it mattered, he said he was tired of putting ..WHERE...IS NOT NULL in every UPDATE of SQL Server spatial data type. Sounds like reason fo...(truncated)...

Spatial Index Diagnostic Procs - The Rest of the Story

(Indexed 2008-12-18):

I've devoted the last few blog entries to describing what's in that spatial index analysis proc output and how to use it and interpret the results. Just wanted to describe the rest of the information and how the information relates to what you'd find in a query plan that uses a spatial index.The procs return:1. Information about the index2. Information about the query sample3. How efficient the index is when used against the query sampleThe information you get about the index is: - ...(truncated)...

Spatial Index Diagnostic Procs - How to specify query sample

(Indexed 2008-12-17):

A little more about the query sample that gets fed into the spatial index procs. Query sample is a singleton geometry or geography. It's not a query. So it's not as straightforward as "here's a spatial query that could use an index, run this query and show me the figures". So this procedure is not going to work any differently for a query that use STIntersects vs STDistance vs... It's going to show you how an index on table A would be utilized given a single operation.It maps mos...(truncated)...

Spatial Index Diagnostic Procs - Filter Output

(Indexed 2008-12-17):

Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the tableO = Number of rows outputP = Number of rows selected by primary filter (by the index)S = Number of rows selected by internal filter (by the index optimizations) Then, P-S = Number_Of_Times_Secondary_Filter_Is_Called (number of times they ran the expensive operation)(N-P)/N = Percentage_Of_Rows_NotSelected_...(truncated)...

Spatial Index Diagnostic Procs - Filters

(Indexed 2008-12-17):

The spatial index diagnostic procs' most enlightening pieces of information have to do with the filter counts and efficiencies reported at the end of the set of columns or XML document output. But what are these filters exactly and what do the results mean?The proc output refers to three filters:1. Primary Filter2. Internal Filter3. Secondary FilterRemember that the spatial indexes are based on tessellation or tiling. Issac has some nice diagrams that show how the tiling works in theory. Ple...(truncated)...

Spatial Index Diagnostic Procs - Intro

(Indexed 2008-12-17):

I've been looking at the spatial index stored procedures (sp_help_spatial_geography_index_xml and friends) a little harder recently, in an attempt to help answer two questions.1. If I have a spatial query, why does/doesn't it use my spatial index?2. If I have a specific spatial query in mind, what are the best values to choose for my spatial index density?In addition to the diagnostic information presented in the procs, there are a few things to keep in mind when attempting to answer the...(truncated)...

Is LINQ the next OLE DB? "LINQ-ed" Server as a rowset source?

(Indexed 2008-12-10):

Sorry to appear after a blog drought with theory meta-type blog entries. Too much time at conferences pondering technologies, I guess. The "relational database bigots" I hang out with don't like LINQ at all. They hope it would shrivel up in a corner and become part of the fad-technology graveyard. Or they're waiting to make big bucks fixing the performance problems they think will ensue.For the life of me I can't figure out why. I think they're reacting to LINQ to SQL and Entity ...(truncated)...

What the Entity Framework has going for it

(Indexed 2008-12-10):

I've been following the ADO.NET Entity Framework since its inception. Never did buy the idea about it being "more than an ORM", thought it was just marketing hype. After all, if it looks like an ORM and smells like an ORM, then... But, the more I've been wallowing around in it and thinking about its position in the world in general, the more I'd thought about what could drive someone to the "more than an ORM" conclusion. Here's three things that come to mind. 1. It's built on...(truncated)...

Post Conference(s)

(Indexed 2008-11-23):

End of the conference season 2008. At least for me, my friends in Belgium are looking forward to their SQL Server Day, coming up soon. I was able to catch up with a lot of friends at TechEd EMEA ITForum, TechEd EMEA for Developers, and SQLPASS, including some folks I hadn't seen in years. Just wanted to reiterate how good it was to see you, how much fun it was to hang out. I heard about of a lot of exciting new upcoming technologies and was able to discuss them with the industry's expert...(truncated)...

Secrets, hints, and tips for SQL Server Extended Events

(Indexed 2008-11-07):

I've been doing talks and demos on SQL Server 2008 Extended Events for a while now, it's one of my favorite parts of the product. Per session waitstats, SQL stack, built-in system health session, what's not to like? Before my talk at TechEd EMEA for IT Professionals last week (which went really well), I discovered something that was very helpful in getting things to run more smoothly. Since the betas, I've always used multi-part names, not only for events, actions, and providers,...(truncated)...

Using the spatial index diagnostic stored procedures

(Indexed 2008-11-05):

I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune to run across these procedures.sp_help_spatial_geometry_index and sp_help_spatial_geography_index put out information as columnssp_help_spatial_geometry_index_xml and ...(truncated)...

Broker External Activator (and more) in latest SQL Server 2008 feature pack

(Indexed 2008-10-30):

I'm not usually one to post product announcements, but I had to point out that the Service Broker External Activator shipped with the latest (Oct 2008) version of SQL Server 2008 feature pack. It's available here.Doing a quick "visual diff" with the August 2008 feature pack, I also notice some new PowerShell extensions, SQLXML 4.0 SP1, and a number of items for Reporting Services (including, of course, Report Builder 2.0), Analysis Services (including ADOMD.NET and some interesting datam...(truncated)...

November Road Trip

(Indexed 2008-10-29):

It's almost November and I'm in the final phase of getting ready for a road trip. In the next three weeks I'll be speaking on SQL Server 2008 at:TechEd EMEA ITPro - talks on Extended Events and on T-SQL performance improvements TechEd EMEA Developers - talks on T-SQL perf, SQLCLR, Filestream programming, Sparse Columns, and SQL Server/PowerShellSQLPASS - a preconference day of all SQL Server 2008 developer features and a spotlight session on programming SQL Server spatial data typesI...(truncated)...

SQL Server 2008 and TokenAndPermUserStore

(Indexed 2008-09-19):

In the past year or so, a few installations had begun to experience problems withthe SQL Server 2005 security cache (aka TokenAndPermUserStore) growing too large overtime. Some manifestations are connection and query timeouts and queries that takea long time.The folks at PSS published the canonicalblog entry about this problem, including knowledge base articles and suggestionsfor SQL Server 2005, and also mention of the name of the SQL Server 2008 parametersto configure the size of this cache at...(truncated)...

SQLCLR interop between SQL Server versions and Visual Studio multi-targeting

(Indexed 2008-09-18):

A friend of mine was asking about the affect of multi-targeting in Visual Studio 2008on SQLCLR. For an explanation of how multi-targeting works, reference DavidKean's blog entry on Visual Studio 2008 multi-targeting and FXCop. I happened to have an instance of SQL Server 2005 RTM (which uses .NET framework version2.0.50727.42) on hand to try. I took two assemblies as a test. Just for fun, one assemblycontained a user-defined function that works on SQL Server 2005, but not on SQL Server2008 (refe...(truncated)...

SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008

(Indexed 2008-09-17):

Although being able to use a GUI tool like SQL Server Management Studio is a nicefeature by itself, it's unusual that fixing something in a GUI makes a feature (thathasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters.You can't have defaults on parameters in .NET, but you can specify them in the CREATEPROCEDURE DDL, for example:CREATE PROCEDURE addwithdefaults (@x int, @z int out, @y int = 5)AS EXTERNAL NAME sampleasm.StoredProcedures.AddWithDefaultsThese work j...(truncated)...

Speaking on Extended Events at SQL Server User Group

(Indexed 2008-09-17):

Extended Events is one of my favorite SQL Server 2008 features and I'll be speakingabout how to use them for problem diagnosis, at the Portland (Oregon) SQL ServerUser's Group's September meeting. Check out http://www.pdxvbug.com/pdxuser.asp fordetails.Meeting is 6:30 on Thursday Sept 25. See you there.[Image]This blog is sponsored by SQLSkills.

On hinting spatial indexes and query complexity

(Indexed 2008-09-06):

Afterreading Isaac's recent blogposting about spatial queries and index hinting,I thought I might add some information based on a query I was working with this week.Sometimes if you have a query that's too syntactically complex, hinting won't work.In these cases you can cause the index to be used (or at least hintable) by breakingup the query. Warning.Protracted code example follows. Ifthe spatial query is somewhat complex, it's useful to write it out step by step, sowe start with the query writ...(truncated)...

Visual Studio 2008 SP1 and SQL Server 2008 SQLCLR features

(Indexed 2008-08-31):

When SQL Server 2008 shipped, Visual Studio 2008 SP1 and .NET 3.5 SP1 shipped a fewdays later. Visual Studio SP1 contains some neat enhancements that allow you to useSQL Server 2008 databases in Server Explorer and the related designers. There'ssupport for SQL Server change tracking in the Sync Services designer. The EDMand LINQ to SQL designers know about DateTimeOffset (datetimeoffset) and TimeSpan(time) in SQL Server 2008. Server Explorer also knows about geometry, geography,and hierarchyid. ...(truncated)...

A GUI visualizer/editor for SQL Server 2008 Extended Events

(Indexed 2008-08-31):

One of my favorite new features of SQL Server 2008 is extended events. I've writtena bunch of blog entries on 'em (use the search, type in Extended Events). So a fewdays ago, I recieved an email from Jonathan Kehayias directing me to his new programon Codeplex, the SQL2008 Extended Events Manager, asking for my opinion and suggestions.Well, my opinion is "I like it a lot". And one of my first suggestions was a starterhelp file, because those of us who are sometimes "GUI challanged" might miss fe...(truncated)...

Semantic (possibly breaking) change in SQLCLR TVFs

(Indexed 2008-08-21):

I came across the following interesting behavior while testing a SQLCLR table-valuedfunction that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server2008. It appears to be by design, because the error message (in 2008) clearly indicateswhat's wrong. But the code worked in SQL Server 2005 and if you depend on this codebehavior, it's a breaking change. And I haven't seen this in a readme file or BOL"What's New" section.First, some background. .NET procedures are allowed to do any...(truncated)...

A helper function for ring-orientation in the SQL Server 2008 geography data type

(Indexed 2008-08-21):

Folks have always had trouble with the fact that ring orientation is required with spatialinstances if you're using SQL Server 2008's geography data type, but not withthe geometry data type. For an explanation of the need for this, reference Isaac Kunen's blogentry here and Ed Katibah's blog entry (link in next paragraph).In Ed's blog entry, he provides a neat way to fix spatial instances thathave the wrong ring orientation for geography by using the geometry type andcalling a method that forces...(truncated)...

Demos for Portland SQL Server user group talk on SQL Server spatial

(Indexed 2008-07-28):

For attendees of last Thursday's talk on SQL Server Spatial for the masses, the democode is located here.Thanks for coming and for your participation![Image]This blog is sponsored by SQLSkills.

How do you shutdown a running SQLCLR appdomain?

(Indexed 2008-07-19):

When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as notedin the previousblog entry), the appdomain normally stays in place for the lifetime of SQL Server.This is done to save appdomain create/teardown and assembly load time. Note that DDLappdomains, as opposed to runtime appdomains, are torn down immediately after theyare used. A friend of mine recently wanted to shutdown an appdomain on purposeto troubleshoot a problem that he thought might have been SQLCLR-related. S...(truncated)...

DDL appdomains appear in SQL Server log in SQL Server 2008

(Indexed 2008-07-18):

.NET (and therefore SQLCLR) divides up running its code (even within the same processlike the sqlserver.exe process) into appdomains. The appdomain is like a lightweightprocess used to enforce isolation between running .NET code within the same Windowsprocess. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate executionof .NET code on a per database and per assembly owner basis. SQLCLR uses appdomains for two reasons: for running .NET user code like functionsand procedures, and ...(truncated)...

I'm speaking at Portland SQL Server Users Group next Thursday

(Indexed 2008-07-17):

I've been able to coordinate being home and the Portland (Oregon) SQL Server usergroup's meeting schedule, so next week I'll be speaking "at home" for a change. I'mgiving a talk on one of my favorite features for developers in SQL Server 2008, SpatialData support. Because it's relatively common these days to see GPS not only in carsbut also in "carry along" devices, cell phones, cameras, and other hi-tech toys Idon't personally possess, I called this talk "SQL Server spatial data for the masses"...(truncated)...

Programming SQLRegistration in the SQL Server PowerShell Provider

(Indexed 2008-07-08):

I've noticed that some folks have written PowerShell scripts that execute againsta list of servers. In the scripts, they read the names of the servers from XML files.But the SQL Server PowerShell provider in RC0 has a useful "component" calledSQLRegistration; its "path" is SQLSERVER:SQLRegistration that they can use instead. This path permits enumeration and manipulation of the (SQL) servers andserver groups that are defined using SQL Server Management Studio. The SQLRegistrationpath is not spec...(truncated)...

Using PowerShell to program SQL Server's Policies and Data Collection

(Indexed 2008-07-08):

I received a question today about whether I'd converted my Policy-Based Managementexamples using SMO (see the multi-part "Programming Policy-Based Management withSMO" series, starting here)from C# to PowerShell yet. I did do this a while ago; they're available as a scriptdownload on the SQLskills website (look on the "Past Conferences" page under TechEd2008). But...Since then RC0 has changed PBM a bit. The multipart name policy can't use ExecutionMode.Enforceany more, so I changed it to Executio...(truncated)...

Changes to the SQL Server PowerShell provider in RC0

(Indexed 2008-06-09):

Last week at TechEd Developers, I gave a talk on PowerShell and SQL Server. I mentionedsome upcoming changes in RC0, and have just had a chance to check them out. The PowerShell provider for SQL Server has been expanded to handle not only a "SQL"subdirectory (which enumerates database objects) and "SQLPolicy" (which enumeratesthe policy-based management objects) but also two new "directories": SQLRegistrationand DataCollection.SQLRegistration covers the groups and members of "Registered Servers"...(truncated)...

Don't just read the Readme, read the Release Notes too

(Indexed 2008-06-09):

SQL Server 2008 RC0 comes with a Readme file and a Release Notes file. It's alwaysgood to read both. In this RC, the readme file contains information on installationand upgrade, and the Release Notes file contains information about things that havechanged from previous releases.In RC0, they've changed the syntax/format of a few relevent new features. The changesaffect T-SQL MERGE, the HierarchyID data type's methods, and the Geometry data type'sLatitude/Longitude order. These won't be doc'd in B...(truncated)...

Performance features in SQL Server 2008 RC0 - Hashes for queries and query plans

(Indexed 2008-06-09):

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDNand Technet.Another performance-related feature (actually its additional information) allows youto have better visibility into performance-affecting queries. This information isavailable as columns query_hash and query_plan_hash in the sys.dm_exec_query_statsand sys.dm_exec_requests DMV. There's a really nice illustrative example in BooksOnline, so I won't repeat it here. You can use the queries in my last blog pos...(truncated)...

Performance features in SQL Server 2008 RC0 - Optimize for Adhoc Workloads

(Indexed 2008-06-09):

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDNand Technet.There's a couple of features that show up in RC0 that are performance related. Oneis a server setting 'optimize for ad hoc workloads'. This one tells SQL Server tosave a "compiled plan stub" in the query cache for adhoc queries, rather than thequery plan that's usually saved. You can see these stubs as cacheobjtype 'Compiled Plan Stub' in sys.dm_exec_cached_plans.And you can see them because trying to r...(truncated)...

What's the DataSet doing in a trace of an Entity Framework program?

(Indexed 2008-05-30):

In a blogposting about a few weeks ago, I'd written about noticing a DataSet being createdin an Entity Framework program by using the ADO.NET client trace facility. EntityFramework programs do (indirectly) cause a DataSet to be created, but its only forresolving the ProviderInvariantName of the underlying data provider. NOT for anythingrelated to the functioning of the EntityClient or ObjectContext in any way. I've revisedthe original blog posting to remove the DataSet reference, but just in cas...(truncated)...

Heading Out To TechEd

(Indexed 2008-05-30):

I'm getting ready to head out to TechEd in Orlando. I'll be doing a full day of SQLServer 2008 for Developers on Monday, a talk on SQL Server client and server-sidetracing (including SQL Server 2008 Extended Events), one on PowerShell and SQL Server,and finishing up with a talk on SQL Server Security for Developers and Architects.I'll also be hanging around the SQL Server Technical Learning Center (on the showfloor)at least part of the time I'm there.If you're around, stop by and say hi...[Image...(truncated)...

Saving a roundtrip when inserting rows with filstream columns

(Indexed 2008-05-30):

It's a good idea when talking to a database to save on network roundtrips. The table-valuedparameter in SQL Server 2008 is an example of a feature that can reduce them in the"1 order, 1-n detail items" use case.So its always been mildly irritating that in order to insert a row with a filestreamcolumn, you'd need to make 2 database roundtrips. One roundtrip is to execute theINSERT statement, inserting an empty value in the filestream column. This causes thefile to be created, a NULL value won't c...(truncated)...

SQL Server 2008 Change Tracking and Sync Services ARE made for each other... in VS2008 SP1

(Indexed 2008-05-16):

This item was mentioned in other blogs too, but just to mention it again (so my SQLCEand Sync Services friends won't think I overlooked it)...Visual Studio 2008 SP1 Beta contains direct support for using SQL Server 2008 ChangeTracking. When you use ADO.NET 1.0 Sync Services with a SQL Server 2008 database,the Sync Services designer (that's Add/New Item/Local Database Data Cache) adds acheckbox that allows you simply to "Use SQL Server Change Tracking". No extra triggers,no tombstone tables, chan...(truncated)...

SQL Server 2008 data types in LINQ to SQL and EF, they (mostly) did it

(Indexed 2008-05-16):

Last fall I'd asked the folks on the data access team about the possibility of includingthe new SQL Server 2008 data types in the new data access object mapping layers. Atthat time they said "no" but they also asked "why do you want it?" and "are peopleexpecting it?". Someone must have had some compelling arguments.I'm sure you've read this in other blogs, but in Visual Studio 2008 SP1 Beta, whicharrived Monday, there was...drumroll please...support for all four date and time typesin not only LI...(truncated)...

First blog entry in a month...what's up

(Indexed 2008-05-16):

After posting the last blog entry, I realized that I hadn't blogged in about a month.Hmmmm...Well, since last month I posted the demos for my SQLConnections talkson the SQLskills website (under PastConferences). I really had a good time at SQLConnections,its been a while since I'd been invited to speak there. Thanks to those folks whoshowed up and said hi.I've been updating my classes for SQL Server 2008 while waiting for the next CTP.Don't know when that's coming, but everyone's guess is "somet...(truncated)...

Trace Events in ADO.NET Entity Framework

(Indexed 2008-05-16):

Every once in a while I'll get inquires on a paper I wrote in 2004 about the ETW traceproviders for ADO.NET (named "Tracing Data Access"). I got an inquiry today, and sinceI'd recently installed Visual Studio 2008 SP1 Beta, I thought I'd try it out on aLINQ to SQL program and an Entity Framework program. If you haven't read the paper, the originalis still available on MSDN, but anembellished version (including SNAC tracing in addition to ADO.NET) was releasedin 2006. I'm also doing a talk at Tec...(truncated)...

Heading off to SQLConnections

(Indexed 2008-04-17):

I'm heading off to SQLConnections inOrlando early tomorrow morning. I'll see starting with a preconference "Day of SQLServer 2008 for Developers" and covering, well, every new feature that could interest adeveloper. I'll also being doing a series of talks about everything from Spatial datasupport to Extended Events to PowerShell in SQL Server to XML for DBAs. If you'll be in Orlando for the conference, stop by and say hi.[Image]This blog is sponsored by SQLSkills.

Converting an EAV design to sparse columns and populating

(Indexed 2008-04-08):

One of the uses for sparse columns will likely be replacing entity-attribute-valuedesigns. In an EAV design, the "sparse attribute" values are represented by a separatetable. The attribute value can either be declared as (n)varchar or sql_variant. Anexample I've always used is the hardware store that wants to keep track of its productsin a "products" table, but each product has different attributes. If you use the minimumnumber of columns to simplify the example, the EAV design looks like this.c...(truncated)...

Getting an activity ID with Extended Events

(Indexed 2008-04-07):

It's easier than you'd think... When you use the SQL Server 2008 CREATE EVENT SESSIONDDL statement with extended events, you specify:Events to be captured (e.g. sqlserver.error_reported)Actions to be fired to add more information (e.g. sqlserver.sql_text)Predicates for conditional capture (e.g. where sqlserver.error_reported.error = 547)Event target to collect the events (e.g. package0.ring_buffer)Addtional options (e.g. MAX_MEMORY)In general, the way you look for events, actions, predicates, et...(truncated)...

SqlClient, System.Transactions, SQL Server 2008, and MARS

(Indexed 2008-04-05):

It appears that there are some changes in .NET 3.5 System.Transactions (or System.Transactions.dlland System.Data.dll version 2.0.50727.1433 if you're looking at assembly versions).Florin Lazar blogs about a change to the syntax here. AndAlazel Acheson blogs about changes to SqlClient changes to accomodate using lightweighttransactions with less promotion to distributed transactions here. It is interesting to read how the latest version of SqlClient can combine automatictransactions with connect...(truncated)...

Accessing multiple servers with the SQL Server 2008 PowerShell provider

(Indexed 2008-03-25):

Short post this evening...Just in case anyone else flails around looking for this feature, its right under yournose. When you use the PowerShell SQL Server 2008 provider, you have visibility toa single, local machine (and all its SQL Server instances you can access with integratedsecurity) by default. To get access to multiple machines using the provider, simply referencea SQL provider path that contains that machine name. If the (Windows) principalhas access to the other machine's SQL Server in...(truncated)...

More about sparse columns and column_sets

(Indexed 2008-03-20):

I'm still getting used to the new sparse column feature in SQL Server 2008. I'd just read in the BOL definition of ALTER TABLE that you can add a column_set toan existing table. I was converting a sample app from an EAV (entity attribute value)design to sparse columns. I used the existing "attribute-value" table to create thesparse columns, then created the table. I then went back to add the column_set withALTER TABLE after the fact.alter table sparsetest3 add spcolset xml column_set for all_spa...(truncated)...

Programming Policy-Based Management with SMO - Part 6 - Categories, Subscriptions, Wrapup

(Indexed 2008-03-18):

This is the last part of a series on programming policy-based management. The seriesstarts here.In the previous installment, I created a policy that was constrained to a single database.To accomplish this, I used a Condition that called out the database by name, and tiedit to the TargetSet using TargetSet's SetLevelCondition method.An alternative consists of creating a policy as part of a PolicyCategory. Each Policyis a member of exactly one PolicyCategory. The default PolicyCategory is the only...(truncated)...

Programming Policy-Based Management with SMO - Part 5 - TargetSets and TargetSetLevels

(Indexed 2008-03-17):

This post is part of a series on programming policy-based management. The series begins here.So, we were working with a policy that required an ObjectSet. ObjectSets contain TargetSets.For example, the ObjectSet for the naming policy (IMultipartName) we were workingon needs a TargetSet for Procedure, Synonym, Table, Function, Type, View, and XmlSchemaCollection. Note that this collection is similar to what you'd see for a MultipartName policyin the SSMS designer dialog. The title for it in SSMS ...(truncated)...

Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets

(Indexed 2008-03-17):

This is part of a series on programming policy-based management. The series starts here.Now, we'll tackle programming a little bit more complicated policy. The table-namingstandard that applies to a set of database objects. For this, we need three items:1. Condition for defining the policy itself.2. Policy that uses the condition and contains....3. Condition that specifies a set of database objects to which the policy should beapplied.The first condition is straightforwardCondition con = new Con...(truncated)...

Programming Policy-Based Management with SMO - Part 2 - ExpressionNode and Policy

(Indexed 2008-03-17):

This is part of a series on programming policy-based management. The series starts here.So, to initialize my Condition's ExpressionNode I need more than just a string. Itdoesn't look like, at this time, every ExpressionNode CAN initialized with a string.But we can use the subclasses. The ones I need here are Operator, Attribute, and Function.I need: DatabaseMailEnabled (Attribute), Equals (Operator) and false (Function). BTW,ExpressionNodeFunction appears to be what you are programming when you ...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 6

(Indexed 2008-03-06):

This is the last post in the series, at least for now. I'll update it (or post moreon the topics discussed here) as the products involved evolve and mature. This one'sabout:LINQ to SQL and EF queries will be "untunable" because, even if you discover a performanceproblem, you can't change the underlying API code to produce the exact SQL query thatyou want. There's too many layers of abstraction to change it.T-SQL is an imperative language, allowing you the ability to rewrite queries for betterper...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 5

(Indexed 2008-03-06):

This post is part of a series about worries when implementing LINQ to SQL or ADO.NETEntity Framework from a SQL database-centric programmer's perspective. The last twoworries are related. It's mostly about either level of abstraction being one levelaway from the "real SQL code" that's being executed.First off...LINQ to SQL and EF will discourage the use of stored procedures to return rowsets;returning rowsets in stored procedures is *thought to be* superior to returning themin dynamic SQL. In ad...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 4

(Indexed 2008-03-06):

This post covers LINQ to SQL and EF worry #4. That is:LINQ to SQL and EF will write code that gets too much or too little data at a time.Too much data in one query is a waste. Too little data is also bad because it meansextra database round-trips.I really put this in for completeness. Both LINQ to SQL and EF have good mechanismsto deal with this one. In addition, its not necessarily (only) an ORM problem. Ina file system graphic user interface, do you prefetch all of the (perhaps thousands)or fi...(truncated)...

Feature synergy in SQL Server 2008

(Indexed 2008-03-04):

SQL Server 2005 introduced some new features that left "traditional" SQL folks puzzled.What's the hidden use for SQLCLR? Or the XML data type? Or Service Broker? Besidesbeing available to you as a programmer, these are all used internally. I once had a conversation with a database person (not SQL Server) who, when askedabout the viability of certain features in his database product for the generaldatabase developer, responded that "although there are only a handful of users usingfeature X, we u...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 3

(Indexed 2008-03-01):

This post is part of a series, see parts 1 and 2.Sorry to be so long getting back to this series.This post covers LINQ to SQL and EF worry #3. That is:LINQ to SQL and EF will encourage "SELECT to middle-tier, then update/delete" ratherthan issuing SQL UPDATE/DELETE statements that are set-based.Neither LINQ to SQL or Entity Framework currently contains an insert/update/deletelanguage. Entity SQL could contain DML in the future, LINQ (Language IntergratedQUERY) to SQL doesn't have one. But both A...(truncated)...

Installing .NET 3.5 on a SQL Server 2005 machine...be careful

(Indexed 2008-03-01):

Recently I had the occasion to load .NET 3.5 on to a machine that had an existinginstance of SQL Server 2005. .NET 3.5 does not work by versioning the "main .NET assemblies"(e.g. there is no "version 3.5" of mscorlib.dll, System.dll) but by replacing the2.0 versions of them. You can observe this by inspecting the 4-part version number.For example System.dll (in windowsMicrosoft.NET, Framework2.0.50727 as well asin the GAC) changed from version 2.0.50727.42 to 2.0.50727.1433 when I installed .NET...(truncated)...

Ed's Blogging about SQL Server Spatial

(Indexed 2008-03-01):

It's good to see Ed Katibah open upa blog on SQL Server spatial data. Ed is sort of the "dad" of spatial data inSQL Server; he's got a ton of experience and history in this space. Be sure tocatch his posting on what's newin spatial for CTP6. I've been trying out the new functions and themore I use the spatial types and functions, the more I understand the reasoning behindthe dual Geometry - Geography types in SQL Server.Issac's back at blogging too about spatialindexes, really makes for good re...(truncated)...

Configuring Filestream in CTP6 (its different)

(Indexed 2008-02-28):

I've been working with Filestream storage in SQL Server 2008 since it appeared inCTP5. The way I've always set it up is to use sp_filestream_configure. During theCTP6 setup process, I noticed you could now configure Filestream as part of setup.Because I knew how to use sp_filestream_configure I skipped that part of setup. Andeverything just worked as expected when I used it. A friend of mine, not wantingto miss anything, configured Filestream as part of setup. When he tried to use it,the followi...(truncated)...

Lots of ORM and VS activity lately

(Indexed 2008-02-26):

Quite a bit of activity last week in the Visual Studio and ORM spaces.Visual Studio released a CTP of VisualStudio 2008 support for SQL Server 2008 CTP6. It allows you to connect to CTP6with Server Explorer, which enables quite a few other features to work. check withOverview document on the download page. SQLCLR projects and T-SQL and SQLCLR debuggingwork too.Curiously the Overview document says "using the LINQ to SQL Designer with SQL Server2008 databases" is specifically not supported. But I'...(truncated)...

LINQ and LINQ to XML to appear in SQL Server 2008 SQLCLR approved list

(Indexed 2008-02-15):

Sometime before SQL Server 2008 RTM, the libraries for LINQ and LINQ to XML (thatis System.Core.dll and System.Xml.Linq.dll) but NOT LINQ to SQL will be added to theSQLCLR "approved" assembly list. The approved assembly list is the list of .NET Frameworklibraries that have been tested in a SQL Server-hosted environment, annotated theircode with HostProtectionAttributes where needed, and can be used in user assembliesthat can be cataloged with "PERMISSION_SET = SAFE". That means you can use 'em i...(truncated)...

Learning LINQ, LINQ to SQL and eSQL

(Indexed 2008-02-15):

I often hear those who identify themselves as "database programmers" (sometimes Ithink these may be folks who program only inside-database code), say: "I'dgive learning LINQ and/or LINQ to SQL/Entities or Entity SQL (eSQL) a go in my sparsetime, but I don't have a good book or a tool.I haven't read a book on either of these yet, spending most of my time in BOL. BOLis a good start (but not comprehensive) for LINQ and LINQ to SQL. eSQL docs are prettysparse, but that's because its not a shipping p...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1

(Indexed 2008-02-14):

I'd been meaning to write this entry for a while, about my opinion on LINQ to SQLand the ADO.NET Entity Framework and performance. I've just finished reading the 2-partblog series "Exploring the Performance of the ADO.NET Entity Framework", and wassurprised (I guess) that the database performance aspect was barely mentioned. One way to look at performance is to examine and profile the ADO.NET code, but becauseboth EF and LINQ to SQL are T-SQL code generators (EF is not database-specific, butI'm ...(truncated)...

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 2

(Indexed 2008-02-14):

This post covers LINQ to SQL and EF worry #2. That is:LINQ to SQL and EF will encourage "SELECT * FROM..." style coding because you getback a nice, clean object this way instead of the less-useful anonymous type you receiveby doing projection. This will also make most/all covering indexes useless.LINQ to SQL and EF can return something other than a whole object instance. Here'san example:// This returns a collection of authorsvar query = from a in ctx.authors select a;// this return...(truncated)...

HTTP Endpoints to be deprecated in SQL Server 2008

(Indexed 2008-02-13):

SQL Server 2005 introduced the concept of endpoints. Every connection entry pointinto SQL Server is abstracted as a endpoint. You can see all the endpoints on yourSQL Server instance by using the sys.endpoints metadata view. In addition to thoseyou'd expect (for TCP/IP, Named Pipes, Shared Memory) there are also endpoints definedfor the dedicated admin connection, Service Broker, Database Mirroring...and HTTP.The endpoint concept is still with us, but HTTP endpoints, those endpoints that allowyo...(truncated)...

The Morning After...SQL Server 2008 Jumpstart

(Indexed 2008-02-13):

Wow. It's already Tuesday and I'm still recovering from last week. I've been layinglow for the last six months or so, writing a few (OK, quite a few) blog entrieson SQL Server 2008 topics. But I'd really been working on my next SQL Server course(on 2008, of course), that premiered last week as part of a program known as SQL Server2008 Jumpstart. Simon Sabin (the newest SQLskillsassociate) and I presented to a good-sized crowd of Microsofties and trainers fromeverywhere. We did the developer trac...(truncated)...

Using SQL Server 2008 spatial and the Virtual Earth map control - 2

(Indexed 2008-02-13):

This entry is a continuation of the previous posting on how to use SQL Server 2008spatial data and Virtual Earth. This entry discusses your web service and SQL Servercode choices.The point of the web service is to translate one of the output formats of SQL Server2008 spatial to a format this Virtual Earth can use. The spatial data types can beoutput in:1. Well-known text format2. "Native" format - that is, return a Geometry/Geography type to a .NET client asthe SqlGeometry/SqlGeography native .N...(truncated)...

Using SQL Server 2008 spatial and the Virtual Earth map control - 1

(Indexed 2008-02-13):

Last week at the SQL Server 2008 Jumpstart I showed an example of using SQL Server2008 spatial data types with Virtual Earth. I showed a single coding style. Therereally is a plethora of coding styles to using these together, and I'd like to describesome of the most common ones. I'd like to do this without much code for now, becausean end-to-end description sometimes results in the graphic aspects (which other controlsto use, how to build Ajax web pages, etc) seem to get in the way.I'm not even ...(truncated)...

Composable DML and Composable Queries

(Indexed 2008-01-21):

In a previousblog entry a while ago, I wrote about writing a single SQL statement that dida SQL MERGE operation, used the OUTPUT clause to put out a rowset and directed therowset into an INSERT statement. This happens in one statement without the need ofexplicitly defining a temporary table and using multiple SQL statements.Turns out that this feature has a name: Composable DML. I've also heard it called(in SQL Server Books Online) "DML table source". I've also heard the term composable queries,...(truncated)...

Service Broker in SQL Server 2008 - When sent message and receive message priority are different

(Indexed 2008-01-21):

More about Service Broker priority in SQL Server 2008.The books online states, when setting a initiator/target priority as local servicename/remote service name that the priority affects:1. Sends from the initiator queue2. Receives from the initiator queue3. Getting the next conversation group from the initiator queue.And specifies the mirror image priority for target/initiator as local service name/remoteservice name affects:1. Receives from the target queue2. Sends from the target service3. Ge...(truncated)...

Service Broker in SQL Server 2008 - Priority By Contract

(Indexed 2008-01-21):

Conversation priority is a new feature with SQL Server 2008. In a previousblog post, I talked about how to simply set one up. But you'd usually not wantto set up a priority for all messages. So, lets set up a simple service and then definea "Premier Customer" priority. Before trying this you need to make sure the databaseis set to honor broker priority.ALTER DATABASE pubs SET HONOR_BROKER_PRIORITY ON;Suppose I had a simple (one database) service pair. A service called "sender" (notelower case) u...(truncated)...

Another use for SQL Server 2008 row constructors

(Indexed 2007-12-28):

One last SQL syntax post for the evening...We've all heard about SQL Server 2008 row constructors. They allow syntax like thisto work:CREATE TABLE name_table (name varchar(20), age int);goINSERT INTO name_table VALUES ('Bob', 54), ('Mary', 30), ('Sam', 15), ('Buddy', 9);goBut how about using them as a table source:SELECT n.name, n.age, tab.speciesFROM name_table nJOIN ( VALUES ('Bob', 'person'), ('Mary', 'person'), ('Sam', 'cat'), ('Buddy', 'cat')) tab (name, species) ON n.name = tab.name;You ...(truncated)...

More hints available to plan guides in SQL Server 2008

(Indexed 2007-12-28):

To round out the new plan guide-related features in SQL Server 2008, there is a newway to express a table hint that increases the plan guide's reach.The sp_create_plan_guide procedure requires a hint as the last parameter. This canbe in a form OPTION (hint), just an XML query plan (in SQL Server 2008 "OPTION (USEPLAN)" isn't required), or NULL. Specifying NULL can be used to "subtract" a hintfrom an existing query where the hint is hardcoded in source code you don't have theability to change.Thi...(truncated)...

Using the OUTPUT clause results and INSERT-SELECT

(Indexed 2007-12-28):

SQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause,you can output column values to a table variable, a table, or return these valuesto the user. You can even use multiple OUTPUT clauses to push the values into botha table variable and a table from the same statement.In SQL Server 2008 there is an additional option. You can use your OUTPUT column valuesdirectly in an INSERT-SELECT statement. Here's what it would look like, using MERGEwith an OUTPUT clause (and ...(truncated)...

Plan freezing and other plan guide enhancements in SQL Server 2008

(Indexed 2007-12-27):

One of the nifty new SQL Server 2005 features was called "plan forcing". You couldacheive plan forcing by using the USE PLAN query hint but this made such a hideousquery that the better way to do this is to use a plan_guide. A plan_guide is a nameddatabase object (like table or view) that associates a query hint with a particularquery. In SQL Server 2008 there have been some enhancements made for plan_guides and planforcing. The biggest change is that you can create a plan from the plan cache di...(truncated)...

Mapping Insert/Update/Delete sprocs with Many-to-Many

(Indexed 2007-12-26):

After getting insert/update/delete stored procedures going with a simple standalonetable (no relationships), I started thinking about how I'd do this in a many-manyrelationship. Let's say that I have students and classes, with a many-many relationshiprepresented by a studentclass table with only a student_id and class_id. EDM mapsthis to a many-many relationship, leaving the "join table" out of the conceptual model(no studentclass entity). That's what I'd want. But...how to map insert/update/del...(truncated)...

Mapping Insert/Update/Delete sprocs with Entity Designer

(Indexed 2007-12-26):

I've been working with the new ADO.NET Entity Framework designer for a few weeks now,and I've got to like it. If you're used to the LINQ To SQL Object Relational designer,in which all the action takes place in the "diagram pane", it takes some getting usedto. The ADO.NET Entity Framework designer actually uses three panes, the Entity Designerpane, the Model Browser pane (which docs itself in the Solution Explorer group atthe right in my layout), and the Mapping Details pane (which docs itself in...(truncated)...

SQL Server 2008 and ADO.NET Sync Services

(Indexed 2007-12-20):

Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reasonfor SQL Server's new Change Tracking feature, and how it differs from Change DataCapture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I'dencourage you to start there. But about "why it's there...".The main reason for change tracking, as far as I can see, is to be used with ADO.NETSync Services. ADO.NET Sync Services is part of the Microsoft Sync Framework and isa way to do (I'm tryi...(truncated)...

Entity Framework Beta3 - Deleting without fetching

(Indexed 2007-12-09):

As a continuation of the previous discussion, here's a DELETE of a title row withoutfetching it from the database:pubsEntities model = new pubsEntities();titles deletetitle = new titles();deletetitle.title_id = "BU9994";deletetitle.EntityKey = new EntityKey("pubsEntities.titles", "title_id", "BU9994");model.Attach(deletetitle);model.DeleteObject(delet...(truncated)... this case, you need to populate both the property (title_id) that corresponds tothe primary key and also populate the EntityKey i...(truncated)...

Entity Framework Beta3 - In search of round-trip optimization

(Indexed 2007-12-09):

The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NETteam blog. One of the things that bothered me originally about EDM was that Entity SQL in V1doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update ordelete a row, I'd have to fetch it first. Let's start with something simple, likean INSERT with a foreign key constaint?All of the "add" examples I've seen always insert a new customer and new order andnew order details at the same time. ...(truncated)...

SQL Server 2008: Ordered SQLCLR table-valued functions

(Indexed 2007-11-21):

Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valuedfunction with an ORDER clause. If you have intimate knowledge that the functionalways returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clausein the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap andeasy Fibonacci sequence function from Dan Sullivan and my SQL Server 2005 Developer'sGuide. create assembly orderedtvf from 'C:tempOrderedTVF.dll'go-- no order clause...(truncated)...

Large user-defined types and aggregates in SQL Server 2008

(Indexed 2007-11-21):

One last SQLCLR feature I'd forgotton about but was quite highly publicized. Thisis extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You justthe MaxByteSize of -1 in the appropriate attribute, like this:[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)]The serialization format is going to have to be UserDefined, unless you have over8000 bytes of blittable types (that is, the .NET types that can use Format.Native);since the blittable types (doc'...(truncated)...

SQLCLR in SQL Server 2008: Multi-input user-defined aggregates

(Indexed 2007-11-20):

One thing that I didn't find in the BOL What's New page is some of the new SQLCLRfunctionality in SQL Server 2008. The first one that intrigued me is support of multi-inputuser-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate,an analytic function that returns the population covariance between two expressions.The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the samein SQL Server.All that I need to do this is to use the "template" for a .NET U...(truncated)...

What's new in SQL Server 2008 CTP5? Start by RTFR and RTFBOL

(Indexed 2007-11-20):

With any new product or CTP, I've always gotten fast results by starting with thereadme.txt file that comes with the product/CTP. Although its now called ReadmeSQL2008.htm,its still worth reading. This usually gives you answers about install scenarios andlast-minute changes.For example, this readme file answers two of the (so-far) FAQs about CTP5. What happened to Surface Area Configuration Utility? Can I install SQL Server 2008 CTP on Windows Server 2008? (ie, is it officiallysupported)You'll...(truncated)...

Service Broker Priorities in SQL Server 2008 CTP5 - An overview

(Indexed 2007-11-20):

SQL Server 2008 adds the concept of priority for conversations. It's setup using specialDDL statements, priority cannot be specified on the CREATE DIALOG CONVERSATION orSEND/RECEIVE DML statements. The DDL statements are CREATE/ALTER/DROP BROKER PRIORITY. To specify a priority, you associate a BROKER PRIORITY object with combinations ofthe qualifiers LOCAL_SERIVCE_NAME/REMOTE_SERVICE_NAME/CONTRACT and the priorityis associated with all messages and conversation endpoints that match that combinat...(truncated)...

SQL Server 2008 XML: Let there be 'let'

(Indexed 2007-11-20):

SQL Server 2008 adds support for the 'let' clause in FLWOR expressions. The for andlet clauses have a similar purpose, to bind content (tuples) to variables. Eitherone can begin a FLWOR expression:declare @x xml = '';select @x.query('for $i in (1,2,3)return $i');> returns 1 2 3declare @x xml = '';select @x.query('let $i := (1,2,3)return $i');> returns 1 2 3The distinction is that let is an assignment clause, in the simple statement using'let' above, $i refers to the entire sequence (1,2,3). The...(truncated)...

ITForum - Day 5 - Powershell and SQL Server 2008 - the reprise

(Indexed 2007-11-15):

The upcoming SQL Server 2008 provider for Powershell was a big hit and sparked quitea bit of discussion when I talked about and demonstrated it during TechEd/Developerslast week. At the time I thought that this might be a good discussion topic for databaseadministators, and, after asking around, a room was found, and I'll be re-presentingthis session, entitled "Using Windows Powershell with the SQL Server 2008 Providerand SMO" here at TechEd/ITForum tomorrow (Friday) at 13:30-14:45 in room 131.I...(truncated)...

ITForum - Days 2 and 3

(Indexed 2007-11-14):

I'm up early this morning for a repeat of my T-SQL in SQL Server 2008 talk. Thingsreally went well for the last two days, folks really seemed to like to see lots ofactual working code, albeit my examples are always "minimalist". There was a lot ofinterest around the Spatial data talk and some discussion of upcoming and ongoingprojects that will take advantage of this new functionality. The increasein interest could be been caused by announcements about spatial data at the conferenceand also the ...(truncated)...

ITForum - Day 1 in Barcelona

(Indexed 2007-11-12):

Today is day 1 of TechEd/ITForum in Barcelona. I have a bit less hectic of a workloadat this one, a total of 4 sessions, 3 of them being "interactive sessions" (this year'sword for chalktalk). Today's session is going to be on T-SQL enhancements in SQL Server2008, at the late-in-the-day time of 5:45-7:00pm. C'mon out and I'll see you there,or, if you can't make the late session, I'm repeating it on Wednesday.Tuesday, I'm doing my only breakout, the "day's worth of material in 75 minutes" onSQL q...(truncated)...

Astoria... I learned something today...

(Indexed 2007-11-10):

Actually yesterday, but today was the first time I'd had a chance to write about it. Having done quite a bit with what's now being called "traditional web services", myfirst impression of REST were, I'll have to admit, the thought that it was web serviceswithout schema. I kinda like my metadata; it's always been irritating that storedprocedures do not store ANY metadata on the number or shapes of the rowsets returned,only metadata on the parameters are stored. The closest that ANSI comes is allo...(truncated)...

TechEd Barcelona Wrapup...and on to TechEd/ITForum

(Indexed 2007-11-10):

Whew! I had a full week at TechEd Barcelona, just getting around to finishing up writingabout it. It was great to get out and see everyone, there were a lot of familiar faces.Thanks for showing up and saying hello. I fully enjoyed each and every presentationand interactive discussion. A special thank you to Gunther Beersaerts for allowing me to attend and present. Gotto meet a lot of "the old gang" as well as some people who I'd only known throughtheir blogs, like Ward Pond and JohannesKebeck. A...(truncated)...

Barcelona Day 4 - Transactions/Isolation and OLE DB v ODBC

(Indexed 2007-11-08):

Today, I'm finishing up my (many) talks at TechEd/Developers' Barcelona with a breakouton best practices for transactions and isolation and leading an interactive discussionon OLE DB and ODBC. I love talking about transactions and, in addition to demonstratingall of the isolation levels SQL Server supports their behavior and repercussions,I'll even show the fairly esoteric "MARS batch-scoped transaction".The "OLE DB and ODBC" discussion should be good. After seemingly moving away fromODBC since ...(truncated)...

Barcelona Day 3 - TSQL Query Plans and XML in SQL Server

(Indexed 2007-11-06):

Tomorrow (Wednesday) I'm changing to multiple best practices session in a single day.In the morning, I'll be speaking about T-SQL query plans and "why queries run slowly"in a session called "Best Practices for Procedural SQL Code". And in the afternoon,a different data model and query language (but the same optimizer) in "Best Practicesfor XML data type and XQuery". Be sure to drop in if you're interested in either ofthose topics.Today's surprise was a preview of the upcoming SQL Server 2008 Pow...(truncated)...

Barcelona Day 2 - Powershell/SMO and SQLCLR

(Indexed 2007-11-06):

Today is day of SQLCLR (that is, use of .NET CLR code in SQL Server) for me at TechEd/Developersin Barcelona. I'm doing two talks on it: one before and one after lunch. The beforelunch talk is a breakout-style talk, illustrating with code when its a good idea touse SQLCLR and when its not a good idea. I'll also touch on some SQL Server 2008 SQLCLRenhancements. Questions and comments at end of the talk.The "after lunch" talk is more of an open discussion. Some folks think that SQLCLRis one of the...(truncated)...

Mondial database for SQL Server 2008

(Indexed 2007-11-05):

I like free, standard, sample databases. One that I've been looking at for quite awhile is the Mondial database currently availableat Institute for Informatics Georg-August-Universitt Gttingen. This set of data(from an old CIA World factbook and other sources), is available not only as a relationaldatabase, but as XML, RDF, and even F-logic. The problem with using this with SQL Server has been the lack of a DATE datatype withthe appropriate value range. The DATE datatype is used as "Date of Inde...(truncated)...

SQL Server 2008: Inheriting From a System Data Type?

(Indexed 2007-11-05):

Ever since the hierarchyid data type was introduced in SQL Server 2008 CTP4, there'sbeen a fair amount of discussion about renaming some of the methods of the data typeto make them a bit more intuitive. In addition, there's been discussion about "missing"methods that don't exist in the hierarchyid data type. Well...Because hierarchyid is a .NET-based system UDT, you can implement these "missing"methods yourself. There's a couple of ways to go about this:1. Use your own assembly that uses the hie...(truncated)...

Wither CTP5?

(Indexed 2007-11-05):

There's been some rumors going 'round about the immenent release of SQL Server 2008CTP5. OK, maybe I've been asking around too. The main reason for the rumors is thatthere was a published CTP timetable at one point and its getting to be about thattime. The other reason is the plethora of sessions, both here in Barcelona at TechEd/Developersand TechEd/ITForum and at DevConnections inLas Vegas (where some of my SQLskills collegues are thisweek) about features that won't appear until CTP5. Looking ...(truncated)...

Welcome to Barcelona! TechEd and Best Practices

(Indexed 2007-11-05):

This week I'm at TechEd/Developers Barcelona. I arrived Saturday and I'm always delighted,being a Portland Oregon resident, to see the sun, even in winter. It's supposed tobe sunny and mid-upper 60s for the next 10 days and that's good because I'll be stayingon for TechEd/ITForum next week too. Oh boy, my sun quota for the rest of the year...I'm going to be doing a breakout or two and some "interactive sessions" (that's whatchalktalks evolved into) every day from Mon-Thurs. Talking about best pr...(truncated)...

Powershell and SQL Server's 2008 SMO - the reprise

(Indexed 2007-10-31):

In the last few years, I've done a few talks at various conferences on the integrationof SMO (SQL Server Management Objects) and Powershell. My friend and co-author ofthe SQL Server 2005 books, DanSullivan, got me into using Powershell and SMO and has written quite a numberof excellent blog entries and articles about it. Because SMO is just anotherloadable .NET library, they're a perfect fit. For TechEdUS, I even wrote a simple powershell provider that makes SQL Server look at afile system (Navi...(truncated)...

Sparse tables, sparse columns, and XML

(Indexed 2007-10-31):

I've been talking with folks for (it seems like) a long time about modeling sparseattributes in a relational database. Seems like I run into a new design where there'sa need for sparse attributes every few weeks. If you remember the "hardware store"example (where each new sales item may have unqiue properties), that's just thetip of the iceburg. Basically, your choices boil down to: Sparse Columns (new columnfor each new attribute), Sparse Tables (new table for each new set of related attributes...(truncated)...

Spatial Data, a niche or a tool for the masses?

(Indexed 2007-10-25):

I'll have to admit it, when I first saw that SQL Server 2008 was adding spatial datasupport, I thought of it as a niche. The province of geographers, cartographers, andmaybe a few others. Complex, involving a lot of higher mathematics, each provincehaving their own geographic encoding, and so on... And that level exists, to me it'sthe production of spatial reference data. When I think of spatial reference data,I think of map data you'd buy from ESRI and data posted by government agencies. Ormade...(truncated)...

Using the SQL Server 2008 UDTs on clients

(Indexed 2007-10-25):

A couple of people have been asking and, in preparing for my upcoming talk on spatialdata on SQL Server 2008 at ITForumin Barcelona in a few weeks I got to ask about using the new systemUDTs types on the client. In an upcoming CTP release there will be an MSI installerfile specifically to install these types on clients. The appropriate files are installedand assembly (Microsoft.SqlServer.Types.dll) registered in the GAC.Since these are .NET data types, these (HierarchyID, Geography, Geometry) ar...(truncated)...

Sync Services and SQL Server 2008 Change Tracking...a perfect match?

(Indexed 2007-09-21):

Just saw AaronBertrand's post from PASS on SQL Server 2008 Change Tracking vs Change Data Capture(change data capture (or CDC) is in the current CTP; change tracking isnot). I'm not at PASS this week myself, but home while the house is being re-roofed. Hispost seems to confirm something I'd suspected all along. Change tracking seems to go hand-in-hand with Sync Services for ADO.NET. I've beenfollowing SyncServices for a while; it's a set of libraries for controlling and implementingsynchronizati...(truncated)...

An example of using ODBC and SQL Server 2008 table-valued parameters

(Indexed 2007-09-20):

I'll be at TechEd and ITForum inBarcelona in November, doing sessions on SQL Server 2005 and SQL Server 2008. Oneof the TechEd sessions is called "To ODBC or to OLE DB?" and is a discussion of usingODBC and OLE DB is applications these days, whether one or the other is "better",if you should convert existing applications, etc. On that note...Back at TechEd US, I'd spoken with Chris Lee, who's in charge of SQL Native Client.That's the OLE DB provider and ODBC driver that ship with SQL Server. SQL...(truncated)...

More info on SQLCE 3.5 beta and LINQ and EDM support

(Indexed 2007-09-20):

I received mail today from the SQL Server Compact Edition folks at Microsoft on myblog post on using SQL Server CE 3.5 beta, Visual Studio Beta 2, LINQ, and EDM.The current plan is:1. Visual Studio 2008 will not ship with LINQ to SQLCE designer support. SQLMetalworks just fine with SQLCE, though, as I'd mentioned. 2. There is planned future support for using SQLCE with EDM and LINQ to Entities whenEDM ships after Visual Studio 2008 (VS 2008 SP1?).3. There is a fix in the works for the FK issue t...(truncated)...

SQL Server 2008 and .NET framework versions

(Indexed 2007-09-19):

So, people always ask... now that .NET Framework 3.0 installed on my system and there'sgoing to be a new version of .NET that includes LINQ, what version of the frameworkwill SQL Server load now, in SQL Server 2008? Does 2.0 still load? Or does it load3.0 or 3.5?Theoretically, and I've written this in books and previous blog entries, SQL Serverloads the most recent version of the .NET framework. .NET service packs thereforeaffect SQLCLR. Especially if they contain updates to say, System.Data.dll...(truncated)...

SQL Server 2008 and Clients - the long story

(Indexed 2007-09-19):

Let's talk about clients and SQL Server 2008. First, a little history...Every new version of SQL Server seems to include a new feature that requires a changeto the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS standsfor tabular data stream, and is a propriatary protocol used by both SQL Server andSybase. Since version 4.21, SQL Server's version of TDS and Sybase's version have"grown apart". Because TDS includes version negotiation you can still use old librariesto talk ...(truncated)...

SQLCLR and system functionality in SQL Server 2008 - part 3

(Indexed 2007-09-19):

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomainwhen its used by system functions, like SELECT * FROM sys.assemblies. Let's try anexperiment with the following setup. I have two user assemblies in a database named "test". One doesn't access any .NETtypes, its called datetest. The new DATE/TIME-related data type series are not .NET-based,but you can use them in SQLCLR procs, as you can use NVARCHAR data type. There aresome restrictions on DATE/TIME series...(truncated)...

SQLCLR and system functionality in SQL Server 2008 - part 2

(Indexed 2007-09-19):

I'm running with SQLCLR on, because I'd like to see the interaction between systemSQLCLR code and appdomains. See the previous blog posting for an explanation of whythis doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh",open SQL log in SSMS, and start a profiler trace to catch Assembly Loading events.SQLCLR is nowhere to be seen.USE TEMPDBGOSELECT * FROM sys.assemblies;GOThe assembly list contains an entry for Microsoft.SqlServer.Types, that's the assemblythat contain...(truncated)...

SQLCLR and system functionality in SQL Server 2008 - part 1

(Indexed 2007-09-19):

I was listening to a replayof the webcast recording on the HierarchyID by Michael Wang (thanks, Michael)and as he mentioned the considerations for the CLR-based type with respect to DDL,I thought it would be interesting to go back and see how this type showed up in thevarious facilities that we have for monitoring what SQLCLR is doing. These facilitiesare:1. Ability to see assemblies registed in each database2. Watch code-running appdomains (but not transient DDL-only appdomains) being created/t...(truncated)...

Using LINQ to SQL and EDM with SQL Server Compact Edition 3.5

(Indexed 2007-09-03):

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas withsome "different" data sources. This post covers using SQLCE 3.5. The latest versionof SQLCE 3.5 comes with Visual Studio 2008 Beta 2. Using SQLCE 3.5 data sources doesn't work with the built-in LINQ for SQL designerin Visual Studio 2008 beta 2 or the CTP EDM designer released shortly after beta 2.You can add a Data Connection for a SQLCE 3.5 database. I used the Northwind.sdf sampledatabase that was supplied. But ...(truncated)...

Using LINQ to SQL and EDM with SQL Server 2008

(Indexed 2007-09-03):

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas withsome "non-traditional" data sources. Namely SQL Server 2008 (CTP4) and SQL ServerCompact Edition 3.5. I'll cover using SQL Server 2008 CTP in this blog entry, andusing SQLCE 3.5 in the next one.Using SQL Server 2008 data sources doesn't work with the built-in designers in VisualStudio 2008 beta 2. Adding new "LINQ to SQL classes" produces an empty design surfaceonto which you can drag items from Server Explorer. P...(truncated)...

.NET Nullable Types as SQLCLR parameters in SQL Server 2008

(Indexed 2007-08-28):

Just found out about this one today. This SQLCLR function works right now inCTP4 of SQL Server 2008.public static Nullable AddTwo(Nullable x, Nullabley){ return x+y; }create function dbo.addtwo (@x int, @y int)returns intasexternal name asmname.[Mynamespace.Class1].AddTwo;select dbo.addtwo(2, null);-> nullGreat! Turns out, I suggested this backin 2005...after SQL Server 2005 shipped. And they listened. Actually, going backover the list from 2005... SSMS does display NULL for a NULL UDT already, ...(truncated)...

At TechEd Hong Kong: precon and day 1

(Indexed 2007-08-28):

11:00AM: I'm sitting in the lounge room at TechEdHong Kong, getting ready to do my first talk on SQL Server. It's an interestingsetup that we have this year, the wireless hub is located next to an area of booths.Lots of interaction (I think) between vendors, attendees, and speakers.The preconference talk on SQL Server 2005 best practices on Saturday seemed togo really well, with T-SQL query tuning taking center stage. There seemed to be alot more interest in XML functionality in SQL Server than ...(truncated)...

XEvent: Some final followup questions (Slight Return...)

(Indexed 2007-08-20):

Last blog entry on SQL Server Extended Events for a while. But...a couple of questionscame up since I wrote my first blog entry on SQL Server Extended Events. What are the major advantages to SQL Server Extended Events? Is this really using the Crimson event system?There's a couple of reasons that come to mind as an answer for the first one. First,and maybe foremost, this eventing system has an ETW target and therefore allows end-to-endtracing. ETW is a provider-based tracing system that is i...(truncated)...

Finishing XEvent package items, syntax, and semantics (moon turn the tides...)

(Indexed 2007-08-20):

This won't be as long of an entry because I'm trying to finish describing the itemsthat you can use in an XEvent session, that is, the items that exist in a package. Events Targets Actions Predicates Maps Types Let's do types and maps. A type is simply a datatype, a simple type like Int16 or complex type like 'SOS_context'. Almost all thetypes live in package0, there's only one each in sqlos and sqlserver packages. Mapsare enumerated constants.See types:select * from sys.dm_xe_objects wher...(truncated)...

Still raining, still dreaming...more about XEvent actions and targets

(Indexed 2007-08-20):

I wanted to finish things off by talking about actions and predicates.Need to make a detour at targets, too. I noticed the BOLexamples (my point is to try not to repeat things you can find in the BOL) don't containan example of actions in DDL. So we'll start with them. An action is an additionalpiece of data that you can tack on to an event. Like a stack trace, or even a causalityID. Or sql_text. The available actions can be seen with:SELECT * FROM sys.dm_xe_events WHERE type = 'action' So let's...(truncated)...

More about XEvent: actions and targets (still raining, still dreaming)

(Indexed 2007-08-20):

It's another rainy day in Portland in summer, so I thought I'd stay inside and writemore about SQL Server 2008 Extended Events.I wanted to finish things off by talking about actions and predicates.Need to make a detour at targets, too. I noticed the BOLexamples (my point is to try not to repeat things you can find in the BOL) don't containan example of actions in DDL. So we'll start with them. An action is an additionalpiece of data that you can tack on to an event. Like a stack trace, or even a...(truncated)...

More about SQL Server 2008 extended events: packages, events, and event sessions

(Indexed 2007-08-19):

It's summertime in Oregon and its been nice and warm out (75-85F) lately. But todayits raining (or specifically, the skies are quite ominous right now), so it'stime to write more about my latest favorite subject: SQL Server 2008 Extended Events.The SQL Server 2008 extended events introduce quite a bit of new terminology, butin investigating the specifics you come across some familiar themes.Extended events are all contained in packages. An event package isidentified by a GUID and a name. Three p...(truncated)...

Making the XEvent ETW target run

(Indexed 2007-08-16):

When starting out with XEvent support, I thought it would be good to start with theETW target, although you can capture and catagorize events in buckets with the asyncbucketizer target, and pair related events (like obtain lock/release lock) with thepair matching target. Both VERY cool. But I just wanted a raw, vanilla trace,to start out. And I wrote apaper on ADO.NET and ETW once. So easy one first, I thought...It turns out that you need privileges to start an ETW session. The ETW session issta...(truncated)...

XEvent in SQL Server 2008

(Indexed 2007-08-16):

When I'm doing problem solving, its always good to have too much information ratherthan too little. With this in mind, I was quite interested in looking at SQL ServerExtended Events (XEvent support) in SQL Server 2008.You could always get diagnostic information in SQL Server through DBCC and SQL Trace/SQLProfiler. In SQL Server 2005 there are enhancements to SQL Profiler, dynamicmanagement views (which enhanced and in some cases superceded DBCC information), DDLTriggers, and Event Notifications....(truncated)...

New SQL Server 2008 Date/Time-related types and ADO.NET

(Indexed 2007-08-02):

I've been looking at the mapping of the new Katmai date/time data types to .NET types,both from the point of view of SQL Server ADO.NET clients and of SQLCLR procedures/functions/triggers.There are a couple of things that "interesting", if I'm not misunderstanding somethingobvious.There are no new System.Data.SqlTypes to correspond to the new SQL Server data types.The beta Visual Studio docs state this as a fact. So, unless SQLCLR supports the genericNullable types as parameters in Katmai, you'l...(truncated)...

Katmai: Using Table-Valued Parameters with ADO.NET

(Indexed 2007-08-02):

I've been trying out table-valued parameters along with ADO.NET support in Orcas andcame across an interesting dilemma. It centers around INSERTs using TVP against atable with an identity column. ADO.NET can use DataTable, IDataReader or IListto represent a TVP parameter. Let's say that I want a TVP and a procedure forinsert that looks like this:CREATE TYPE dbo.JobsTableWithIdentity AS TABLE ( job_id smallint identity primary key, job_desc varchar(50), min_lvl tinyint, max_lvl tinyint);The "...(truncated)...

Two friends of mine (and SQL geeks) tie the knot

(Indexed 2007-08-01):

Now that Kim and Paul haveeach posted about it and even published some pictures, I guess its time forme to send out a heartfelt CONGRATULATIONS to them both on the occasion of their recentwedding last weekend. All the best in your upcoming life together!In a seperate/related announcement, Paul announced that he'll be joining SQLskillsat summer's end. Welcome, Paul, it will be fantastic to have you onboard![Image]This blog is sponsored by SQLSkills.

SQL Server 2008 July CTP - what's new (and what's gone)

(Indexed 2007-08-01):

I saw Dan Jones' posting thatthe SQL Server 2008 July CTP (aka CTP4) was available onthe Connect website. This one has some good things in it (like the new date/timedatatypes as well as the hierarchyid data type) that should keep me busy for a while.There's much more new stuff than that, but the connect website has also thedetalis.One thing that is included that isn't intuatively obvious is ADO.NET client support.The Visual Studio Orcas Beta 2 release contained a new version (well, its still cal...(truncated)...

Some book reviews

(Indexed 2007-07-17):

When I'm not busy writing about SQL Server, I quite enjoy reading books about it thatlook at things from a different point of view. I've been catching up on my readinglately, but getting behind on my book reviews. So here's a few reviews I'd been meaningto write for some time. The authors asked that I review them on Amazon, but I don'tlike either giving out personal information just to do a book review (too intrusive)or the idea of anonymous reviews/fake personal information (too easy to "stackt...(truncated)...

TechEd 2007 in Hong Kong - I'll be there

(Indexed 2007-07-17):

In just over a month, I'll be at TechEd2007 Hong Kong. I'll be delivering a preconferencetalk on (what else) SQL Server 2005 and a number of breakout sessions. Check theSQLskills Upcoming Events formore information. The folks in Hong Kong always put on an excellent show,and I'm very much looking forward to it. In addition, some of my old friends like JonFlanders and Ron Jacobs will also be there.It should be a good time, stop by and say hi.[Image]This blog is sponsored by SQLSkills.

MERGE, JOINS, and determinism

(Indexed 2007-07-10):

SQL Server 2008 will contain an ANSI SQL standard MERGE statement with extensions.Listening to the webcast lastFriday, there's a fairly straightforward way to describe how this works.Let's go back to first principals. Relational databases support two ways to do UPDATEand DELETE; positioned updates and searched updates. In a positioned update, you openan updatable cursor over a set of rows, navigate to the row you want, and issue an"UPDATE...WHERE CURRENT OF" statement. The searched update (UPDAT...(truncated)...

And the EAV winner is .... sparse columns

(Indexed 2007-06-24):

Many of you have already heard the "hardware store" story. What's the best way tomodel products in a hardware store, where new items arrive at the hardware store eachday. Each item has a "short list" of similar properties (e.g. UPC, price) and a longlist of dissimilar properties (e.g. paint has color, type, amount and curtain rodshave width, metal, etc). How to model the dissimilar properties for each item in relationaltable(s)?This isn't as unusual of a problem as you might think, examples I've...(truncated)...

ORDPATH, ORDPATH, everywhere

(Indexed 2007-06-24):

ORDPATH is a hierarchical labeling scheme used in the internal implementation of theXML data type in SQL Server 2005. It's meant to provide optimized representation ofhierarchies, simplify insertion of nodes at arbitrary locations in a tree, and alsoprovide document order. It's described in the academic whitepaper "ORDPATHs:Insert-Friendly XML Node Labels". In addition to being used internally when the XMLdata type is stored, its also part of the key of the PRIMARY XML INDEX, used to speedup XQu...(truncated)...

SQL Server system data types implemented in .NET

(Indexed 2007-06-24):

SQL Server 2008 will contain, if my count is correct, 7 new data types. Note: noneof these are in the current CTP.DATE - ANSI-compliant date data typeTIME - ANSI-compliant time data type with variable precisionDATETIMEOFFSET - timezone aware/preserved datetimeDATETIME2 - like DATETIME, but with variable precision and large date rangeGEOMETRY - "flat earth" spatial data typeGEOGRPAHY - "round earth" spatial data typeHIERARCHYID - represents hierarchies using path enumeration modelThe first four (...(truncated)...

A supported Service Broker external activator?

(Indexed 2007-06-24):

Had to write about another thing that "caught my ear" at TechEd during a chalktalkby Rick Negrin about Service Broker usage patterns. Service Broker supports "internal activation", that is, associate a stored procedurethat gets invoked when a queue has messages to process as well as "external activiation".When using external activation, an event notification occurs when a queue has messagesto process, and this notification is picked up by an external application; the externalapplication processe...(truncated)...

Table valued parameters in SQL Server 2008

(Indexed 2007-06-23):

The June CTP of SQL Server 2008 contains support for table-valued parameters. Here'sa usage scenario for these that has been around for a while. Imagine you are running an online store and deal with (among other data) orders andorder detail lines. You'd like to have a stored procedure that can add an entire orderin one server round trip, regardless of the number of items that I order (that is,1 order header and 1->n order detail lines). You'd even settle for two round trips,one for the order hea...(truncated)...

Grouping sets and TABLIX - made for each other?

(Indexed 2007-06-23):

SQL Server 2008 will include GROUPING SETS; a (very) short explanation is that theseallow the equivalent of multiple GROUP BY clauses in a single SQL statement.The result is a UNION ALL of the resultsets. SQL Server 2008 also contains/allowsstandard syntax for ROLLUP and CUBE, which have been in SQL Server for a while.One way to use (or to think of) grouping sets is that, while ROLLUP with N columnsproduces a UNION of N+1 results and CUBE produces N-squared -1 results, grouping setscan produce a...(truncated)...

SQL Server 2008 new features - the list

(Indexed 2007-06-23):

There was a fairly well-known Powerpoint slide that attempted to summarize the newSQL Server 2005 features in bullet points of a single slide. By the release,there were so many new features, the feature list had to be rendered in a 5-pointfont to fit. At TechEd 2007, Microsoft presented the new features of SQL Server 2008(was: SQL Server Katmai) in an analogous format. Although it's not yet down to a 5-pointfont, there are quite a few impressive new features on tap. Here's the list, modulo(my ow...(truncated)...

Katmai Sessions at TechEd

(Indexed 2007-05-11):

SQL Server "Katmai", which some folks are already calling "SQL Server 2008" becauseof its scheduled release date, was officiallyannonuced yesterday. I've been following things and noticed, a while back, thatthere are a number of Katmai sessions scheduled at TechEd in Orlando, in June.Now that there is an official announcement, I guess I can blog about these withoutpossibly hurting anyone's feelings. Note that, as with the official announcement,the descriptions are pretty sparse. So you'll have t...(truncated)...

DevDays Amsterdam SQL Server Postconference event

(Indexed 2007-05-06):

I'll be doing a one-dayworkshop on SQL Server 2005 in Amsterdam in June, as a postconference event of MicrosoftDevDays. Although I've done quite a few events on SQL Server 2005 before,this one will be quite a bit different from any that I've done before. It deals withbest practices.It's been about 4-5 years since I started teaching and working with early adaptersof technologies like SQLCLR, Service Broker, and XML data types. During the "breakin" period, people were most interested in how they w...(truncated)...

TechEd 2007 - I'll be there

(Indexed 2007-05-06):

I'll be at TechEd2007 in Orlando next month doing a breakout session on SQL Server 2005 scaleouttechnologies, as well as a couple of chalktalks (on eventing and WMI and on Powershelland SMO) in the SQL Server area. Stop by and say hi.And...if you're thinking of going to a pre-conference talk, Kimberly Tripp and I willbe delivering an information-packed day on Leveraging SQL Server Always-On Technologiesto Acheive High-Availability and Scalability. I'll be covering internals of some ofthe many sc...(truncated)...

EDM Wizard Not Working? Use EDMGen

(Indexed 2007-04-22):

The day after Orcas Beta1 appeared for public download reports appeared on the ADO.NETTechnology Preview forum that the EDM Wizard (that is, "Add New Item/ADO.NET EntityData Model) wasn't working. And, sure enough, if you choose "Generate From Database"and carefully choose which tables to use, you get a model with no entities. If youchoose an empty model, you get "Specified argument out of range of valid values",and get the three mapping files, but no language file. What to do?One way around thi...(truncated)...

SQL Server Compact Edition - newest stuff - SQLCE and entities

(Indexed 2007-04-22):

SQLCE 3.5 comes with an ADO.NET data provider that supports entities, by supportingthe ADO.NET 3.5 entity data model (EDM). I noticed this in Orcas March CTP with theinclusion of a new DLL, System.Data.SqlCe.Entities. Although the DLL was there, therewas no support in Visual Studio for any provider but SqlClient, and trying to do thismanually failed (DbProviderFactory.GetService returned null, IIRC).In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NETEntity Data Mode...(truncated)...

SQL Server Compact Edition - newest stuff - sync services

(Indexed 2007-04-21):

One of the latest developments in the SQLCE space is the beta for ADO.NET Sync Services.Sync Services ships as a standalone download, runnable on Visual Studio 2005 and isalso built-in to Visual Studio Orcas. Both versions require a new version of SQLCE,version 3.5. This runs side-by-side with SQLCE 3.1, although both version's DLLs havethe same names they are installed in different directories. The Orcas B1 version hasa newer version of the DLLs and install GUI support.The Orcas GUI support con...(truncated)...

SQL Server Compact Edition - additional pieces

(Indexed 2007-04-21):

So now that I've found SQLCE andknow which version I have, what can I do with it besides use the ADO.NET and OLE DBAPIs? Well, turns out I need some auxiliary pieces. If you use 3.0, the pieces "comewith", but when you upgrade to 3.1, you need to upgrade these. I must admin I found this somewhat confusing, probably because I "started from scratch"and also wanted to use 3.1. I had to intall some pieces before it would "recognize"others. For example, unless you have the Windows Mobile dev tools in...(truncated)...

SQL Server Compact Edition - versions and installed locations

(Indexed 2007-04-21):

As part of a project, I've had occasion to look at the SQL Server Compact Editionin earnest. Although I've taken a cursory glance before this, its my first attemptto put all the pieces into place. So I thought I'd write it down as I went along.SQL Server Compact Edition's original name was SQL Server Mobile, but as of version3.1 its now supported on a variety of mobile devices but its also supported onTablet and Desktop PCs. For simplicity, I'll refer to all versions as SQLCE from nowon.SQLCE sh...(truncated)...

Welcome, AdventureWorks Light

(Indexed 2007-04-18):

I always look through the SQL Server 2005 samples with each new incantation, and alwaysseem to find things in there that are new and interesting. In theSP2 version, the samples included a new sample database, "AdventureWorks Light".Also known as AdventureWorksLT. Because folks that teach SQL Server do have a bunch of canned queries for expositionand know exactly how those queries are supposed to behave, sample databases reallynever disappear. Pubs was the sample database inherited from Sybase, a...(truncated)...

Back home from Belgium Developer and ITPro days

(Indexed 2007-04-02):

I just returned from MicrosoftDeveloper and ITPro days in Ghent, Belgium. The hospitality was great, and theevent itself drew some big crowds of top notch developers and IT pros. Ghent was anamazing city, with medieval architecture sharing the city with modern buildings. Besidesthe four talks on SQL Server 2005 and ADO.NET that I did for the main conference,Wednesday, I gave a special talk on Event Notifications for the BelgianSQL Server user group. On the previous, the user group and I went out...(truncated)...

My SQL Server security best practices whitepaper is available

(Indexed 2007-03-21):

For folks that have been asking...my latest whitepaper "SQLServer 2005 Security Best Practices - Operational and Administrative Tasks" wasposted on the Technet website this week. It also covers the nuances of security whenusing SQL Server SP2 and Vista. Enjoy![Image]This blog is sponsored by SQLSkills.

A bonus talk for Belgian SQL Server user group next week

(Indexed 2007-03-19):

Next week (28-29 Mar) I'll be doing some talks on SQL Server 2005 and ADO.NETvNext at DevDays Belgium in Ghent. On 28 Mar, I'll also be doing a special additional talkon SQL Server 2005 Event Notifications (including a cross-database notification demo) forthe Belgian SQL Server user group at the event. If you're a registered member of theuser group, I'll see you there. You can also sign up to be a member of the user groupat the usergroup website. Thanks to the user group for inviting me.[Image]T...(truncated)...

Two things you can't do in SQLCLR

(Indexed 2007-03-13):

Since SQL Server 2005 was introduced with .NET programming support, folks have beentrying to push the boundaries of what can be used in SQLCLR or at least trying todetermine where those boundaries are. Here's two things that, as far as I know, can'tbe done in SQLCLR.1. Use dynamically generated code. The canonical example of this is dynamic serializationassemblies generated when you use "Add Web Reference" in the generated web serviceproxy code. The way around this is to use the sgen utility. Bu...(truncated)...

Using stored procedures with EDM ObjectServices in the March CTP (with code)

(Indexed 2007-03-04):

One thing that I thought was particularly interesting in the Orcas Mar CTP was supportfor stored procedures. This support exists in LINQ to SQL and EDM ObjectServices;I thought I'd start with ObjectServices. There almost no documentation on this topicat this point, about half a page with an incomplete mapping schema example. That'sto be expected at this point, though. In Mar CTP, you can specify stored procs for insert/update/delete, but not for EDMqueries yet. You need to change the SSDL (store...(truncated)...

SP2 must haves and SP2 compatibility

(Indexed 2007-02-26):

SQL Server SP2 was released last Monday. The links were posted on many blogs, so Iwon't repost any but the mainone here. SP2 is a "major" service pack because there are a few "must have" featuresin addition to some rather useful improvements and quite a few bug fixes. The onesI'd consider must haves are:1. Support for running SQL Server 2005 on the Windows Vista operating system. Vistawill inform you of the SP2 requirement when you install SQL Server 2005. You installthe "base" SQL Server 2005 f...(truncated)...

DevDays in Ghent - see you there

(Indexed 2007-02-26):

Next month I'm looking forward to speaking at DevDaysin Ghent Belgium on SQL Server 2005 and on ADO.NET vNext. The conferenceis being held on 27-29 Mar, but most (all) of my talks will be on Thursday, the29th. On Wednesday at noon, I'll be doing a book signing at the A/W booth. If you'rearound at DevDays drop by and say hi. Besides ADO.NET vNext, I'm talking about SQLquery tuning, making an application run faster using Service Broker, and SQL ServerSP2 and SQL Server on Vista.See you there![Imag...(truncated)...

SSIS Connectivity Whitepaper is Live on Microsoft website

(Indexed 2007-02-06):

My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live onthe Microsoftweb site today. For anyone who is confused by all of the whitepaper announcementslately, here is a list of titles and direct links.This one is about SSIS:Connectivity and SQL Server 2005 Integration Serviceshttp://download.microsoft.com/download/2/7/c/27cd735...(truncated)... these two (from last week) are about scaleout technologies and solutions. Thatis Service Broker, Scalable Shared Database, Peer-...(truncated)...

The Internals and Troubleshooting whitepaper is available

(Indexed 2007-01-30):

The companion whitepaper to my "Planning, Implementing, and Administering ScaleoutSolutions with SQL Server 2005" whitepaper (see yesterday's post) is available. Thiswhitepaper is called "Internals, Troubleshooting, and Best Practices for use of ScaleoutTechnologies in SQL Server 2005", with as much about internals as I could cram in50 pages. Again, I don't have the "main" link; the directlink is here. Enjoy.[Image]This blog is sponsored by SQLSkills.

The first of my scaleout whitepapers is available

(Indexed 2007-01-30):

I've been working on some whitepapers on scaleout technologies in SQL Server 2005.The first whitepaper is now available; I don't have the main link, but hereis the direct link to the doc file on the Microsoft download site. The whitepaperis about the implementation steps when using scaleout technologies like Service Broker,Scalable Shared Database, Query Notiifcations, and Peer-to-Peer Replication and howto choose which technology or combination of technologies is the best fit. It will be follow...(truncated)...

XQuery 1.0 et al are now W3C recommendations

(Indexed 2007-01-24):

Michael Rys (and who would know better/sooner about this) just announced onhis blog that XQuery 1.0 and associated specs (including XPath 2.0 and XSLT2.0) are now official W3C recommendations. Congratulations to the working group onthis. The specification process is also underway for a standard XQuery Update Facilityand XQuery/XPath Full-Text query facility. The specs are available on the W3Cwebsite.I'll have to revisit/reread the specs, now that they are finalized. The one that alwaysseem to ca...(truncated)...

A Draft of my SSIS Connectivity Whitepaper is available

(Indexed 2007-01-11):

The SQL Server 2005 Integration Services team has posted a draft of my SSIS ConnectivityWhitepaper on their connectivity wiki at http://ssis.wik.is (notethe change in the address of the wiki site). It addresses the details of usingOLE 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 tohear about it.Enjoy...[Image]This blog is sponsored by SQLSkills.

Some questions and answers on plan guides

(Indexed 2006-12-30):

Thought I'd answer one last question or two from the last time I did the talk on SQLServer 2005 plan guides. If you're asking at this point "what is aplan guide", reference the SQL Server 2005 BOL or this blogentry.Q. Can you use a plan guide on an encrypted procedure? I want to put a hint on encryptedvendor-supplied code.A. No, per books online you cannot create an OBJECT plan guide on any object thatis itself encrypted or references an encrypted object. You get error message 10512if you try.Q....(truncated)...

Does sp_dropuser "do the right thing" with schemas?

(Indexed 2006-12-30):

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 statementwithout a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the newuser usually has no access to. And that sp_adduser, for backward compatibility, willCREATE a SCHEMA named after the user and assign that SCHEMA as the user's defaultschema. Question was, "does sp_dropuser do the right thing and drop the schema namedafter the user?"S...(truncated)...

Using SQLCLR functions in indexed views

(Indexed 2006-12-30):

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 wroteto 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 simplesample, but there were a few surprises.1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement...(truncated)...

Don't be Afraid Of ... at ITForum

(Indexed 2006-11-13):

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 20052. Take some DBAs a bit out of their comfort zoneIf you've been avoiding these features until now, its time to take the plunge andlet these features help you, rather than avoid them and do without the beneifts theyprovide. The topics are:1. XML for DBAs - Showplan, DDL triggers and lots of things other things YOUuse are XML.2. Key Management - this is useful e...(truncated)...

One Service/Queue per Query Notification, please

(Indexed 2006-11-13):

Last week at TechEd I was showing off Query Notifications. When I showed using thepreprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor),a delegate asked about using the same queue with more than one subscriber. He repeatedthe question when I showed the low-level SqlNotificationRequest. I did some tests over the weekend and the answer is no. You need a separate queuefor each subscriber, otherwise the SqlDependency listener gets "confused". If youuse the dynamically...(truncated)...

SMO and Powershell: Better Together, The Scripts

(Indexed 2006-11-07):

Sorry, I know that slogan has been used already. Thanks to everyone who showed upfor the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, arethe demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showingup and showing me some neat shortcuts... as I typed. Many of the scripts werewritten "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 Sullivanand my "A...(truncated)...

All posts and opinions are the property of their respective Sources. The rest copyright (c) 2006 SQL Server Reporter.

Images have been removed from posts, and some posts have been modified for display purposes.

SQL Server is a registered trademark owned by Microsoft Corporation.

The SQL Server Reporter is in no way affiliated with or endorsed by Microsoft Corporation.

Jobs indexed from indeed.com.