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 to a combobox or drop down list.

 Dim instance As SqlDataSourceEnumerator =      SqlDataSourceEnumerator.Instance
 Dim dt As DataTable = instance.GetDataSources()

Remember, though, this requires the assembly to have full trust. There is not a specific permission you can apply to use this otherwise.

Don’t Forget: www.acehaid.org

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 be verified with sp_helprotect,NULL,ASPNET

Finally, I just gave up and removed the ASPNET account from SQL Server in its entirety and recreated it with the necessary permissions and everything worked just fine.

Though I’m very frustrated not to have figured out what was causing the problem, I’m satisfied in knowing that there is a solution (remove & recreate.)

Here, as listed in Sushil’s fantastic blog post from late September, which listed all of the RTM changes for Query Notification, is how to set up the perms.Note that this is for IIS5 whereas in IIS6 you would use the NT Authority\NetworkService account.

All of this is run against the database that you want the perms for, not in Master.

sp_grantlogin ‘myMachineName\ASPNET’     –this gives the ASPNET account login access to SQL Server
sp_grantdbaccess ‘MyMachineName\ASPNET’, ASPNET   –this gives the login access to the database with the “nickname” ASPNET

GRANT CREATE PROCEDURE to ASPNET
GRANT CREATE QUEUE to ASPNET
GRANT CREATE SERVICE to ASPNET
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to ASPNET  –note that the schema is case sensitive!
GRANT VIEW DEFINITION to ASPNET

The above are to call Start(), to do the actual notifications, you need:

EXEC sp_addrole ‘sql_dependency_subscriber’
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO ASPNET
GRANT RECEIVE ON QueryNotificationErrorsQueue TO ASPNET
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to ASPNET
EXEC sp_addrolemember ‘sql_dependency_subscriber’, ‘ASPNET’
GRANT SELECT TO ASPNET

Don’t Forget: www.acehaid.org

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 thought Service Broker was really hard to understand. If it’s hard for a SQL Server whiz, then I’m not going to beat myself up about it anymore – until of course, I need it more directly than just through Query Notification (via ASP.Net or ADO.Net).

Don’t Forget: www.acehaid.org

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 only mystified, but a little heartbroken.

The only thing that was different was that I had run a SqlRequestNotification demo right before it. This demo listens for the notification on a separate thread.

Now playing with it some more, I see that after I end the first demo (SqlRequestNotification) and start up the SqlCacheDependency… when I change the data, I hit the event handler in the first demo. So that was still hanging around.

What I think is happening is that even when I end the demo and close the page, the file based web server is still there (I can even see it in my system tray right now). The app was still alive and the listener was still listening. So now I am going to have to dig further into SqlRequestNotification in a real scenario even though it is not something I think I will not use frequently. If it wasn’t the web app, it is likely that in this non-best practices demo code, I am not disposing enough things (though the listener’s main task is within a using statement) or something along those lines. Most importantly, it is not a likely scenario to run these two things back to back and therefore this is an unusual problem that I encountered.

I couldn’t really take the time to think this through in the session and probably wouldn’t have come to this conclusion under the small pressure of the clock ticking and those expectant faces in the audience. So I just had to go with “I promise you this works! This is the right code for you to use…” and move on to wrap up the session.

Posted from BLInk!

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

Rapid Updates

Transactions

Service Account for SQL Server

Posted from BLInk!

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 but now it seems simple. I used what I learned watching Pablo Castro’s DAT320 session that I have on my TechEd 2005 video as the basis for this. I watched him do it in the video and translated the concepts into a simpler version using VB. I had to deal with some differences in the way static variables interact with shared methods in VB that had me pretty confused (as I’m not so adept with that)  – very different than how Pablo did it in his C# demo. But I did get it working and now have a big satisfied grin on my face. And next week I get to share (show it off) with attendees of my session.

Don’t Forget: www.acehaid.org

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 you need to consider a variety of factors when choosing this number (such as network latency, how many columns are in the table, etc – bigger is not always better!) This is hardly new info at this point, but there is one other setting – zero. I am listening to Pablo Castro’s ADO.NET 2.0/SQL Server integration talk from TechEd (DAT320) and laughing because he says (this is not a direct quote – I am paraphrasing) “If you set it to zero, there will be no limit to the number of rows….[pause]…which isn’t really very good for performance .. [pause].. I don’t really know why we put it there, but…[you can practially hear him shrug his shoulders] .. we did”. Pablo can totally get away with this… the audience laughs with him and he moves on. Funny how if some other teams said something like this this, they would probably have many detractors. ADO.NET is just a happy API!

Don’t Forget: www.acehaid.org

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.NET in the SQL CLR. The article is by the ADO.NET master himself, Pablo Castro, who is the Technical Lead on the ADO.NET team.

Don’t Forget: www.acehaid.org