Sushil's WebLog

Time to resurrect my blog...

2009-06-08T20:42:00Z

Its me again after 4 long year. I have decided to start blogging agian. From the last time I wrote in 2005, there have been multitude of things I have been working. I was working on JDBC driver for SQL Server in 2005-06 and then moved to Entity Framework, which had its first release as part of .Net Framework 3.5 SP1. Currently, I am working on Entity Framework 4.0 and LINQ to SQL. With the public release of Visual Studio 2010 Beta1 and Entity Framework 4.0 Beta1, I think it is an opportune time for me to start with my ramblings about Entity Framework, in general.

Hope you will find something useful here in the future,

Sushil.

SQL Server 2005 and Visual Studio 2005 RTM!

2005-10-28T22:04:00Z

Yes, we have done it! SQL Server 2005 and Visual Studio 2005 are now released to manufacturing. What this means that we are done with both the products, you can even download the official bits if you have MSDN subscription. More information can be found at http://msdn.microsoft.com/subscriptions/ . The official launch of these products will happen on Nov. 7 2005 in San Francisco. You can find more information about the Launch at http://msdn.microsoft.com/Launch2005/

Looking forward to your feedback and comments.

SqlDependency changes for RTM

2005-09-27T19:49:00Z

I just posted a blog on our team webpage on the changes in new SqlDependency feature from Beta 2 to RTM. There have been a lot of changes since we shipped in Beta 2. Do let us know what you think of the new changes. We are looking forward to hear about this.

See you at PDC05!

2005-09-13T00:27:00Z

I am here at PDC05, LA represeting the SQL Server team as part of staffing the product pavillion. There is just one word to sum up the level of excitment/ enthusiasm that I feel in the air - WOW! And I have been told that its just starting, with more people coming in for the main event that starts tomorrow, it going to get even better. Imagine that!

This being my first PDC, I am fired up to meet customers and partners. I just wanted to share some of the DataWorks (my team) activities at PDC:

As part of the staff duties, here is the schedule of where you can find me at PDC.
Pablo Castro (ADO.Net, technical lead) will be giving an advance talk on ADO.Net. Here is a brief summary of his talk. I did get a sneak peak to his talk and it definetly gives lot of good information. The talk focusses on how the some of the new features in 2.0 can be used in "real" apps when it comes to large data that would make it more performant and scalable.

If you need more information, just drop me a line or see you in LA!

ConnectionStringBuilder in ADO.Net v2.0 - a Primer

2005-03-30T23:22:00Z

Do you want to know more about ConnectionStringBuilder feature in ADO.Net v2.0? If yes, then look up our team site where I posted a short introduction.

Our team blog is Online

2005-03-20T18:49:00Z

The team in which I work is called DataWorks. I am excited to announce that we as a team have our own Web Log at http://blogs.msdn.com/dataaccess. Check out the message from our PUM (Product Unit Manager) - Alyssa. Via this blog, we look forward to get feedback from you for the components we release. Check it out!

Getting Notifications to Work.

2005-01-31T17:44:00Z

With good things said about Notification in my previous blog, I will like to take a step further to walk you through some common issues in setting up this feature. We would need to undestand how notifications internally work to track some of these issues. Lets start with the SqlDependency object. When we create a SqlDependency object we are creating a listener on the client to track notifications sent by the server. There are two types of protocol options that SqlDependency supports – HTTP and TCP when creating a listener. The HTTP listener uses the HTTP.SYS functionality to create a listener. By default when no protocol option is specified, we try to create an HttpListener; if that for some reason fails, we create a TCPListener. When a change occurs, the server then dispatches notification messages via a .Net Procedure to the client listener. Now, let’s go over some common issues.

My Application doesn’t get Notification messages at all:

There could be many reasons why we could end up with this. Here are some reasons.

Is CLR enabled on SQL Server?

Cause: Since the code on the SQL server that dispatches notification messaged to the client is a .Net Procedure, you will have to enable CLR on Server. We are working on not requiring this restriction.:)

Solution: Here is way to do this for now,

EXEC sp_configure 'show advanced options', '1'
go
RECONFIGURE
go
EXEC sp_configure 'clr enabled', 1
go
RECONFIGURE

Does the SQL user have permissions on the Queue and/or the Service

Cause: Since this feature uses the SQL Server Service Broker (aka SSB) infrastructure to get notification, the user has to have the permissions on the Notification Service.

Solution: To grant the permission to user ‘Willy’ on the Service and queue use:

GRANT SEND on service::SqlQueryNotificationService to Willy

GRAND RECEIVE on SqlQueryNotificationService_DefaultQueue to Willy

Also the user needs permission to subscribe to notification. To do this use:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO Willy

Is it an XPSP2 machine and/or you using a firewall to block ports?

Cause: As said above, Notifications are dispatched from the server to the client via listener that sits on the client. With XPSP2 we have firewall enabled by default. Since the firewall will block any message sent to your TCP/HTTP ports, this may not generate Notification event.

Solution: Make sure these are open for the application.

I get an instantaneous notification telling that the Subscription failed.

Cause: There are set of requirements that are placed on the queries for which are notifiable . Basically, the restrictions are quite similar to restrictions for Indexed Views in SQL Server 2000. More information on these requirements can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_9jnb.asp

Solution: Queries that pass the above requirements can only be used.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Guerrilla SQL Server 2005 class

2004-12-17T17:44:00Z

I happened to go to one of the class from a mentoring company. It was presented by Bob Beauchemin-Niels Berglund-Dan Sullivan. Boy! they were great!
I was just in time for the ADO.Net presentation that Bob made. He talked about top 3 features that he liked in ADO.Net
1. Provider Factory - He talked at great lengths about how easy it was to use provider factory to write generic code
2. Meta Data/GetSchema - The easy way to get meta data Schema. More information in his article in MSDN - (link)
3. Tracing - Yes, we shipped a way for you to get trace output from System.Data internal code. Now this is the feature that was not that well documented in Beta docs, but this article (written by bob, grin) does a very good job explaining the internals and how to get it working.

It was great to see these guys present. They were awesome and great! (Oh, did I already say that :))

Notification Support in SqlClient Managed Provider

2004-12-07T17:47:00Z

After a brief hiatus, let me start with blogging the new Notification support in SqlClient MP that is introduced in Whidbey. There are scenarios in which an application would store a cache of data obtain from a DB Server and then re-query from the same cache to save round-trips to the server (for better performance). Typically, the app would want some mechanism to be notified when this very cache was changed by some one. In short, this feature allows you to monitor a specified result-set (collection on table rows, as defined by your select statement.) and then notify you when something is changed in the monitored result-set.

In this blog, I will just post a sample illustrating on how easy it is to set a result-set for Notification. In the upcoming blogs, I will get into more details with some gotchas, tips and tricks with Notifications. Lets see how simple its to get this working. Lets say way, create a table test with following TSQL

create table test (i int)
go
insert into test values (1)
insert into test values (2)
insert into test values (3)
insert into test values (4)
go

The following sample shows how a console application that reads data from the 'test' table into its cache and will get notified if some one has made changes to this cache on the server.

using System;

using System.Data;

using System.Data.SqlClient;

class QuikExe

{

string connectionstring = "Put you connection string here";

public void DoDependency()

{

using (SqlConnection conn = new SqlConnection (connectionstring))

{

conn.Open();

Console.WriteLine("Connection Opened...");

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "Select i from dbo.test";

//Notification specific code

SqlDependency dep = new SqlDependency (cmd);

dep.OnChanged += new OnChangedEventHandler(TestEvent);

SqlDataReader r = cmd.ExecuteReader();

//Read the data here and close the reader

r.Close();

Console.WriteLine("DataReader Read...");

}

void TestEvent(Object o,SqlNotificationEventArgs args)

{

Console.WriteLine("Event Recd");

Console.WriteLine("Info:" + args.Info );

Console.WriteLine("Source:"+args.Source );

Console.WriteLine("Type:"+args.Type );

}

public static void Main()

{

QuikExe q = new QuikExe();

q.DoDependency();

Console.WriteLine("Wait for Notification Event...");

Console.Read();

}

Run the above application and you will see the following output
Connection Opened...
DataReader Read...
Wait for Notification Event...

To verify that we indeed get a notification when the test table is change. Now invalidate the cache that we read using the following T-SQL statement (using any client tools-Workbench or another console Application :))
insert into test values (5)

Now, you will see the following Notification message from the above application
Event Recd
Info:Insert
Source:Data
Type:Change

Great! All we did here was to create a SqlDependency object and associate the Command to it before executing.
SqlDependency dep = new SqlDependency (cmd);

Also, to get notified we added a call-back event.
dep.OnChange += new OnChangeEventHandler(TestEvent);

The SqlNotificationEventArgs in turn has three properties which are enums:
1. Info - Gives information on what caused the notification.
2. Source - Gives the source of this notification.
3. Type - Type of notification.
Since in our case an Insert statement caused a Change in Data. The args properties are set as Info=Insert;Source=Data;Type=Change;

Note: Since the supporting functionality that makes this happen (SSB) is only available in SQL Server 2005 (Yukon), the above code will only work with SQL Server 2005.

So how did all this happen? In SQL Server 2005, we have a SSB service running in the MSDB database (called the SqlQueryNotificationService). When a command associated with a SqlDependency executes; it makes a request to this service for being notified if there occurred any change in the obtained result set. When a change occurs, the SSB service then puts a notification message in a specified Queue. The server then send the message from the queue to the client. The client then calls the call-back Event appropriately.

Tips and tricks to be aware while using SqlDependency is coming soon,
Stay posted.

Enumerating SQL Servers

2004-10-14T18:56:00Z

Continuing with the discussion on enumeration, I will go over ways to enumerate active SQL servers on the network in this blog. Pre-Whidbey, to get list of active servers on the network, we could have done Inter-Op calls to server enumeration APIs in OleDb. If we wanted to write only managed code,we could have broad-casted a carefully crafted UDP packet to the network on port 1434 and waited for response from active SQL Servers and then list them.

In Whidbey, there is an API provided to do the server enumeration. It allows to list active servers on the network. Here is the sample code:

using System.Data;

using System.Data.Sql;

using System;

public class Repro

{

public static int Main(string[] args)

{

SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;

DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();

foreach (DataRow row in datatable1.Rows)

{

Console.WriteLine("****************************************");

Console.WriteLine("Server Name:"+row["ServerName"]);

Console.WriteLine("Instance Name:"+row["InstanceName"]);

Console.WriteLine("Is Clustered:"+row["IsClustered"]);

Console.WriteLine("Version:"+row["Version"]);

Console.WriteLine("****************************************");

}

return 1;

}

Output:
****************************************
Server Name:TESTSERVER-D1
Instance Name:
Is Clustered:No
Version:8.00.194
****************************************
****************************************
Server Name:TESTSERVER01
Instance Name:INST2
Is Clustered:No
Version:8.00.194
****************************************
****************************************
Server Name:TESTSERVER3
Instance Name:
Is Clustered:No
Version:8.00.194
****************************************

What did we do here? SqlDataSourceEnumerator has a public static property that returns an instance of SqlDataSourceEnumerator . The GetDataSources() method on the Enumerator returns a datatable that returns list of active Servers on the network. Following information about the server is included in the table:
1.Server Name: Name of the server.
2.Instance Name: Name of the SQL Server instance. If its a default instance then this is DBNull.
3.Is Clustered: returns Yes, if Server is clustered, else No.
4.Version: returns the full version number of the server listed.

Q:Why do we have a static property Instance on SqlDataSourceEnumerator ?
A:The Instance property is a result of the factorization of the class. The base class, DbDataSourceEnumerator, defines the abstract GetDataSources method. This way, all derived enumerators have to implement it. This implies the method has to be instance method and cannot be static. To make it easy to write code we just added a static property on SqlDataSourceEnumerator that gives its instance.

Q:Why are some values for InstanceName, IsClustered and Version DBNull?
A:The Enumeration API that SQL Client Managed Provider calls, first broadcasts the 'special' packet on port 1434 and waits for a timeout. The replies that it receives with-in the timeout contain all the required information to populate the DataTable. In addition to this,the API looks at the Active Directory (AD) to list the other servers that did not reply for the broadcast packet. There is no way to know the values for InstanceName, IsClustered and Version when listed via AD and hence they are DBNull.

Q:Why are all servers on the network not listed in the list?
A:Since we wait for a time-out it is not guaranteed that we return all the active servers on the network. In fact, the list is most likely to give a different list depending on the network I/O, server performance and other time-depending constraints.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights