How to connect to MS SQL Server from OpenInsight Using ODBC Connectivity (General,Warehousing) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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 [[notes:///85256DC100249E21/07DBCBABB6B3E379852566F50064CF25/C38A83DF2C8FA4E8852565370049A962|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 **A__d__d.**

** **

3.  Select the SQL Server driver as the data source, then click on **Finish**. 

 

{{kb0199_1.png}}

 

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 **__N__ext  >******

** **

5. Enter in the appropriate information for logging into the server and click **__N__ext  >******

** **

{{kb0199_2.png}}

** **

6.         Select or create the appropriate default database for your purposes and click on **__N__ext >**

** **

{{kb0199_3.png}}

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 **__T__OOLS  __C__LIENT****/SERVER WORKSPACE **icon.

        From the Client/Server Workspace menu choose **__F__ILE  __N__EW**

        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:

 

{{kb0199_4.png}}

 

        From the Client/Server Workspace menu choose **__F__ILE  __S__AVE AS**

        When prompted for a filename, enter **SQL7TEST**

      

Setting the system defaults

        From the Client/Server Workspace choose **__T__OOL  __C__ONNECTION  DEFAULTS**

        Select **ODBC **as the Data Source.

        Ensure that **Use Transaction Processing** is not checked.

        Click the **__A__CCEPT** button.

 

**Testing the SQL connection**

        From the Client/Server Workspace choose **__T__OOL  __Q__UERY 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.

 

 {{kb0199_5.png}}

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 **RevBase**or whatever test database you created).