SQL
I found out about 15 minutes ago that SQL Server 2008 RTM version is available for download from MSDN! Congratulations to the SQL Server team!
As I watch the 3 GB download trickle slowly onto my hard drive, I'm left with a few questions at this point, like what version of NETFX will be installed when I run setup? RC0 so nicely installed the .NET 3.5 SP1 Beta Framework onto my machine, and since .NET 3.5 SP1 itself has not RTM'd, what are they going to do? Distribute the beta with the SQL Server RTM bits? Release .NET 3.5 SP1 to manufacturing? ...
In this, the eighth part in a series on the new Spatial Data types in SQL Server 2008, I'll step away from the database and do a little spatial coding using .NET.
Redistributable .NET Library
Up to this point in the series, I have demonstrated a lot of interesting (?) things that you can do with the new Spatial data types (Geometry and Geography) in SQL Server 2008. You might be thinking, "That's swell, and all, but I wish I could do some of that stuff without needing to be tethered to a database." Well, you know what? You can!
I mentioned in...
Tuesday at the NWNUG meeting, Steven Smith spoke on various ways to squeeze performance out of your ASP.NET applications. This was a fantastic talk, and gave me plenty to think about (since ASP.NET is not my forte, I only consider myself to have an intermediate skillset on this topic).
One suggestion that he made involved caching database writes. That is, instead of immediately writing logging-type information to the database for every request, which is a relatively expensive operation considering the small payload size, that you could accumulate them in a short-term cache, and then perform the write operation periodically. Fewer database...
This morning's goal was to quickly install SQL Server 2008 RC0, and then move on with some project work. Let's just say that my project work should resume by this afternoon...
In the interest of disk space, I removed an existing installation of SQL Server 2005 Developer Edition. And then the installation of 2008 RC0 began by installing the "Microsoft.NET Framework 3.5 SP1 (Beta)"... which is probably "install-smell" for me needing to pave my machine when the product finally RTM's. But, I digress...
The installation went pretty smoothly until it came time for the "System Configuration Check" that takes place after you...
The Open Geospatial Consortium's Simple Features specification, which SQL Server 2008's Geometry data type is based upon, defines standards for working with spatial data using a flat-earth (projected planar) model. Ironically, these standards don't exactly cover the intricacies of using an ellipsoidal model, which is needed to "accurately" represent the world that we live in. In other words, the OGC standards define how to work with paper maps of the world, but not globes.
Fortunately, the SQL Server team recognized that that the Geometry type is inadequate for a lot of scenarios, and implemented a second data type just for representing...
In the Part 4 and Part 5 of the series, I demonstrated some instance methods of the Geometry type that returned a new Geometry based on existing instances. In this part, I will concentrate on instance methods and properties of the Geometry type that return scalar values and Points.
STArea, STLength
Typically, your spatial data will represent something from the real world. A LineString may be the collection of points gathered from a GPS device, and together they may represent the path that you took from your home to the office. A Polygon may be the collection of points around the...
A friend of mine (name withheld, I didn't actually ask if I could blog this... ;-) asked for advice to what appears to be a simple problem until you try to implement it. Consider the following somewhat normalized table:
AccountNum
...
In the previous part of this series, I demonstrated instance methods that transformed a single Geometry type into another useful Geometry. In this post, we'll go a step further and show methods that allow two or more instances to interact with one another in order to produce a new Geometry.
For my baseline, I'll use two Polygons that overlap each other:
DECLARE @g geometry
= 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @h geometry
= 'POLYGON((30 30, 50 30, 50 50, 30 50,...
In this, the 4th post in a series (Part 1, Part 2, Part 3) on the new spatial data types in SQL Server 2008, I'll explain some of the methods that are used to transform a single Geometry instance into another useful Geometry instance. Note that I'm using Geometry for simplicity, but these techniques also work with Geography. Edit: Ok, after starting to take a hard look at Geography, I realized that A LOT of the methods that Geometry offers are not implemented in Geography. :-/ Sorry to mislead you.
Useful TipTo help me to visualize geometries as I explore the...
In the previous parts of this series (Part 1, Part 2), I introduced the Geometry and Geography data types, the various subclasses (Point, LineString, Polygon, etc), and demonstrated a little bit of the Well-Known Text (WKT) syntax. These two posts were primarily informational in nature, and didn't touch SQL Server at all. Let's change that!
Instantiating the UDT
The Geometry and Geography data types are implemented as User Defined Types (UDT) written in .NET. They are automatically installed with the server, and are available for use by any SQL Server 2008 database. For this post, I will use the Geometry type to...
In the first part of this series, I provided a somewhat verbose description of the two different types of Spatial data supported by SQL Server 2008: Geometry (flat Earth model) and Geography (ellipsoidal model). Now, let's dive in a bit deeper, and take a look at some of the specifics.
Standards
From their website, the Open Geospatial Consortium, Inc. (OGC) is described as "a non-profit, international, voluntary consensus standards organization that is leading the development of standards for geospatial and location based services." Unlike me, these folks are the real experts in the field who understand both the problems and the solutions...
Around the 1995 timeframe, two very different systems opened my eyes to the power of geospatial data, particularly how it can be used to enable an analyst to make better business decisions. At the time, I was working in IT at the hub sorting facility for an overnight freight company that specialized in heavyweight cargo. This company not only had a fleet of aircraft flying freight around the country, but also operated a large fleet of trucks (both linehaul and LTL).
Shortly after leaving that company, I was tasked with working on an e-commerce system that had some unique sales territory...
Beyond Relational
The introduction of SQLCLR in SQL Server 2005 allowed for very rich User Defined Types to be utilized. This meant that a developer could create a single object that contained multiple data points (properties) and could also perform calculations internally (methods), yet store that instance in a single field of a single row in a database table. Suddenly, any type of data could be stored and queried in the database, instead of just strings and numbers.
Fast forward to the present time, as Microsoft is celebrating the launch of SQL Server 2008 (notice that in the brilliance that is known...
The problem: A user tries to open a report from SQL Server Reporting Services using Internet Explorer. They are prompted for their username and password, despite the fact that their machine is part of an Active Directory domain. The expectation is that since the SSRS server is on the local intranet, that the logon would happen automatically. The findings: The server was incorrectly being assigned to the [external] Internet zone by Internet Explorer. By default, IE will not automatically log you into a website located in this zone as it would if the website was...
I received a support email yesterday for a system that I had written a few years ago. It seems that they were running year-end reporting, and total combined profit numbers were coming out in the quadrillions. It would have been a nice profit to report, but alas, not an accurate one.
When a number is magnitudes larger than what it should be, and there is division involved in a calculation somewhere, then that smells of floating point math issues. In this case, there was a SUM() being performed on a series of numbers, and instead of the result being a true zero,...
Nothing makes me want to pull my hair out more than writing SQL Queries that need to join two or more tables, with one of those tables containing an "Effective Date" column. Of course, the requirement is inevitably to filter out all records from this join such that the match condition is on some date value from one table and the latest "Effective Date" from the second table that pre-dates the first table's date value. /headdesk
For example, suppose that we have a table that maintains a "capacity" number that changes over time. To maintain historical accuracy, an "Effective Date" column...
While trying to install the SQL Server 2008 November CTP onto a fresh Windows XP VPC image, I came across this error:
---------------------------
Setup Failure
---------------------------
Setup has encountered the following error: Unable to find an entry point named 'MsiSetExternalUIRecord' in DLL 'msi.dll'..
Per Allen White's workaround posted on MS Connect, all that you need to do is manually install Windows Installer 3.1 before running the SQL Server setup.
You can find the Windows Installer 3.1 setup on the CTP DVD (ISO image) in the following directory:
D:\Servers\redist\Windows Installer
(Assume that drive D is your DVD-ROM)
SQL Server Integration Services (SSIS) is a huge step forward from its predecessor, Data Transformation Services (DTS). One thing that they have in common, though, is that there is no one simple method to allow a database user to execute a SSIS package on demand.
SSIS is THE centerpiece of Microsoft's ETL (Extract-Transform-Load) offering. In lay terms, SSIS is the tool that you use to import data into your SQL Server database from any number of sources, and it's a fantastic tool for doing so... so long as your environment doesn't change often... and you execute the package from Visual Studio or the...