Getting SqlDependency to work takes an enormous amount of orchestration up front - especially with regards to SQL Server permissions. Steve ran into a problem which made SqlDependecy work only intermittently. He finally got to the bottom of it (was related to the dbo owner) and has blogged about it here...

I was recently involved in an ASPAdvice thread about close and dispose - an age old .NET debate. (Okay, the "age old" part is relative.) As backup, I quoted the msdn documentation that says "close and dispose are functionally equivalent" and someone pointed out not to believe everything I read and that...

Okay - the post title is meant to be facetious, but this blog post on my DevLife blog is some early thoughts on the ADO.NET vnext whitepapers and some of the ORM community's reaction to them as well. [ read more ... ] [A DevLife post]...

Thanks to Fabrice for pointing out that the whitepapers for the next gen ADO.NET are online. I am really excited abut this stuff. I'm printing them out for my airplane reading. (Which is wholly unlike me!) I better get back to packing! Next Generation Data Access ADO.NET Entity Framework Overview...

Though I have the necessary permissions for using SqlDependency almost memorized (as well as documented in my presentations and my new CoDe Mag article on Query Notification ), I tend to forget that when using the lower level SqlNotificationRequest, that you need permissions to send and receive on your...

Someone asked me about ADO.NET indexing which encouraged me to write about some of the amazing results of some performance tests I have done comparing VS2003 and VS2005's DataTable indexing. [ Read more...] [A DevLife post] Don't Forget: www.acehaid.org...

Sahil's teaser video for his ado.net 2.0 video training course is great. Clearly a guy who loves data access and has an enormous reverence for data. But watch the video all the way through as you will surely get a great laugh at the end! Don't Forget: www.acehaid.org...

Did you hear that the ADO.NET 2.0 indexing engine screams? But not in the way I was expecting! [ Read more ... ] [A DevLife post] Don't Forget: www.acehaid.org...

There's a cool new .net 2.0 feature I didn't know about until I needed to do this a few days ago. If your application has FullTrust, you can use the System.Data.Sql.SqlDataSourceEnumerator to get a list of available SQL servers (2000 and 2005 only). The query returns a DataTable which you can just attach...

I went around and around (and dragged poor Sushil Chordia and blogless Leonid Tsybert along for the ride) with an access permission problem that my ASPNET account was having when trying to do SqlDependency.Start. All of the correct permissions (listed below) had been applied to the account, which could...

I have had to grok and also explain Service Broker as part of my work and presentations on Query Notification. I kind of get it on the surface but do not get it deeper down and will probably never write t-sql to work with it anyway. I was very happy in talking with a SQL Server guru recently who also...

Jon Hassel was the technical editor of Sahil's new book and is obviously very pleased to have participated on such a great book! Don't Forget: www.acehaid.org...

In my Sql Query Notification session, I had an odd problem. My SqlCacheDependency demo did not receive it's invalidation. This happened when I set it up in code and also when I set it up in <%Cache> directive on the page. This is a demo that I have done many times in the past year so I was not...

The rules for using Query Notification have finally settled after evolving through all of the betas and ctps. Here they are copied and pasted directly from the msdn help files: Applications that use query notification features need to take into account the following special considerations. Valid Queries...

I'm reviewing my demo that I built for caching data at the middle tier that is part of my query notification session at DevConnections next week - and had a funny moment. I wrote this a while ago and am just revisiting it and felt pretty impressed with myself when I saw how it worked. It's a little complex...

If you are doing an UpdateBatch with ADO.NET 2.0 and do not explicitly set an UpdateBatchSize parameter, this will default at one - meaning that one row will be sent at a time to the server for updating. Whatever other number you set it at is the number of rows that will be sent in a batch and of course...

Here is an excellent article on using ADO.NET and SQL CLR inside of SQL Server: Managed Data Access Inside SQL Server with ADO.NET and SQLCLR . Besides the obvious code, there are things you should be aware of such as context connections, how transactions work and most importantly, when not to use ADO...

There was a problem with SqlDependency pre-Beta2 which created headaches for anyone trying to use it as a non-admin. It also created headaches for programmers at Microsoft who were in charge of it. They finally reworked some of the plumbing in order to get past the permission issues. In this DataAccess...

One of the fun brain teasers that was asked in my What's new in ADO.Net 2.0 talk today at Code Camp 4 in Boston was if it was possible to create a DataReader from the original values of a DataTable using the new CreateDataReader method. Although there isn't a direct way to do it, I came up with a simple...

In my What's new in ADO.NET 2.0 talk, I have one slide and three quick demos on Query Notifications. For DevConnections, I am doing a whole talk on the topic. I was a little worried about filling up an entire session on the topic, but I have learned so much about Query Notifications, caching and SQL...

I recently received my TechEd 2005 conference DVDs which allow me to now catch up on many awesome sessions I was not able to attend while I was there. One session in particular that I just watched which is filled with great prescriptive information - not just how to , but when and why - is “DAT421: Client...

If you have ever used a SqlDependency in .NET 2.0, have you ever inspected the object before you execute your command? In the object is a property called Callback ID. When I am running both the .NET code and SQL Server on the same machine or same network, this is set up to traverse back through TCPIP...

I will get this to work! Currently I have to be logged in as an administrator to get SqlDependency to work. I have tried giving a variety of notification related permissions in the database to my lowly user login, but to no avail. I just came across this - oh, what a shocking place to find the info in...

One of the many topics I cover in my ADO.NET 2.0 talk is about the ability to modify the rowstate of unchanged rows with SetAdded or SetModified. If you have never been in a position where you wished like crazy that you could change the row state, or if you don't understand the semantics of DataRowState...

I have not looked at all at the strongly typed datasets in .NET 2.0, so when Dave Burke started talking about DataTableAdapters last night, I thought he was talking about the DataTableReader. He was pretty adamant. We gave him a hard time - it was late, we were punchy. What can I say. In reality, I just...

Just yesterday I was working some last minute review of a Customized Debugging in VS2005 presentation for DevTeach that I will be doing early next week and was stunned to see the "List" property gone when debugging into a rows collection of a datatable. Had the ADO.NET team taken this stuff back under...

I had someone ask an ADO.NET question last night that I just couldn't answer. It was a little more about distributed transactions which I have little experience with. So I had him type the question into Notepad on my laptop. Twenty minutes later, I saw Pablo Castro and had him type the answer so that...

Well that was fun. I just did a webcast with DCC Glen Gordon on MARS, a new feature of SQL Server2005 that is leveraged in ADO.NET 2.0. (I'll post a link to the on-demand version of it when it gets posted) Although I have looked at, experimented with, presented on and written about MARS previously, this...

I hope everyone got to catch the ADO.NET 2.0 (What's New Overview) webcast that Mark Dunn and Glen Gordon did yesterday. If not, look for it to show up in the on-demand webcasts soon. Tomorrow at 12pm EST I will be doing another ADO.NET 2.0 webcast with Glen, specifically on the topic of MARS (Multiple...

I am going to be doing a webcast with DCC Glen Gordon on the topic of MARS - Multiple Active Resultsets - on May 25th. This is one of a pair of ADO.NET 2 talks that Glen and Mark Dunn are doing together, but I'll be filling in for Mark on this one. MARS is a topic that I have been digging into for almost...

SqlDependency is really easy to get working if you are an admin, but when using it in a web app (whether via the System.Data.SqlDependency or System.Web. Caching.SqlCacheDependency) it is not so easy when you are not running under an admin account, which is what you should be doing (although maybe Don...

Thanks to everyone who attended my Web Services Security and ADO.NET 2.0 sessions. I have had so much awesome feedback on the security session which I really appreciate. The ADO.NET 2 talk is also one of my favorites. I could go on for days with that one as well. If you are looking for the slides for...

One of the killer problems I originally had with SqlDependency was that I was trying to use it against an old pubs database. Finally I discovered the database property called "Database Compatibility Level" and it was set to Version80. When I changed it to Version90 the SqlDependency worked again.Well...

I have looked at the benefits of the Load method on DataTables and DataSets that is in ADO.NET 2.0 in an article and a presentation that I have been having fun doing lately. Bill Vaughn gets really granular with the feature and proposes and interesting idea... http://www.AcehAid.org...

Wally and I have been keen on ensuring that SqlDependency - which was pretty tricky to get working when I was first learning about it- still works. Of course it does! :-) I have a handful of SqlDependency demos in my ADO.NET 2.0 talk that worked just great with the Feb CTP bits (after a lot of figgerin...

I had to get a copy of one of my clients sql server db tables onto my local sql server. For some reason DTS was failing on a particular record and I couldn't identify the problem or the record or the error. I finally gave up and went over to my whidbey box, set up an ADO.NET bulk copy and streamed the...

I have posted my powerpoint and demos from my ADO.NET 2.0 session. I loved doing this session. I even got to do it twice. I had lots of rave reviews from attendees. A big win-win. I am hopefully going to do it again at the Mid Atlantic Code Camp when I go to D.C. in May and again at my user group. It...

I am starting to realize that this tighter integration of ADO.NET and SQL Server means that I am going to have to get much more knowledgeable about SQL Server. I have been (to coin a great book title) a "reluctant DBA" for a long long time. But now that we can do things like BulkCopy in ADO.NET 2.0,...

holy canoli. remember when I compared using bulk copy to insert 104,000 records into another database? I compared it to dataadapter update using batch updating with the size set at 100. The difference in November CTP (VS2005 and SQL Server 2005 full version) was 21 seconds for the bulk copy and 41 minutes...

When I uninstalled the November CTP of SQL Server 2005, I inadvertantly deleted my databases as well. When I installed the Feb bits and looked at the instructions for getting AdventureWorks back in there, I didn't have the right files. So I trolled around the install disk, found the CAB file where the...

1) SqlDependency is throwing a wierd error just after the notification comes back from the sql server to the client: ObjectDisposedException - no stack trace, not happening within a method. This also effects the Web.Cache.SqlCacheDependency which uses the ado.net class (someone at MS is helping me with...

From Andrew Conrad's post about DataSets and Null values : [because the features are currently broken] , the DataSet behavior WRT to nullable types will either be changed or not supported for RTM of VS 2005. However, it is very probable that it will be supported some time in the future. see my previous...

Perhaps, it's true :-) (and hopefully just momentary...) I have LOST MY MIND! this works just fine: Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=myserver;Trusted_Connection=True;Database=pubs") Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("select * from authors...

In preparation for the ADO.NET 2.0 talk I am doing at ASP Connections in March , I am now looking at ADO.NET 2.0 from the perspective of a web developer and how some of these features will specifically enhance web development. It's an interesting way to look at it since every type of database programming...

I have written before about the wonderful new parameter of the DataView.ToTable method - DistinctRows. Interestingly, the new DataTable that is created still has ties to the parent DataSet. So if you want to add it to another DataSet you will have to use the DataTable.Copy, rather than the new DataTable...

(I have modified this post) When you move a DataTable's guts into another via DataTableReader, DataTable.CreateDataReader and DataTable.Load in ADO.NET 2.0, you don't carry over the DataSet parent hook and therefore you can add this 2nd DataTable to another DataSet without having to worry about using...

UpdateBatch has been dramatically changed with the current bits (41129) of VS2005. Here are the details on that . Now that we can exceed the 2100 parameter limit I have been doing some experiments with a 10000 record query from a 104,000 record table and doing batch updates in big chunks. It will be...

A few days ago I noticed that GetDataReader had an obsolete stamp all over it in the online docs. Jackie Goldstein as well as Kawarjit Bedi (from the ADO.NET team) let me know it was just because the name was being changed. I found this warning in the latest bits (November CTP) to be much less alarming...

(aha! just getting a new name: CreateDataReader . Thanks for the info Jackie and Kawarjit (from the AD0.Net team !) whoa. I just noticed this in the online docs that come up through the msdn library with vs2005 ( which points to this link ) The same holds true (as per the above set of docs) for DataTable...

read this and then this post to understand the problem. Well ... I started out trying this against pubs and it didn't work and then I tried it with AdventureWOrks and it didn't work. I focused on pubs and made a lot of changes without it working. Then I finally went back to AdventureWorks and it did...

There are SO many caveats to using query notification wtih SQL Server. Things like the fact that it is not based on transactions (eg you will get a notifcation even if something is rolled back). You have to do a LOT of trolling around in the books online to find all of this stuff and frankly, I still...

UNCLE! I have the right code from the documentation, from Bob B's awesome article , from the whidbey.adodtnet newsgroup, these posts from Wally McClure ( rules , enabling CLR in SQL ) and he was kind enough to spend a good chunk of time with me on i.m., too. But I cannot freaking get a notification from...

I decided to move all of my posts regarding ADO.NET 2.0 into their own category. http://www.thedatafarm.com/blog/CategoryView.aspx?category=ADO.NET2 Posted from BLInk!...

One of the new features in ADO.NET 2.0 is the DataTableReader . I read about this and invented all kinds of great uses for it in my head. The DataTableReader is a firehose from a DataTable or DataSet, much like a SQLDataReader is from a SQL Database. I dreamt of ability to disconnect the DataTableReader...

BulkCopy sounds great on paper, but have you actually tried it? I did. I took a 104,000 record table from SQL2000 on one server and used BulkCopy to import those records into a pre-existing table in SQL2005 on another server. But first I tried the same operation with DTS as a benchmark and that took...

My eyes are getting bigger and bigger as I'm digging into transactions in ADO.NET 2.0, or I should really just say in .NET 2.0. First, I like that it solves a problem I currently have with a .NET 1.1 application. I have to insert data into two tables. The first gets one new record, the second gets many...

Sahil (who is the author of this book on ADO.NET ) and I chatted about transactions in MARS after my DotNetRocks show on Friday. I think his curiousity lay in the potential of multiple transactions within one connection in MARS. Rather than theorize, I thought I would just try it in code and my answer...

I've read about MARS, thought about MARS and written a little about it what I learned. Now I'm going to divert my attention from WSE for a while and start playing again wtih ADO.NET 2.0. The first thing I'm going to do is take Angel's MARS FAQ blog post (he's on the ADO.NET team) and try out some of...

dataview.totable(distinct) and she shows us some code for achieving this now ....

While digging into some ADO.Net 2.0 a few weeks ago , I found that iXMLSerializable, previously (or is that currently) hidden in .NET 1.x and used for datasets has now been exposed so that we can leverage it. I had been “victimized” (I say that tongue-in-cheek) by accepting the magic of my...

I am reading Pablo Castro's article on doing Asynchronouos processing in ADO.NET 2.0 . Pablo is a/the (?) Program Manager on the ADO.NET team. This is the guy who, in his What's New in ADO.NET Whidbey session at TechEd said that his team fought over a particular feature “because we love the developers“...

In ado.net 2.0, datatables are serializable (grin grin grin) so that means you can return them from a web service (though you must fill the tablename property first). Below is what the xml looks like (compared below that, to what the same table looks look like in a dataset) Not much difference here....

Since System.Data does not fit into the scope of my BCL talk and I LOVE ADO.NET and LOVE some of the new things in System.Data, I thought I would point them out here quickly for anyone who hasn't seen it yet. • More user of DataReader – DataSet.GetDataReader – DataSet.LoadfromDataReader...