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 blog post, Sushil Chordia not only explains why the problem existed, but what they did to fix it, how to use SqlDependency now (most changes were in the plumbing, but you do have to do some minor changes in your code) and also what permissions are necessary in SQL Server to get it working.

Don’t Forget: www.acehaid.org

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 way to achieve this.

Basically you get a dataview of the table. Set the RowsStateFilter to OriginalRows. (That’s available in 1.x also). Then use the new DataView.ToTable method to create a new table. Lastly, use the Table.CreateDataReader to create a DataTableReader.

dim dv as DataView=myTable.DefaultView
dv.RowStateFilter = DataViewRowState.OriginalRows 
dim dtNew as DataTable=dv.ToTable()
dim dtr as DataTableReader=dtNew.CreateDataReader

Don’t Forget: www.acehaid.org

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 Server Service Broker, that I could go on and on about it.

For anyone who has started working with this fantastic new result of collaboration between ADO.NET and SQL Server developers at Microsoft, there are some post-Beta2 changes you should be aware of.

Most importantly, it will, indeed, be possible to use query notifications (easily) when running as a non-admin. Phew!

Also, the plumbing has changed. Through Beta2, notifications come back through either TCPIP or HTTP. This was creating the permission issues above. Now It will be necessary when using SqlDependency or SQLCacheDependency, to do an application wide SqlDependency.Start, which will open up a SQL Connection for the notifications to come through.

Currently there are some settings that need to be tweaked which we haven’t figured out yet so I wouldn’t bother trying to use this post Beta2 anyway. After many many (many) hours of experimenting, I am determined that there is some setting in SQL Server that we are missing. (And I don’t mean enabling service broker on the database). The experimenting pays off with a deeper understanding of how things work.

As soon as I find out the magic combo of settings, I will be sure as ___ 😉 to post them here!

Update: My old friend CompatibilityLevel had come back to bite me in the rear end – and hard. I was using the pubs database. Steve Smith was having the same problem and using the Northwind database. I was using a new install and therefore not inheriting my pubs db where I had changed the setting for pubs to 90. I thought the issue was gone, but it wasn’t.

So, though this deserves it’s own blog post – remember you can check and set the compatibility level. You need 90 for working with Notification Query. I wonder if Service Broker has the same requirement.

will tell you what the level is.

sp_dbcmptlevel yourdatabasename,90

will set it to 90.

Thanks to Sushil Chordia and Leonid Tsybert at Microsoft for their help with this!!

Don’t Forget: www.acehaid.org

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 and Middle Tier Data Caching with SQL Server 2005” by Pablo Castro and Steve Lasker. One of the gems in there is this slide by Pablo, who is the PM on the ADO.NET team. [read more]

[A DevLife post]

www.acehaid.org

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.

 “<MachineAddress>tcp://192.168.0.5:58343</MachineAddress>
<AuthType>None</AuthType>
<Key>a55f6539-5d30-4e67-b87a-a4e3ebb85131</Key>” 

192.168.0.5 is the address of the client machine and 58343 is one of the ports that SqlDependency is pre-defined to use. This is using Beta2 bits and I expect that this will change when I load the next CTP onto my computer. But for now, it’s interesting to see.

I am now curious to see what the message looks like when it arrives in SQL Server’s clutches, before it strips out the query and sends it off to be processed.

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 the documentation for VS2005! 🙂 I must have come across it early on before I understood what the heck I was reading. Now it makes sense to me though and these looks like some good clues!

Security

The dependency infrastructure includes a client-side listener that is contacted by the server to send notifications. This listener is protected by Code Access Security attributes and by authentication. For more information, see SqlNotificationPermission Class and SqlNotificationAuthType Enumeration.

Code Access Security Assertions

The listener infrastructure enables the listener to listen on TCP ports or HTTP ports depending on which system is running. To use the listener, which is necessary to use SqlDependency, the caller must have SqlNotificationPermission. The use of the SqlNotification request is independent of the dependencies and listener infrastructure on the client. Therefore listener permission is not required.

I know that I did grant Query Notification permissions to my lowly user account ( as a test) and also QueryNotifcationErrorsQueue as the exception instructed me to. I also opened up some of the recommended array of ports (which at the time was supposed to be a bit of a hack fix).

Still I never got it working.But I am definitely adamant about succeeding at this! So I will be sure to post my solution when I get there.

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 had no clue. So Dave now has a post explaining all about the TableAdapter here!

www.acehaid.org

[A DevLife post]

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 I could get it back to Ziga without misinterpretation. Pablo also filled me in so that I understood the question as well as the answer. I thought I would share it here….

Q. What’s the performance difference when using a transaction scope vs. assigning transactions to connections & sqlcommands the ado.net 1.1 way?

A. Short answer (there is much more to this topic)

If you’re using System.Transactions against a SQL Server 2005 server, then the cost is more or less the same (assuming that you don’t bring more than one Sql connection to the scope).

On the other hand, if you’re hitting a SQL Server 2000 server, then System.Transactions will be more expensive because we’ll “promote” the transaction (into a distributed transaction) even for the very first connection.

Posted from BLInk!

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 was the deepest I have gotten into it. Glen and I had a lot of interesting talks in advance of doing the webcast. One thing that I really have learned a lot more about is transactions, where we can benefit from MARS the most.

Doing a webcast is fun, but for me the downside is not having the interaction with a group of people that you get doing a live presentation. You can see on people’s faces when something is confusing, or if you are going on a little too long about something you may think is really fascinating, but they don’t. 🙂 and make adjustments as you go along.

MARS is very powerful, but it’s so important to understand what it does and how it works before you start leveraging it. Otherwise you have the potential, as Christian Kleinerman says in his excellent MARS article, to really shoot yourself in the foot!

http://www.AcehAid.org