Panopea Consulting - iMIS Blog

Using Full Text Indexing with SQL Express

If you have a db that is using Full Text Indexing but you're on SQL Express instead of the full blown version of SQL 2005, you'll find that the Express-version Mgmt Studio version that doesn't have the same options for setting up full text searching. But I found this great posting on how to work with the Express Studio to set up full text indexing.

Use SQL Express Mgmt Studio with MSDE

A very cool thing about SQL 2005 for many iMIS customers using MSDE 2000 who have been using the Whitebear MSDE Manager or whatever, now you can get a real tool -- SQL Express Management Studio is available free from Microsoft's website. It's much easier and much more powerful to work with.

What is even cooler is that you can start using the Management tool right now with your MSDE 2000 db even before you get around to upgrading. (Note: You must be on iMIS 10.6 or higher to have your database using SQL 2005 or SQL Express. Using the tools works with any version of iMIS using MSDE 2000, but running the db in the db engine is not ok until 10.6.)

One thing I came across is that there is a small registry edit that is necessary to make if you try to use the SSE Mgmt Studio to back up an MSDE database. Here's a link to get more information on it.

To download the Management Studio, go the Microsoft Download Center or use this link.

SQL Server Licensing - Maybe not what you thought

I was at a SQL 2005 training class recently and learned something new about licensing for SQL Server. Since it's not really my baliwick to be the one obtaining the SQL Server licensing for iMIS clients, I've never dug too deepling into the licensing rules. But I have heard people say that you only need 1 CAL for your web server to connect to your SQL Server, i.e., if CF_CUST connects to the server, that's just one connection.

NO, NO, NO according to the trainer at this class, a former Microsoft employee who has trained SQL Server classes for years. You need to have a CAL for EVERY device connecting to your db, which means anyone using e-Series should certainly be getting Processor licensing if they want to be Microsoft legal. I wonder how many organizations are following this rule.

From Microsoft's licensing document for SQL Server 2005:

Sometimes organizations develop network scenarios that use various forms of hardware and/or software that reduce the number of devices or users that directly access or use the software on a particular server, often called multiplexing or pooling hardware or software. Use of such multiplexing or pooling hardware and/or software does not reduce the number of CALs required to access or use SQL Server software. A CAL is required for each distinct device or user that is connected to the multiplexing or pooling software or hardware front end.

This is true no matter how many tiers of hardware or software exist between the SQL Server and the client devices that ultimately use its data, services or functionality.

If it makes you feel any better, if you're buying multiple core processors, Microsoft is counting each processor as one, regardless of how many cores are on the chip.

Calculating Age & the DATEDIFF Function

I found today that a client of mine has been calculating age based on this calcuation:
    datediff(year, BIRTH_DATE, getdate())

Because of how the DATEDIFF function works, this is a crude way to calculate age. The problem with DATEDIFF is that it counts "1" if the two years being compared are different by one year. For example, datediff(year, '12/31/2005', '1/1/2006') gives an answer of 1, but obviously those dates are not a year apart as we would think of it.

So, if you use the calculation of datediff(year, BIRTH_DATE, getdate()) to calculate age, you've prematurely advanced the age of anyone whose birthday has not yet occurred in the current calendar year. That's very important to avoid if you are calculating age for insurance or other age-based programs your organization might be running.

Doing a quick search on the internet, I found the following formula on this page: 

DECLARE @birthdate SMALLDATETIME, @endDate SMALLDATETIME
SET @birthdate = '19740224'
SET @endDate = GETDATE()

SELECT DATEDIFF
(
     YEAR,
     @birthdate,
     @endDate
) - CASE
     WHEN 100 * MONTH(@endDate) + DAY(@endDate)
     < 100 * MONTH(@birthdate) + DAY(@birthdate)
     THEN 1 ELSE 0 END

As the article suggests, you might want to make this into a function if you are on SQL Server/MSDE 2000 or higher.

Be sure to consider this concept of how the DATEDIFF function works anytime you are using it, so you don't create a similar "overcounting."

I think there was a discussion of this on NiUG sometime ago but googling was easier than searching NiUG's archives.

Support for SQL Server 2005

ASI also made this announcement regarding SQL Server 2005 support:

As a general practice, ASI tests and supports new 3rd party major versions ( including Service Packs) with the next version of iMIS that is scheduled to be released after the 3rd party software version is available to the public. Because the general availability date for Microsoft's SQL Server 2005 database platform occurred so close to the release date for iMIS 10.6, insufficient time remains in the iMIS production schedule to ensure full compatibility with SQL Server 2005. For this reason, the initial release of iMIS 10.6 will not provide full support for SQL Server 2005.

However, knowing the importance of SQL Server 2005 to many of our customers, ASI will continue to evaluate SQL Server 2005 compatibility with iMIS 10.6 and may announce full support for it in the near future. In any event, the next production release of iMIS after 10.6 will provide full support for the Microsoft SQL 2005 Server database platform.