Well, thanks to Dave Sell (etal.) at Microsoft I was able to get the latest service pack for SQL Server 2005 installed. Unfortunately, it took almost a month to get this resolved by some hard-working Microsoft engineers. The error logs were only partially helpful. The web sites we were lead to were not.
March 2009 Archives
I’ve spent the last twenty years or so warning about the problems of SELECT * in queries but Peter Blackburn recently reminded me that a SELECT * in a View is worse. I have an article in the works that explains why in some detail, but I wanted to make developers using Visual Studio or SQL Server Management Studio (SSMS) know that if they have a SELECT * imbedded in a View, they won’t find it—at least not with the VS or SSMS Query editor (QE). The problem is, the QE automatically converts any ambiguous reference like SELECT * into the actual columns when it parses and displays the query. Unless you save this refactored SQL, the original ambiguous SQL remains in place to cause havoc later—and it will.
So, how do you find out if a View has an imbedded SELECT *?
I had a question from our Reporting Services book website that lead me to create a sample application to illustrate how to deal with filter arguments. IMHO, the mechanism used by the Report Designer is still not that great as it requires too much (non-VB-like) type coercion. That is, one constantly has to force the type of the TextBox values as well as the type of the left and right-hand side of expressions to match instead of having the report processor’s RDL interpreter just figure it out. I have attached a ZIP file that has the project used to illustrate several filtering techniques. Of course, it makes a lot of sense to filter the initial DataSet but you might want to give the user the ability to filter the fetched rowset so the query does not (necessarily) need to re-execute.
Sample Report (BI) Application CheckingDates
I just replaced my copper DSL with FIOS. This meant getting another IP address but it also means I can serve those who want to pull down my content at 5 MBS. Let me know if there are issues.
I’m giving a webinar on SQL Server Stored Procedures Thursday morning (March 12th) at 1 PM Eastern time. This 70-minute session discusses how to write efficient stored procedures and call them from ADO.NET.
The materials and précis can be downloaded from here… http://www.pbconferences.com/8Y/9W
So, I asked Microsoft to explain why the ConnectionString dialog exposed by the SqlClient .NET Data Provider no longer has the Connection Reset key. They say it was removed. Has anyone heard why? IMHO, this exacerbates the problem with the Close method. That is, when you use the SqlConnection.Close method in ADO.NET, the connection state is maintained until the next time the connection is used or it times out (4-8 minutes later). I think this is dumb. Why not release the connection state on close? Sure, it’s going to take another round-trip to do so but that can be done in the background asynchronously so the client need not care or even notice that the connection they just closed is being cleaned up and the state they left behind is being flushed. It’s like having a hotel that leaves the dirty sheets on the bed and wet towels on the floor until the next guest tries to get into the room. That’s when the open must be delayed to set the room (or the connection) back to the initial state.
Leaving the state behind means that anything allocated to the Connection is left lying around in memory or on disk getting old and useless. Since they killed the Connection Reset option, it can’t even be used by subsequent connections. I certainly hope the default (and now only) state is “True” (clean on open).
Ideas I think the SQL Server team could use to build some future products. These most have to do with SQL Server Management Studio tools improvements that would be leveraged (in time) into Visual Studio.
Better organization of objects like Stored Procedures, Views, Tables into logical (user-defined) groups: Accounting, Finance, Inventory, Personnel. Some SPs or objects might be in more than one group.
This is a more complex task. While NorthWind has relatively few objects, even Adventureworks (AW) has almost too many tables and objects to manage graphically without some degree of organization. AW deals with this to some extent by clever object naming, but this only applies to tables. When a database has 200 stored procedures it would be great to have these organized by class but consider that some objects may be used in more than one “class”.
During the MVP Summit I had an opportunity to interact with members of the SQL Client team (in the SQL Server group). While I can’t talk about their future plans, I can talk about the suggestions I made:
Okay, I lied, here are several more items for future versions of Visual Studio. The problems I outline here (and elsewhere) might have been mentioned before, but finding the right team at Microsoft to take ownership of these issues is a challenge. We’re constantly told by one team or another that some other sub-section of the “Visual Basic” team handles this issue or that.