Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== 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.//** kb/kb_articles/kb0198.txt Last modified: 2024/01/30 13:36by 127.0.0.1