Trick: Use MSSQL 2000/2003 under BI 4.1

[Former Member]

Remark: This document is tested / in relation with a BOXI server running on MS Windows

Officially BO 4.1 is not able to handle < MSSQL 2005 Server.

Because we have a legacy MSSQL system as a data source with several universes attached we had to connect it to BI4.1 and using these steps we finally achieved it.

Known Issues

• Reports using "EXCEPT" will cause issues because EXCEPT is not known by MSSQL 2000 - simple re-work of the Report can fix this issue

Install MSSQL 2008 Native Client

Run "SQL 2008 Native Client.msi"

• Welcome screen: Next
• License Agreement: Next
• Registration Information
• Feature Selection
  • Next
• Installation
  • Next
• Completing
  • Finish

Create an ODBC Connection directly on the BOXI Server

Logon to BOXI server.

Please go to Start -> Control Panel -> Administrative Tools  and click on Data Sources (ODBC).

Go to the System Panel and click on “Add…”

Now the New Connection Wizard (programmed by Microsoft) will be started – on the first page you should see a list of available drivers. For our current topic we search for “SQL Server” in this list.

If you found it then click on it that it´s marked blue - > after this you can click on “Finished”

After this you will get a new window (which was also programmed by Microsoft)

At this window you have to specify the Name for the Connection and the Server IP which you would like to use:

After fields are specified then you can click on “Next”

In the next Window you have to specify the Username which should be used and also the password:

Fill in data source logon credentials then please click on “Next”

In the next Window there are some additional Parameters regarding quotes etc.

After filling in these parameters you can click on “Next”

If theses parameters are also filled you can click on “Finish” -> after this you will get a Dialog to check the Connection. – click on “Test Data Source”.

Considering no mistake is made you will see a message regarding a successful connection. – if not then go back through the wizard and correct the failed parameter.

At the end click on OK (also in the next window).

You've successfully created the odbc connection.

Change the already existing / Create new connection in BOXI 4.1.

Open the “Universe Design Tool” and click on “Connections”. Or use IDT. We continue with UDT.

If you change an existing (migrated) connection otherwise skip this section.

In the appearing window you need to search for your connection which is used to connect to the MSSQL 2000 Server and double click on it.

In the next window you will see some details regarding this connection like Username and Server which is used.

But this connection is e.g. an OLE DB Connection which must be changed – to change this click on “Back”

Now you will see a list of available Drivers you have to  use: “MS SQL Server 2008 / OLE DB Providers

Click on “OLE DB Providers datasource” that it will be marked BLUE and then click on “Next”

Type in the Username and Password which should be used for this connection and select the previously created ODBC Connection name from the “Data Source Name” Combobox

If all fields are filled click on “Next”

On the next page you can specify some parameters which should not be changed.

If default is fine click on “Next”

Now you will see summary which can directly be closed with the “Finish” button

As a last step you need to close the Connection Overview with the “Close” button. Otherwise your configuration is lost.

Finally test if the connection also works from BO / Universe side.

Now you happily use or reuse your not supported legacy MSSQL databases and prepare them for migration.