Connecting To Your SQL Database [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.
====== Connecting To Your SQL Database ======

===== I.       Requirements =====

 

For the purposes of this example we will be using Microsoft’s SQL Server 2012, and using the AdventureWorks2014 sample database. 

 

===== II.      Making the connection =====

1.       The easiest way to define a new SQL connection is to use the SQL Connection Wizard.  It can be found from the OpenInsight 10 IDE by clicking, //NEW//, //Database Components//, //SQL-based DataSource//.

{{SQL_007.jpg}}

 

1.      From the New Data Source dialog box choose Connection Class: **DSBFS** and enter your Connection Name: **ADVENTUREWORKS_IRELAND**.

 

{{SQL_008.jpg}}

 

2.      When the Wizard is first started, the user is presented with a Welcome screen. Enter a name for this connection.  The name of the connection is used by OpenInsight to attach the data.  Below you can see that we have named this connection ADVENTUREWORKS_IRELAND.

 

{{SQL_009.jpg}}

 

3.     Clicking on the ‘Next’ button brings up a panel that prompts for the location of the Shadow Dictionary location. Shadow Dictionaries are OpenInsight versions of the column definitions that define the SQL table. OpenInsight does not want to affect the column definitions on the SQL data source, so a local copy is made.  There are a number of advantages to this, including formatting for OpenInsight reports.

 

The Wizard offers a default folder location where the shadow dictionaries will be created.  If a full path is not specified a directory will be created within your OpenInsight folder. We will accept this default, and then click on the ‘Next’ button.

 

{{SQL_010.jpg}}

 

 

4.      The next panel displayed is where the Connection String is entered.

 

//What is a connection string?//  When OpenInsight connects to a database we let ADO.Net utilize a provider to do the job for us.  The connection string contains the information, such as server name, database, and logon information that the provider needs to know to be able to establish a connection to the database or the data file.

      

Because there are so many different vendors and providers, and because many of these providers have multiple ways to make a connection, there are many ways to write a connection string.  There are websites such as [[http://www.connectionstrings.com|www.connectionstrings.com]] that help you determine these strings.  However, we have provided an ‘Options’ button to help in creating the connection string.

                  

Clicking on the ‘Options’ button launches a Data Link Properties dialog box.

 

{{SQL_011.jpg}}

 

5.     This dialog box is the standard Windows system interface for configuring connection strings to data sources.  This dialog box exposes all of the properties that the selected OLE DB provider supports.

 

The dialog box contains four tabs: Provider, Connection, Advanced and All.  We will only need to use the first two tabs in this example, and it is probably all you will need in practice.  In the example below we have selected Microsoft OLE DB Provider for SQL Server.

 

Once we have selected the OLE DB provider we want to utilize, clicking on the ‘Next’ button will bring up the Connection tab where we need to enter or choose connection information.

 

{{SQL_012.jpg}} 

 

In the example below, we will use the server where the copy of SQL Server is installed.  When OpenInsight wants to connect to this server, we will choose the copy of SQL Server on the system where it is located.

We will also utilize the Windows NT integrated security.

 

{{SQL_013.jpg}}

 

7.   Select the database on the server.  For this example we will use the AdventureWorks2014 database.

 

{{SQL_014.jpg}}  

 

8.    Once we have chosen the database on the server, we will click the ‘Test Connection’ button.  If the connection succeeds we will get a message like the one below.  Click the ‘OK’ button in the Data Link properties window.  Control will be returned to the SQL Connection Wizard, where the Connection String will be filled in.

 

{{SQL_015.jpg}}

 

 

9.     The Connection String has been created.  A list of SQL Providers supported by the SQL connection is displayed in the drop down list box.  This list may change from time to time.  The specific provider needs to be chosen because some of the providers require specific functionality and syntax changes.  Choosing the correct provider allows OpenInsight to handle this correctly.

 

Once the Connection String has been created, clicking on the ‘Next’ button brings up the Authentication Settings panel.

 

{{SQL_016.jpg}}

 

 

Although the Authentication Settings screen seems to duplicate some of the settings from the Data Link Properties screen, the logon data from that screen is not always populated in the connection screen.  Fill this screen out with the appropriate information, as provided to you from the SQL Server’s DBA.

 

Clicking on the ‘Next’ button will move the wizard to the Attach Now panel.

 

 {{SQL_017.jpg}}

 

The SQL Connection Wizard is completed.  Once the ‘Finish’ button has been pressed, SQL Dataset Wizard will launch.

 

{{SQL_018.jpg}}