Thursday, December 13, 2012

Installing on SQL Server 2000

If you need to install Acinta Shortcut on a SQL Server 2000 then there are a couple of changes you need to make. They are briefly described below.

Views

  1. "Acinta_Split" not supported. You'll see an error like this: "Syntax error near a". Acinta_Split is used to decipher "Tælleværker" in C5. Table-valued functions are not supported by SQL Server 2000. You have to remove/comment out all SELECT statements using this function. As a result, you'll not be able to see the results of "Tælleværker".
  2. Top N not supported. A couple of places contain sub-queries in the form:
    SELECT TOP 1 <Expr>
    .....
    ORDER BY ....

    This must be rewritten to:
    SELECT MAX(<Expr>)
    .....

    These functions were introduced in SQL Server 2005.
    How to solve: Replace TOP 1 with MAX( ) and remove the ORDER BY clause.
  3. Row_number() and RANK not supported. In one place these functions are used. There is no simple way to express this function in SQL Server 2000 so the simplest solution is to replace the whole expression by "NULL". If you need this function you'll have to rewrite it yourself. However, it is rarely used so don't worry.

Indexes

Indexes in SQL Server 2000 don't allow the INCLUDE columns. You have to rewrite the indexes and put all needed columns in the main index part. Remember there's a limit of 16 fields per index.

In addition, the "WITH" sub-clause specifying index options, is not supported and must be removed.

For example you have to rewrite this index:
CREATE NONCLUSTERED INDEX [ACINTA_IX_ProjTrans] ON [dbo].[PROPOST]
(
[DATASET] ASC,
[DATO] ASC,
[LXBENUMMER] ASC,
[NUMMER] ASC,
[ART] ASC,
[VARENUMMER] ASC,
[LOKATION] ASC,
[MEDARBEJDER] ASC,
[ENHED] ASC,
[BUDGETKODE] ASC,
[TRANSAKTION] ASC,
[VALUTA] ASC,
[PROTYPE] ASC,
[KOPIERET] ASC,
[OVERFXRTLXN] ASC
)
INCLUDE ( [ANTAL],
[KOSTBELXB],
[TEKST],
[BELXBVAL],
[BELXBSTD],
[DRIFTSFXRTANTAL],
[DRIFTSFXRTBELXBVAL],
[DRIFTSFXRTBELXBSTD],
[KURS],
[DRIFTFXRTLXNTILLXGVAL],
[DRIFTFXRTLXNTILLXGSTD]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

to something like this:
CREATE NONCLUSTERED INDEX [ACINTA_IX_ProjTrans] ON [dbo].[PROPOST]
(
[DATASET] ASC,
[DATO] ASC,
[NUMMER] ASC,
[ART] ASC,
[VARENUMMER] ASC,
[LOKATION] ASC,
[MEDARBEJDER] ASC,
[BUDGETKODE] ASC,
[TRANSAKTION] ASC,
[VALUTA] ASC,
[PROTYPE] ASC,
[OVERFXRTLXN] ASC,
[ANTAL],
[KOSTBELXB],
) ON [PRIMARY]
GO

Other considerations

How do I know which SQL Server version it is?

In order til check the version of SQL Server you need to open SQL Server Management Studio (or Enterprise Manager for older versions) and connect to the server.
The internal version number can be read in the red box: 10.
The internal version numbers have this meaning:
8: SQL Server 2000
9: SQL Server 2005
10: SQL Server 2008
11: SQL Server 2012

Beware of "Compatibility mode"!

SQL Server provides a feature called "Compatibility mode" that enables a newer version of SQL Server to pretend it is an older version. The background for this obscurity is of course to allow older applications to work seamless with newer versions of SQL Server that they were not designed for.

Thus, what may at first sight look like e.g. SQL Server 2008 could in fact be an SQL Server 2000. Or actually it is not that simple. When SQL Server runs in compatibility mode for SQL Server 2000 then the following applies:
  1. It does not recognize newer SQL features such as "TOP" and "Row_number( )". Thus, the fix for these features applies.
  2. It will allow you to create table-value functions (Acinta_Split) but it won't let you use them, thus the remedy for this applies.
  3. It still allows you to create indexes with Included columns. So there's no need to rewrite indexes!
One last thing to consider is whether the compatibility mode is actually correct or if it should be set to the actual version. But you have to verify this; if you change the compatibility mode it may cause some applications to malfunction!