====== Warehousing OpenInsight Tables to MSSQL Server 7.0 (General,Warehousing) ====== ====== ====== ==== Created at 18 MAY 2000 03:18PM ==== **Introduction** ** ** The following provides a step-by-step walkthrough example of using the OpenInsight Data Warehousing Wizard to warehouse linear hash tables into a MS SQL Server Database using ODBC connectivity.  Revelation Software has successfully performed  Warehousing OpenInsight Tables to MSSQL Server 7.0  using OpenInsight 3.72 and ODBC.   **//Note://**  For instructions specific to MSSQL Server version 6.5, please refer to the end of this document.      **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 DataSources in OI  (click [[notes:///85256DC100249E21/07DBCBABB6B3E379852566F50064CF25/C38A83DF2C8FA4E8852565370049A962|here]] for the link to it).     **Walkthrough** ** ** 1.  Preparing OpenInsight 3.72 for SQL 7.0 Data Warehousing:         Create a directory named //SQL7TEST// on a local hard drive of the workstation (this walkthrough will use //SQL7TEST// as the application name, user name, table name, etc. for demonstration purposes).         Install a fresh copy of OpenInsight 3.7.2 to that directory   2.  Creating a application         Start OpenInsight 3.72.         Open the //SYSPROG// application.         In SYSPROG Application Manager view, choose __F__ILE  __N__EW APPLICATION from the menu.         Create a new application called //SQL7TEST// with the username //SQL7TEST// // // 3. Creating a table         In the //SQL7TEST// Application Manager choose __T__OOLS  __T__ABLE BUILDER from the menu.         From the Table Builder menu choose __F__ILE  __N__EW         Click //__O__K// to accept the default file location.         Enter the fields in Table 1 into the table.         From the Table Builder menu choose __F__ILE  __S__AVE         When asked for a table name, enter //SQL7TEST.//         Close Table Builder.   **//Example Table 1//** |**POSITION       **|**COLUMN NAME**|**DATA TYPE** |**KEY**|**NOTNULL**|**DEFAULT**| |KEY 1 |ID |VARCHAR(65541)|YES |NO |  | |2 |FIELD_1 |VARCHAR(65531)|NO |NO |  | |3 |FIELD_2 |VARCHAR(65531)|NO |NO |  | |4 |FIELD_3 |VARCHAR(65531)|NO |NO |  |     4. Creating a form         Once back in the //SQL7TEST// Application Manager choose __T__OOLS  __F__ORM DESIGNER from the menu.         From the Form Designer menu choose __F__ILE  __N__EW FORM         Under the Data Source heading choose OPENINSIGHT TABLES         Under the Tables heading double-click //SQL7TEST.//         Highlight all of the fields under the COLUMNS heading.         Click __A__DD to add the fields to the form.         Click __O__K to finish designing the form.   5. Saving and running the form         From the Form Designer menu choose __F__ILE  __T__EST RUN         In the Test Run Setup window, make sure that both  Actual data tables  and   Always save  are checked.         Click __O__K.         When asked what to save the form as, type //SQL7TEST//.         When the form is saved it will run and accept data entry.   6.  Populating the form with data         Populate the table with the ten records in //Table 2.//         Once the table has been populated close the form by choosing __F__ILE  __C__LOSE from the form menu.         Close the Form Designer by choosing __F__ILE | __E__XIT from the Form Designer menu.         You should now be back in the Application Manager.   **//       Example Table 2//** |**//ID//**|**//FIELD_1//** |**//FIELD_2//** |**//FIELD_3//**| |**//1//** |**//Bob//** |**//Jones//** |**//Cat//** | |**//2//** |**//Jen//** |**//Smith//** |**//Dog//** | |**//3//** |**//Randy//** |**//Watson//** |**//Bird//** | |**//4//** |**//Samantha//**|**//Richards//** |**//Hamster//**| |**//5//** |**//James//** |**//Rodriguez//**|**//Gecko//** | |**//6//** |**//Barbara//** |**//Johnson//** |**//Cat//** | |**//7//** |**//David//** |**//Sade//** |**//Dog//** | |**//8//** |**//Michelle//**|**//Tierney//** |**//Bird//** | |**//9//** |**//Robert//** |**//Adams//** |**//Hamster//**| |**//10//**|**//Carrie//** |**//King//** |**//Gecko//** | **// //** 7.  Creating an SQL Connection in OpenInsight         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 //__O__K//.         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: {{kb0198_1.png}}         Choose your data source and then click __O__K. (The //SQL7TEST// data source will only appear after you have created it.  For the   purposes of this demonstration, //SQL7TEST// was chosen as the data source).         From the Client/Server Workspace menu choose __F__ILE  __S__AVE AS.         When prompted for a filename, enter //SQL7TEST.// // //  8.  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.   9.  Starting the Data Warehousing Procedure         From the Client/Server Workstation menu choose __T__OOLS  __C__REATE WAREHOUSE PROCEDURE         From the drop down menu choose the //SQL7TEST //table.         Click the //__N__EXT //button.         Click the //__N__EXT //button to accept the default number of SQL tables.         Make sure that the net window lists the ID field as the primary key.         Click on the //__D__EFINE// button to start the Table Definition Wizard.   10. Continuing the Data Warehouse Procedure with the Table Definition Wizard         When asked for a unique table name, enter //SQL7TEST.//         Click the //__N__EXT// button.         Click the //__N__EXT//** **button to accept the defaults concerning primary keys.         At the next window, click the //__A__DD //button to add specific rows to be warehoused.         When a listing of the rows appears, highlight each row, and then click the //__N__EXT //button.   11.  Modifying the Default SQL Scripts and Finishing the Data Warehousing Procedure         A window containing SQL scripts like those below should now appear:                    create table SQL7TEST                          (                          ID text not null primary key,                          FIELD_1 text null,                          FIELD_2 text null,                          FIELD_3 text null                          )           We are going to delete the bolded portions of the SQL scripts:                    create table SQL7TEST                          (                          ID text **not null primary key**,                          FIELD_1 text **null**,                          FIELD_2 text **null**,                          FIELD_3 text **null**                          )             The final script should look like this:                    create table SQL7TEST                          (                          ID text,                          FIELD_1 text,                          FIELD_2 text,                            FIELD_3 text                          )           Click the //__F__INISH// button.  The table we just created should be listed.         Click the //__N__EXT// button.         Click the //__F__INISH// button to complete the Data Warehousing Procedure.   12. Using Warehouse Manager         From the Client/Server Workstation menu choose __T__OOLS  __W__AREHOUSE MANAGER         At the next window, make sure that the //SQL7TEST// table is listed, and the checkbox is checked.         Click the //__N__EXT// button.         Click the //__N__EXT// button to accept the defaults concerning workstations         At the next window make sure  //Yes, Create The Data Warehousing Tables for Me//** ** is checked.         Click the //__N__EXT//** **button.         Click the //__N__EXT// button to accept the defaults concerning clearing tables.   13. Connecting to a Database:         From the Initiate Warehousing window, click the //__C__ONNECT// button.         At the //Connect to Data Source// make sure that the correct SQL connection is specified (i.e., //SQL7TEST//).         Make sure the Login Name and Password are correct.         Click the //__O__K// button.         Click the //__N__EXT// button.   14.  Running the Warehouse Procedure         From the Initiate Warehousing window, make sure Run Each Warehousing Procedure Once is checked.         Click the //__F__INISH//** **button.  This will open the Warehouse Manager window.         From the Warehouse Manager window, click the //__S__TART//** **button.   The system will now go through the warehouse procedure.  If there is an error in the process the Review Log button will become enabled and clicking on it will reveal the errors encountered in the warehousing process.  If there are no errors and all of your records were processed, then your new table has been created.   **//NOTE:  //**//If the Review Log button is enabled, repeat the Data Warehousing steps again and verify all the above steps were followed exactly.// // // 15.  Verifying Success of the Data Warehouse Procedure         From the Client/Server Workspace choose __T__OOL  __Q__UERY WINDOW         At the  Connect to Data Source  window, select SQL7TEST as the data source.         Make sure the **Login Name** and **Password **are correct.         Click the //__O__K //button.         At the top of the New Query window (where the flashing cursor is) type the following: SELECT * FROM SQL7TEST           Click on the Test Run button. The rows from your warehoused table should appear:   {{kb0198_2.png}}       **MSSQL Server 6.5 Notes** MSSQL Server 6.5 has been successfully tested to warehouse large amounts of OpenInsight (version 3.72) data using a native connection without any OpenInsight feature limitation.  The instructions below are specific to those preferring a native connection to SQL Server 6.5.   **//Omit steps 7-8 in the MSSQL 7.0 instructions above, and replace with steps a-e below.  Upon completion of steps a-e, proceed to step 9 of this document.//** a.  Obtaining and running the SQL batch file         Open the MS6BATCH.TXT file located in your root OINSIGHT directory (This is also located on our website __www.revelation.com__ in our  For WORKS  Downloads section).         Copy all of it, open your MSSQL Query Analyzer, start a New Query, paste the MS6BATCH.TXT contents in the query window and run the query.   b.  Obtaining the appropriate .DLLs         Place the following .dlls in your Windows/system directory for Win95/98 users, or your Winnt/system directory for Windows NT users: {{kb0198_3.html; charset=utf-8}}{{kb0198_4.html; charset=utf-8}}   c.  Creating a native SQL Connection in OpenInsight         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** **//SQLServer//.         Fill out the information shown in Figure 1 (Connection window) below.         From the Client/Server Workspace menu choose __F__ILE  __S__AVE AS         When prompted for a filename, enter //SQL7TES//T.   **//Figure 1//** {{kb0198_5.png}} **// //** d. Setting the system defaults         From the Client/Server Workspace choose __T__OOL  __C__ONNECTION DEFAULTS.  Select //SQLServer//** **as the Data Source.         Ensure that //Use Transaction Processing// is not checked.         Click the __A__ccept button.   e. Testing the SQL connection         From the Client/Server Workspace choose __T__OOL  __Q__UERY WINDOW         At the //Connect to Data Source// make sure that the correct SQL.connection is specified.         Make sure the //Login Name// and //Password//** **are correct.         Click the //__O__K// button.   **//Proceed to Step 9 of this document.//**