For those that read InformationWeek, you know that articles regarding the auto industry appear regularly. In this week's issue, five auto industry executives are profiled including International Truck & Engine's VP of IT. I was suprised when he used my group (Supply Chain) for one of his examples.
The article can be found at http://www.informationweek.com/story/showArticle.jhtml?articleID=160401450&pgno=3.
Friday, April 08, 2005
Tuesday, March 29, 2005
Kudos
This post is a bit off topic from the usual technical nature of this site but all too often those that deserve praise for a job well done go unnoticed.
This past weekend my wife, in-laws, and I were travelling on a Sunday afternoon road-trip between Crawfordsville, Indiana and Greencastle, Indiana. As we returned to Crawfordsville around 4:00 PM, we were caught off-guard as a Crawfordsville Police officer drove south on US 231 and pointed and indicated to the motorists heading northbound to pull over and stay off the road. Within a minute after the officer passed, a green SUV passed at an extremely high speed and was being pursued by several Indiana State Police squad cars.
After the chase passed, we continued travelling toward Crawfordsville and witnessed another Crawfordsville trooper pulling in some spike strips. Not too far ahead, the police had the SUV stopped and surrounded.
The coordinated effort between the Crawfordsville and Indiana State Police was able to stop the vehicle and prevent anyone from being injured during the chase.
According to the linked article the driver was travelling at speeds of up to 110 mph. At that speed, these agencies had approximately six minutes to coordinate and execute a plan for stopping the driver before she reached Crawfordsville!
As a traveller in the vicinity of this pursuit I must commend all of the departments that were involved. It was their training and quick thinking that prevented anyone from being injured this Easter Sunday.
On behalf of my family, I sincerely thank all of the officers involved for their service.
This past weekend my wife, in-laws, and I were travelling on a Sunday afternoon road-trip between Crawfordsville, Indiana and Greencastle, Indiana. As we returned to Crawfordsville around 4:00 PM, we were caught off-guard as a Crawfordsville Police officer drove south on US 231 and pointed and indicated to the motorists heading northbound to pull over and stay off the road. Within a minute after the officer passed, a green SUV passed at an extremely high speed and was being pursued by several Indiana State Police squad cars.
After the chase passed, we continued travelling toward Crawfordsville and witnessed another Crawfordsville trooper pulling in some spike strips. Not too far ahead, the police had the SUV stopped and surrounded.
The coordinated effort between the Crawfordsville and Indiana State Police was able to stop the vehicle and prevent anyone from being injured during the chase.
According to the linked article the driver was travelling at speeds of up to 110 mph. At that speed, these agencies had approximately six minutes to coordinate and execute a plan for stopping the driver before she reached Crawfordsville!
As a traveller in the vicinity of this pursuit I must commend all of the departments that were involved. It was their training and quick thinking that prevented anyone from being injured this Easter Sunday.
On behalf of my family, I sincerely thank all of the officers involved for their service.
Thursday, March 24, 2005
Oracle, DB2, and ASP.NET
In my ASP.NET applications, I typically use SQL Server 2000 but lately I have had the need to connect to Oracle and DB2. The Oracle and DB2 providers do not work with ASP.NET out of the box so I thought I'd share some of my experiences along with best practices for resolving the issues. Documentation for resolving these issues seems to be lacking, particularly in the area of DB2.
As you may know, ASP.NET applications execute in the user context of the ASPNET account on the server computer. By default, this account does not have access to the installation folders of the two providers. This lack of permissions will cause an ASP.NET application attempting to utilize either provider to incorrectly report that the corresponding provider has not been installed.
One solution to this is to make the ASPNET account a member of the server Administrators group. This approach should be avoided. Instead, grant the ASPNET account Read & Execute permission on the folder where the provider is installed. These permissions should be inherited to lower levels of the directory structure. Refer to Table 1 for the standard installation paths for the two providers. Note that your installation may vary depending on the version installed on your server.
The DB2 Installer does simplify this process a bit. A group named DB2USERS is created during installation and the appropriate permissions have been granted to the group. To give the ASPNET account the appropriate access to the folder, add it to the DB2USERS group. This will require restarting IIS which can be easily accomplished by running IISRESET from the run dialog box.
My server did not fall into this category and the registry entries were referencing the correct path but I thought I should include it for completeness.
If the new provider still doesn't work through ASP.NET, determine if connections to the database server can be made using the provided software (SQL+ for example) and query Google for your symptoms. As a personal preference, I typically check Google Groups before trying standard Google.
As you may know, ASP.NET applications execute in the user context of the ASPNET account on the server computer. By default, this account does not have access to the installation folders of the two providers. This lack of permissions will cause an ASP.NET application attempting to utilize either provider to incorrectly report that the corresponding provider has not been installed.
General Issues
| Client | Folder |
|---|---|
| Oracle | C:\Orant\ |
| DB2 | C:\Program Files\IBM\SQLLIB |
The DB2 Installer does simplify this process a bit. A group named DB2USERS is created during installation and the appropriate permissions have been granted to the group. To give the ASPNET account the appropriate access to the folder, add it to the DB2USERS group. This will require restarting IIS which can be easily accomplished by running IISRESET from the run dialog box.
Oracle Specific Issues
This issue seems to be specific to the Oracle 8i client and the MDAC (Microsoft ODBC driver and the Microsoft Oracle Provider) and is addressed in http://support.microsoft.com/default.aspx?scid=kb;en-us;264012. Essentially, a useless error message is displayed: SQL State: "NA000What is causing this is that some registry entries are incorrect or missing. Follow the instructions in the KB article linked above to solve the problem. Of course, the obligatory warning about incorrect modification of the registry can cause the computer to cease functioning applies.
Native error code: 0
Driver Message: [Microsoft][ODBC Driver for Oracle][Oracle]
Do you need any suggestions to avoid the error?"
DB2 Specific Issues
In trying to resolve the connectivity issue with DB2, a DB2 DBA suggested examining the registry to verify that the entries in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INT for DB2Connect refer to the appropriate folder. The DBA said that the issue seems to be limited to systems where the client software was upgraded from version 7 to version 8.My server did not fall into this category and the registry entries were referencing the correct path but I thought I should include it for completeness.
Summary
Whenever a new database connectivity provider is installed and needs to be used by ASP.NET, permissions on the installation folder should be checked to ensure that the ASPNET account can access the files. For security reasons, never set the ASPNET account as a member of the Administrators group although doing so would typically solve the problem.If the new provider still doesn't work through ASP.NET, determine if connections to the database server can be made using the provided software (SQL+ for example) and query Google for your symptoms. As a personal preference, I typically check Google Groups before trying standard Google.
Tuesday, March 08, 2005
SQL Server 2005 Book Reviews
Now that my SQL Server 2005 presentation is over, I have time to write reviews of the books I used to gather my material! My two primary resources were:
Both of these books were fabulous, particularly considering the unpredictable nature of beta software. The books provide excellent preliminary documentation for the new features of SQL Server 2005 including the .NET Runtime Host extensions and Broker Services.
In addition to being well written and full of examples, these books are a great compliment of each other. The Addison-Wesley book shines in the area of defining the syntax of the new T-SQL extensions and providing examples. The MS Press book does not lay out the syntax but provides better examples and often includes a diagram to accompany a discussion.
As is expected with any literature based on a beta product, there are a few issues with the books. Foremost, the books make reference of the System.Data.SqlServer namespace. As of December of 2004, this namespace has been removed and its functionality has been moved back into System.Data.SqlClient. Additionally, The MS Press book makes use of a ":" syntax for creating an object based off of a member of an assembly. For consistency, the ":" syntax has been replaced with a "." syntax.
If I had to suggest one of these books, my vote would immediately go to the MS Press book. The Addison-Wesley book tends to be wordy and drawn out. The MS press book is half the length of the Addison-Wesley book and covers the same information almost in the same level of detail. The MS Press book is also an easy read and its use of diagrams makes scanning for information simple. For anyone serious about getting involved with SQL Server 2005 development though, I would recommend reading these books together to get a better view of the overall picture.
- A First Look At SQL Server 2005 for Developers from Addison Wesley
- Introducing Microsoft SQL Server 2005 for Developers from MS Press
Both of these books were fabulous, particularly considering the unpredictable nature of beta software. The books provide excellent preliminary documentation for the new features of SQL Server 2005 including the .NET Runtime Host extensions and Broker Services.
In addition to being well written and full of examples, these books are a great compliment of each other. The Addison-Wesley book shines in the area of defining the syntax of the new T-SQL extensions and providing examples. The MS Press book does not lay out the syntax but provides better examples and often includes a diagram to accompany a discussion.
As is expected with any literature based on a beta product, there are a few issues with the books. Foremost, the books make reference of the System.Data.SqlServer namespace. As of December of 2004, this namespace has been removed and its functionality has been moved back into System.Data.SqlClient. Additionally, The MS Press book makes use of a ":" syntax for creating an object based off of a member of an assembly. For consistency, the ":" syntax has been replaced with a "." syntax.
If I had to suggest one of these books, my vote would immediately go to the MS Press book. The Addison-Wesley book tends to be wordy and drawn out. The MS press book is half the length of the Addison-Wesley book and covers the same information almost in the same level of detail. The MS Press book is also an easy read and its use of diagrams makes scanning for information simple. For anyone serious about getting involved with SQL Server 2005 development though, I would recommend reading these books together to get a better view of the overall picture.
Friday, February 04, 2005
<SUBJECT>At a Loss</SUBJECT>
As I indirectly mentioned in my previous post, I will be giving a presentation on SQL Server 2005 at the March .NUFW meeting. I have been spending a great deal of time preparing for this presentation and have most recently been studying the new XML capabilities including extensions to the FOR XML clause, the inclusion of the XML data type, and the native support for XQuery.
All of these enhancements seem exciting but I am at a loss as to why much of this is being natively supported in an RDBMS, especially one that allows for embedded CLR assemblies. Don't get me wrong, I think that XML is a great technology and have used it many times but like everything else, it is a tool and needs to be used appropriately.
I fully understand wanting to manipulate XML as close to the database as possible to reduce the amount of network traffic typically required to build a dynamic XML document. As I mentioned above, SQL Server 2005 supports embedded CLR assemblies. One of the new features includes in version 2.0 of the .NET Framework is the SqlServer namespace that provides classes for querying and manipulating data against the context connection. This provides an easy and familiar mechanism for building and parsing XML documents inside of the database because the classes defined in the System.Xml namespace are accessible!
I even understand the need for an XML data type so that XML documents may be passed into or returned by stored procedures or user defined functions for parsing or building, respectively. What I do not understand is storing XML in a relational database!
All of the arguments I have read or heard for storing XML in a relational database fall into a couple of categories:
As for the second argument, I will agree that XML does allow for an easy mechanism for persisting and recovering object related data. This is also known as laziness! I would even argue that this approach is lazy even when the data is "guaranteed" (is anything ever certain?) not to change.
I have never seen an XML document that wasn't able to be easily represented in a relational format. It is true that many documents require many tables but the point is that it can still be done. In many aspects, it is also more efficient to store data values individually spread across multiple tables than it is to store a single XML document (or fragment) in a single column.
Parsing the data values into individual columns across an appropriate number of tables provides many advantages to storing an entire XML document in a single column.
All of these enhancements seem exciting but I am at a loss as to why much of this is being natively supported in an RDBMS, especially one that allows for embedded CLR assemblies. Don't get me wrong, I think that XML is a great technology and have used it many times but like everything else, it is a tool and needs to be used appropriately.
I fully understand wanting to manipulate XML as close to the database as possible to reduce the amount of network traffic typically required to build a dynamic XML document. As I mentioned above, SQL Server 2005 supports embedded CLR assemblies. One of the new features includes in version 2.0 of the .NET Framework is the SqlServer namespace that provides classes for querying and manipulating data against the context connection. This provides an easy and familiar mechanism for building and parsing XML documents inside of the database because the classes defined in the System.Xml namespace are accessible!
I even understand the need for an XML data type so that XML documents may be passed into or returned by stored procedures or user defined functions for parsing or building, respectively. What I do not understand is storing XML in a relational database!
All of the arguments I have read or heard for storing XML in a relational database fall into a couple of categories:
- XML is universal because it uses UNICODE.
- XML allows for native mapping of properties to objects, thus, it provides a simple mechanism for persisting and recovering the data related to an object.
As for the second argument, I will agree that XML does allow for an easy mechanism for persisting and recovering object related data. This is also known as laziness! I would even argue that this approach is lazy even when the data is "guaranteed" (is anything ever certain?) not to change.
I have never seen an XML document that wasn't able to be easily represented in a relational format. It is true that many documents require many tables but the point is that it can still be done. In many aspects, it is also more efficient to store data values individually spread across multiple tables than it is to store a single XML document (or fragment) in a single column.
Parsing the data values into individual columns across an appropriate number of tables provides many advantages to storing an entire XML document in a single column.
- When storing raw XML, a great deal of space is wasted just to store tags and attribute names. Depending on the structure of the XML, this can quickly add up since it will be extended across all of the rows in the table storing the XML.
- What if a single value needs to be changed? How about adding a new Item? If the XML document is large, this can be an expensive operation since the entire column value will need to be replaced. (It should be noted here that SQL Server 2005 does support a proprietary XML Data Manipulation Language (DML) but the XQuery specification does not currently support this functionality so it is likely to change in future versions.)
- What if a different view of the data is needed? Suppose the XML represents an article and only the title is needed? The entire document would need to be read and parsed to extract a single value. The title itself could be broken out into its own column but then other problems may exist:
- Multiple copies of the same data if the title is left in the XML. This is wasting space and adding complexity because the values need to be kept in sync.
- The title could be removed from the XML and stored in its own column. This is eliminating the purpose of storing the XML in the database in the first place because the title will either need to be added back into the XML for delivery to the client or two queries will need to be run!
- Another gripe about storing raw XML in a database is that it is difficult to query against. An example is if the XML column stores documents that describe books and a user wants to find all of the books written by "Bob", either every column needs to be returned and searched using XPath or XQuery (SQL Server 2005 provides native support for XQuery but the syntax is an awkward combination of T-SQL and FLWOR)
- The XML specification does not require values to be strongly typed. Strongly typed data ensures consistency. Storing the values in individual columns that are appropriately typed will add an extra layer of protection to the data.
- Finally, a "typical" XML document does not represent a scalar value but rather, a complex type.
Subscribe to:
Posts (Atom)
