I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like most folks that aren’t completely centered in that part of the data-based offerings, the plethora of new functionality and new releases made it difficult to keep up. The online help […]
Bob Beauchemin
SQL Server Blog
Book Review: Chris Webb’s “Power Query for Power BI and Excel”
Posted on: September 15, 2014 11:11 am
New Azure services and evolution of the Service/SQL Server relationship
Posted on: August 21, 2014 2:42 pm
Today, two new service offerings for the Azure platform were introduced, DocumentDB and Azure Search. These are exciting in themselves and worth a look-see but, to me, they are also occurrences of the phasing out of the “database as an application server” concept, formalized in the SQL Server 2005 timeframe. At least, in the cloud. […]
Using filestream, streaming I/O and SQL logins with impersonation
Posted on: July 27, 2012 11:31 am
A number of months ago, I wrote an article using the SQL Server filestream feature programmatically. I've gotten a number of inquiries since from folks who *need* to login to SQL Server using SQL logins (often the same SQL login for everyone), but want to access the stream using streaming I/O (e.g. SqlFileStream data type). […]
Does everybody get that? (Type system compatibility mode in drivers)
Posted on: June 19, 2012 11:03 am
This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. The SQL Server "MAX" data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) have been around for some time (SQL Server 2005) now. And the […]
SQL Server and Pooled vs. Non-pooled connections
Posted on: May 26, 2012 5:08 pm
A variation of this question came up a few weeks ago on a mail list that I subscribe to and it seems to come up a lot. "Can SQL Server distinguish a which connections use connection pooling?" The answer depends on how you ask the question, and I've tried to ask it in such a way […]
Native ODBC components in SQL Server 2012 SSIS
Posted on: November 26, 2011 4:58 pm
Just had the occasion to look at SSIS Sources and Destinations in SQL Server 2012 RC0. After reading about the stated direction to use ODBC for SQL Server/relational data access over OLE DB and writing up my own blog post about it, I wondered how long it would be before a native ODBC source and destination appeared […]
Speaking at Portland, OR, User Group in December
Posted on: November 4, 2011 1:54 pm
Speaking at a user group is a bit different from speaking at a conference or other event. Because you know the folks there personally (hi Ken, Chris, and all ;-), you can ask around and "do requests", rather than just do what's interesting to you at the time. This one's a request. On December 8, I'll […]
SQL Server 2012 client features – ADO.NET 4.02 released
Posted on: November 3, 2011 2:09 pm
This post is about the changes in the client stack for SQL Server 2012. It's a very short post. The last two major releases of SQL Server (2005 and 2008) corresponded to .NET 2.0 and .NET 3.5 SP1 respectively. In addition, each one corresponded to a new version of SNAC (SQL Native Client, that is, […]
Adieu, OLE DB
Posted on: August 31, 2011 5:19 pm
The writing has been on the wall for some time, but a few days ago Microsoft made it official. OLE DB will no longer be supported with SQL Server after the Denali (SQL Server 11.0) release of SQL Server. Microsoft is "embracing" ODBC. "OLE DB will be supported for 7 years from launch" (of SQL […]
Correlating client and server-side trace in SQL Server Denali
Posted on: August 19, 2011 11:28 am
I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for. I've always been interested in (well, wrote a whitepaper on) using […]
TVPs and plan compilation – the reprise
Posted on: March 13, 2011 4:53 am
A few months ago, I published a blog entry entitled "The interesting case of TVPs and plan compilation" about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while, I published a connect bug. Turns out that this behavior […]
The interesting case of TVPs and plan compilation
Posted on: December 23, 2010 1:49 pm
A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), […]
LINQ to SQL and Entity Framework: Panacea or evil incarnate? Revisited.
Posted on: July 1, 2010 1:50 pm
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 […]
Guide to the Data Development Platform for .NET Developers whitepaper available
Posted on: November 18, 2009 4:18 pm
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. […]
Upcoming improvements in EDM T-SQL code generation
Posted on: August 7, 2009 2:53 pm
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 […]
L2S and EF parameterization problem will be fixed in .NET 4.0 – completely
Posted on: May 26, 2009 10:56 am
For those of you who's blog readers don't always get updated (rather than brand new) blog entries, there's a happy ending to my last "fixed-almost" blog entry on EDM 4.0 and parameterization. I've heard that this has been fixed to generate VARCHAR(8000) parameters and also that the unparameterized version: var x = from a in ents.authors […]
L2S and EF parameterization problem fixed in .NET 4.0 – almost
Posted on: May 25, 2009 2:29 pm
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 { […]
Is LINQ the next OLE DB? “LINQ-ed” Server as a rowset source?
Posted on: December 10, 2008 3:56 pm
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 […]
What the Entity Framework has going for it
Posted on: December 10, 2008 3:19 pm
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 […]
What’s the DataSet doing in a trace of an Entity Framework program?
Posted on: May 30, 2008 11:48 pm
In a blog posting about a few weeks ago, I'd written about noticing a DataSet being created in an Entity Framework program by using the ADO.NET client trace facility. Entity Framework programs do (indirectly) cause a DataSet to be created, but its only for resolving the ProviderInvariantName of the underlying data provider. NOT for anything […]
SQL Server 2008 Change Tracking and Sync Services ARE made for each other… in VS2008 SP1
Posted on: May 16, 2008 11:12 pm
This item was mentioned in other blogs too, but just to mention it again (so my SQLCE and Sync Services friends won't think I overlooked it)… Visual Studio 2008 SP1 Beta contains direct support for using SQL Server 2008 Change Tracking. When you use ADO.NET 1.0 Sync Services with a SQL Server 2008 database, the […]
SQL Server 2008 data types in LINQ to SQL and EF, they (mostly) did it
Posted on: May 16, 2008 10:43 pm
Last fall I'd asked the folks on the data access team about the possibility of including the new SQL Server 2008 data types in the new data access object mapping layers. At that time they said "no" but they also asked "why do you want it?" and "are people expecting it?". Someone must have had […]
SqlClient, System.Transactions, SQL Server 2008, and MARS
Posted on: April 5, 2008 10:14 pm
It appears that there are some changes in .NET 3.5 System.Transactions (or System.Transactions.dll and 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. And Alazel Acheson blogs about changes to SqlClient changes to accomodate using lightweight transactions with less promotion to distributed transactions in the ADO.NET Team blog. […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 6
Posted on: March 7, 2008 11:08 pm
This is the last post in the series, at least for now. I'll update it (or post more on the topics discussed here) as the products involved evolve and mature. This one's about: LINQ to SQL and EF queries will be "untunable" because, even if you discover a performance problem, you can't change the underlying […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 5
Posted on: March 6, 2008 8:39 pm
This post is part of a series about worries when implementing LINQ to SQL or ADO.NET Entity Framework from a SQL database-centric programmer's perspective. The last two worries are related. It's mostly about either level of abstraction being one level away from the "real SQL code" that's being executed. First off… LINQ to SQL and […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 4
Posted on: March 6, 2008 7:41 pm
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 means extra database round-trips. I really put […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 3
Posted on: March 1, 2008 10:52 pm
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" rather than issuing SQL UPDATE/DELETE statements that are set-based. […]
Lots of ORM and VS activity lately
Posted on: February 26, 2008 7:48 pm
Quite a bit of activity last week in the Visual Studio and ORM spaces. Visual Studio released a CTP of Visual Studio 2008 support for SQL Server 2008 CTP6. It allows you to connect to CTP6 with Server Explorer, which enables quite a few other features to work. check with Overview document on the download […]
LINQ and LINQ to XML to appear in SQL Server 2008 SQLCLR approved list
Posted on: February 15, 2008 12:58 am
Sometime before SQL Server 2008 RTM, the libraries for LINQ and LINQ to XML (that is System.Core.dll and System.Xml.Linq.dll) but NOT LINQ to SQL will be added to the SQLCLR "approved" assembly list. The approved assembly list is the list of .NET Framework libraries that have been tested in a SQL Server-hosted environment, annotated their […]
Learning LINQ, LINQ to SQL and eSQL
Posted on: February 15, 2008 12:24 am
I often hear those who identify themselves as "database programmers" (sometimes I think these may be folks who program only inside-database code), say: "I'd give learning LINQ and/or LINQ to SQL/Entities or Entity SQL (eSQL) a go in my sparse time, but I don't have a good book or a tool. I haven't read a book on […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 2
Posted on: February 14, 2008 11:55 pm
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 get back a nice, clean object this way instead of the less-useful anonymous type you receive by doing projection. This will also make most/all covering indexes useless. LINQ to […]
MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1
Posted on: February 14, 2008 1:47 am
I'd been meaning to write this entry for a while, about my opinion on LINQ to SQL and the ADO.NET Entity Framework and performance. I've just finished reading the 2-part blog series "Exploring the Performance of the ADO.NET Entity Framework", and was surprised (I guess) that the database performance aspect was barely mentioned. One way to […]
Composable DML and Composable Queries
Posted on: January 21, 2008 12:30 pm
In a previous blog entry a while ago, I wrote about writing a single SQL statement that did a SQL MERGE operation, used the OUTPUT clause to put out a rowset and directed the rowset into an INSERT statement. This happens in one statement without the need of explicitly defining a temporary table and using […]
Mapping Insert/Update/Delete sprocs with Many-to-Many
Posted on: December 26, 2007 10:13 pm
After getting insert/update/delete stored procedures going with a simple standalone table (no relationships), I started thinking about how I'd do this in a many-many relationship. Let's say that I have students and classes, with a many-many relationship represented by a studentclass table with only a student_id and class_id. EDM maps this to a many-many relationship, leaving […]
Mapping Insert/Update/Delete sprocs with Entity Designer
Posted on: December 26, 2007 7:55 pm
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 used to. The ADO.NET Entity Framework designer actually […]
SQL Server 2008 and ADO.NET Sync Services
Posted on: December 20, 2007 8:08 pm
Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server's new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I'd encourage you to start there. But about "why it's […]
Entity Framework Beta3 – Deleting without fetching
Posted on: December 9, 2007 4:36 am
As a continuation of the previous discussion, here's a DELETE of a title row without fetching 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(deletetitle); model.SaveChanges(); In this case, you need to populate both the property (title_id) that corresponds […]
Entity Framework Beta3 – In search of round-trip optimization
Posted on: December 9, 2007 3:39 am
The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog. One of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. […]
Astoria… I learned something today…
Posted on: November 10, 2007 10:32 pm
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", my first impression of REST were, I'll have to admit, the thought that it was web services without schema. I kinda like my metadata; it's always […]
An example of using ODBC and SQL Server 2008 table-valued parameters
Posted on: September 20, 2007 11:15 pm
I'll be at TechEd and ITForum in Barcelona in November, doing sessions on SQL Server 2005 and SQL Server 2008. One of the TechEd sessions is called "To ODBC or to OLE DB?" and is a discussion of using ODBC and OLE DB is applications these days, whether one or the other is "better", if […]
SQL Server 2008 and Clients – the long story
Posted on: September 19, 2007 9:06 pm
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 change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by […]
Using LINQ to SQL and EDM with SQL Server Compact Edition 3.5
Posted on: September 3, 2007 3:26 am
I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "different" data sources. This post covers using SQLCE 3.5. The latest version of 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 designer in Visual […]
Using LINQ to SQL and EDM with SQL Server 2008
Posted on: September 3, 2007 3:18 am
I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "non-traditional" data sources. Namely SQL Server 2008 (CTP4) and SQL Server Compact Edition 3.5. I'll cover using SQL Server 2008 CTP in this blog entry, and using SQLCE 3.5 in the next one. Using SQL Server 2008 data […]
New SQL Server 2008 Date/Time-related types and ADO.NET
Posted on: August 2, 2007 10:51 pm
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 something obvious. There are no new System.Data.SqlTypes to correspond to the new […]
Katmai: Using Table-Valued Parameters with ADO.NET
Posted on: August 2, 2007 9:49 pm
I've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, DbDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let's say that I want a TVP and a procedure for insert that […]
EDM Wizard Not Working? Use EDMGen
Posted on: April 22, 2007 3:07 am
The day after Orcas Beta1 appeared for public download reports appeared on the ADO.NET Technology Preview forum that the EDM Wizard (that is, "Add New Item/ADO.NET Entity Data 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 […]
Using stored procedures with EDM ObjectServices in the March CTP (with code)
Posted on: March 4, 2007 12:36 am
One thing that I thought was particularly interesting in the Orcas Mar CTP was support for 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 topic at this point, about half a page with an incomplete mapping schema example. That's […]
SSIS Connectivity Whitepaper is Live on Microsoft website
Posted on: February 6, 2007 12:59 am
My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live on the Microsoft web site today. For anyone who is confused by all of the whitepaper announcements lately, here is a list of titles and direct links. This one is about SSIS: Connectivity and SQL Server 2005 Integration Services http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc […]
The Internals and Troubleshooting whitepaper is available
Posted on: January 30, 2007 10:52 pm
The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have […]
The first of my scaleout whitepapers is available
Posted on: January 30, 2007 1:13 am
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 here is the direct link to the doc file on the Microsoft download site. The whitepaper is about the implementation steps when using scaleout technologies like Service Broker, Scalable Shared […]
LINQ/EDM in SQLCLR?
Posted on: October 20, 2006 8:16 pm
Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question. LINQ and Entity SQL are QUERY languages. Although they might be able to […]
What IS ADO.NET vNext anyway?
Posted on: September 26, 2006 7:24 am
I did a talk yesterday at TechEd Hong Kong on ADO.NET vNext. Most of them just wanted to know what ADO.NET vNext was about. Short and sweet. Here goes… There are two main new features: 1. Entity data model (EDM) – a mapping of objects over relational database to bring data access to a higher […]
Who uses MARS? ADO.NET vNext, that’s who.
Posted on: September 16, 2006 8:48 pm
There was a newsgroup discussion recently about the intricacies of Multiple Active Resultsets in SQL Server 2005, and one of the questions that came up was "Who actually needs/uses MARS anyway"? I've been taking a much closer look at ADO.NET vNext lately, one reason being that I'm doing a talk on it at TechEd Hong […]
New paper on Data Access Tracing
Posted on: August 14, 2006 8:01 pm
I came across a new article, "Data Access Tracing In SQL Server 2005" at MSDN and I'm listed as one of the authors. That's interesting, as I didn't remember writing it 🙂 but upon closer inspection, they give me credit because they leveraged my original article "Tracing Data Access" from the ADO.NET 2.0 series. Thanks Jennifer and […]
Entities and SQL Server 2005 Metadata. Really.
Posted on: July 21, 2006 7:09 am
After having a go at the information on the Entity Framework and other ADO.NET 3.0 papers that were posted about a month ago, I was trying to come up with a good example of relational tables or views that are designed with entities in mind. Or perhaps from entities. Concepts like inheritence, for example. Unlike […]
DLINQ and FLWOR
Posted on: June 7, 2006 5:51 am
I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of […]
Expose data through an ADO.NET data provider
Posted on: March 27, 2006 12:43 am
A frequently asked question since SQL Server 2005 shipped is "how do I expose my custom data source to SSIS/SQLRS/your app here". If you have a data server that you must connect to, and it accepts commands and returns resultsets, its a nature fit for an ADO.NET data provider-based mechanism. Having worked with OLE DB […]
New samples; Calling Oracle from SQLCLR
Posted on: February 10, 2006 11:04 pm
I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1/4/2006" and its 20mb instead of the old once (just under 16mb). The site says: Since its original release, […]
SQLCLR, transaction nesting, and TransactionScope
Posted on: August 25, 2005 6:55 am
Ahh…enough shameless self-promotion (for a while), let's talk transactions. I worked in the past with folks who like to compose transactions. SQL Server supports nesting of transactions and named savepoints but not autonomous transactions. So CREATE PROCEDURE X AS BEGIN TRAN — work here COMMIT calling it standalone means the work is in a transaction. […]
Whatever happened to…
Posted on: August 7, 2005 6:31 am
I answered a question on the beta newsgroups last week about OSD and RSD (remember them?) by saying that those features had been pushed out into the future. The person then asked if the material in chapter 14 of our first look book had any practical value. I must admit that you can't cut and paste the examples and […]
How DO you change your SQL Login password?
Posted on: June 3, 2005 9:27 pm
SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd […]
DataDirect providers…and some ADO.NET 2.0 beta2 changes
Posted on: May 16, 2005 5:25 am
Realized that I haven't blogged in a while. I been …uh…working on stuff and traveling a lot lately. Big surprise, right? This week I'll be on vacation. Traveling. Now I understand what the term “busman's holiday“ means. Last weekend I had dinner with Rob Steward of DataDirect Technologies at his house. The ribs were great (thanks […]
System.Transactions, promotable transactions, and composition
Posted on: February 9, 2005 11:52 pm
There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points. The first point of interest is that you will use System.Transactions to use transactions in SQLCLR procedural code in SQL Server 2005. In the beta 2 implementation of the SqlServer data […]
IRowsetFastLoad, the reprise
Posted on: January 6, 2005 10:54 pm
I had an interesting flashback today. Someone wrote with a reference to a mailing list posting I'd written in 2000, about IRowsetFastLoad. They wanted code, because nowadays attachments to public postings have become a thing of the past. Looked in my OLE DB code, from back when I was teaching "Essential OLE DB", and … […]
More ADO.NET 2.0 and XML data type
Posted on: November 30, 2004 7:42 pm
When you retreive a rowset that contains an XML data type column or use an XML data type column as parameter input, you have two choices. You can use the vanilla .NET type, System.String, or a special types from the System.Data.SqlTypes namespace called SqlXml. ADO.NET 2.0 handles conversion when you use strings (as long as […]
Thought on Microsoft client XQuery
Posted on: October 30, 2004 9:12 pm
I've known for a couple of weeks now that XQuery and the new XML-SQL client mapping have been dropped from System.Xml in .NET 2.0. The XQuery implementation over the XML data type in SQL Server 2005 is NOT going away, of course. Just the client-side bits. Folks are encouraged to keep using XSLT 1.0 and […]
Another minor SNAC/SQLOLEDB provider difference
Posted on: October 6, 2004 5:12 am
The new SNAC (SQL Native Client) OLE DB provider handles naming of parameters a tad differently than SQLOLEDB. Let's say that I have the parameterized query (using the ODBC-style question mark parameter markers): SELECT * FROM authors WHERE au_id LIKE ? And I call OLE DB's ICommandWithParameters::SetParameterInfo to set some parameter information. SetParameterInfo takes a parameter […]
OLE DB/ODBC, SNAC, and the new “MAX” data types
Posted on: October 5, 2004 6:08 am
After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. […]
XML and SQL Server: stream, column, or string?
Posted on: September 9, 2004 4:46 pm
With the introduction of the XML data type (using an XML data model) comes there's a choice of pulling XML out of the database as a stream or column. Using an XML data type as a column type will get you a column of type XML. That's SqlXml to you in .NET. SELECT … FOR […]
Better XML to DataSet inference in .NET 2.0
Posted on: August 16, 2004 5:32 pm
When you load XML into an ADO.NET DataSet using it's ReadXml() method, ADO.NET uses a fixed set of rules to determine how to infer a relational structure for the XML. These rules are doc'd in the .NET framework docs. One of the most frustrating rules was that you couldn't have the same attribute name or […]
SqlTypes.SqlXml and XmlReader.Create
Posted on: August 12, 2004 10:12 pm
As Mark Fussell will tell you, one of the coolest things in System.Xml 2.0 is the static creator methods on XmlReader and XmlWriter. It made his top 10 list, albeit at number 10. I've been looking into how the client side handles SQL Server 2005's XML data type and was bemoaning the fact that System.Data.SqlTypes.SqlXml […]
Where did the netlibs go?
Posted on: August 10, 2004 5:29 pm
I’m a person who always likes to know where things live in the OS, database, or whatever product I’m dealing with. Being able to point to a specific DLL or configuration file or registry entry gives me something tangible to hang my hat on, rather than think that things happen “by magic”. So I […]
On mapping between data models
Posted on: July 21, 2004 11:59 pm
In recent posts I'd mentioned mapping a few times, it's time to get back and explore it in earnest. There appears to be three major data models in use by programmers today: Relational – that's where the majority of the corporate data is stored. SQL and its product-specific dialects is the main programming language that follows […]
A sample ADO.NET 2.0 data provider
Posted on: July 9, 2004 3:30 am
After writing DevelopMentor's Essential OLE DB class and teaching it to provider writers (and detail-oriented consumer writers) for a few years, I always thought that a good way to get myself acquainted with the new data model was to write a new database client provider/driver/etc whenever the model changed. I wrote a simple .NET data provider during […]
Have a SNAC
Posted on: July 7, 2004 4:23 am
If you've recently installed the SQL Express version of SQL Server 2005, you may or may not have noticed SQL Native Client (SNAC). If you want to use the new features of SQL Server 2005 like multiple active resultsets or snapshot isolation from OLE DB, ADO, or ODBC, you're going to need SNAC. SNAC is […]
