How to connect to MS SQL Server from OpenInsight Using ODBC Connectivity (General,Warehousing)
Created at 18 MAY 2000 03:21PM
Introduction
With the introduction of MSSQL Server version 7.0, came the feature loss of 16-bit native connections. It was discovered that OpenInsight data still warehoused, however there were limitations to the amount of data that was returned to the OpenInsight query tool. For many Revelation customers, this became a feature loss issue with the query tool for OpenInsight.
The following provides a step-by-step walkthrough of connecting to a MS SQL Server from OpenInsight using ODBC Connectivity. Revelation Software has successfully connected to MSSQL Server 7.0 Using ODBC Connectivity using OpenInsight 3.7.2.
Pre-Requisites
1) Verify that you have a 32-bit SQL Server ODBC driver file. An example is SQLSRV32.DLL version 3.70.06.23 dated 11/20/98 (installed, for example, during the MS SQL Server 7.0 Client Install, see step 2 below). You should be able to see this driver listed within your ODBC 32-bit Data Sources (located within the Control Panel) under the Drivers tab.
2) Install the SQL Server Client to the workstation. Minimum install requires the Client Connectivity tools (as can be seen in the Desktop custom install). Use the Client Network Utility to add the SQL Server to the list of available servers.
3) For this walkthrough, we recommend installing the MS SQL Server 7.0 Query Analyzer (Under Management Tools, during setup). Test your connection by using Query Analyzer on a known table on the server.
4) You will have to thunk your ODBC.INI file. See the OpenInsight KB article on Seeing 32-Bit Data Sources in OI (click here for the link to it).
Creating an ODBC Data Source
1. Open up the ODBC Data Source Administrator (located within Control Panel) on a workstation with has access to the MS SQL Server 7.0.
2. Click on the System DSN tab, which looks almost identical to the User DSN tab, and click on Add.
3. Select the SQL Server driver as the data source, then click on Finish.
4. You ll see another screen which requires a Data Source name, description and server name. Enter in SQL7TEST for name and description. Enter in the <servername> where you wish to Warehouse your Data. Click Next >
5. Enter in the appropriate information for logging into the server and click Next >
6. Select or create the appropriate default database for your purposes and click on Next >
7. On the following screen accept the default settings and click Finish >
8. Click Test Data Source and then OK if it test successfully. Afterwards, you should see your Data Source in the System DSN Dialog Box/Tab.
9. After thunking has been done. You must re-edit your ODBC.INI file to include your new Data source. The following line should be copied from the [32-bit] Data Sources and pasted into the non-32-bit sources so it looks like below:
[ODBC 32 bit Data Sources]
MS Access Database=Microsoft Access Driver (*.mdb) (32 bit)
dBASE Files=Microsoft dBase Driver (*.dbf) (32 bit)
Excel Files=Microsoft Excel Driver (*.xls) (32 bit)
Visual FoxPro Database=Microsoft Visual FoxPro Driver (32 bit)
Visual FoxPro Tables=Microsoft Visual FoxPro Driver (32 bit)
dBase Files - Word=Microsoft dBase VFP Driver (*.dbf) (32 bit)
FoxPro Files - Word=Microsoft FoxPro VFP Driver (*.dbf) (32 bit)
SQL7TEST=SQL Server (32 bit)
[ODBC Data Sources]
MS Access Database=Microsoft Access Driver (*.mdb) (32 bit)
dBASE Files=Microsoft dBase Driver (*.dbf) (32 bit)
Excel Files=Microsoft Excel Driver (*.xls) (32 bit)
Visual FoxPro Database=Microsoft Visual FoxPro Driver (32 bit)
Visual FoxPro Tables=Microsoft Visual FoxPro Driver (32 bit)
dBase Files - Word=Microsoft dBase VFP Driver (*.dbf) (32 bit)
FoxPro Files - Word=Microsoft FoxPro VFP Driver (*.dbf) (32 bit)
SQL7TEST=SQL Server (32 bit)
[MS Access Database]
Driver32=C:\WINNT\System32\ODBCJT32.DLL
[dBASE Files]
Driver32=C:\WINNT\System32\ODBCJT32.DLL
[Excel Files]
Driver32=C:\WINNT\System32\ODBCJT32.DLL
[Visual FoxPro Database]
Driver32=C:\WINNT\System32\vfpodbc.dll
[Visual FoxPro Tables]
Driver32=C:\WINNT\System32\vfpodbc.dll
[dBase Files - Word]
Driver32=C:\WINNT\System32\VFPODBC.DLL
[FoxPro Files - Word]
Driver32=C:\WINNT\System32\VFPODBC.DLL
[SQL7TEST]
Driver32=C:\WINNT\System32\sqlsrv32.dll
Testing Your ODBC Source Within OpenInsight
Setting up an SQL connection
Start OpenInsight.
When prompted for an application, choose any application.
From the Application Manager, choose the TOOLS CLIENT/SERVER WORKSPACE icon.
From the Client/Server Workspace menu choose FILE NEW
At the New Entity window, highlight Connection and click OK.
From the Connection window make sure that the data source type is: ODBC.
Double-Click on the Value field for Data Source and you should see something similar to below:
From the Client/Server Workspace menu choose FILE SAVE AS
When prompted for a filename, enter SQL7TEST
Setting the system defaults
From the Client/Server Workspace choose TOOL CONNECTION DEFAULTS
Select ODBC as the Data Source.
Ensure that Use Transaction Processing is not checked.
Click the ACCEPT button.
Testing the SQL connection
From the Client/Server Workspace choose TOOL QUERY WINDOW
At the Connect to Data Source Window, ensure that the correct SQL connection is specified.
Validate that the Login Name and Password are correct.
Click the OK button.
The Query Window should appear. Enter in the following script (as seen below) where <testtble> is a data table in your database: Select * from <testtble>
Click the Test Run button and the data should be returned.
For this walk-through, replace testtble with a known data table in the database you created. This table was copied from an existing table, using Enterprise Manager s Export utility, from an older database which was a warehoused table from OI using a native SQL connection with MSSQL Server 6.5. Because the Data Source was for a new database RevBase , this step was necessary to demonstrate the connectivity.
To reduce the likelihood of error, it may be prudent to copy the table successfully tested from that particular database to the test database RevBaseor whatever test database you created).