Load SQL Server Data
Last updated
Last updated
TIP: Note that the Data Wizard windows can be resized as necessary for optimum display of data.
To display the Data Wizard, select New Data Source in the Load Data Section from the File Ribbon.
The Welcome wizard page displays. Click Next. In the Select Data Source Type wizard page, select Database. Click Next.
In the Select Database Type wizard page, select Microsoft SQL Server (version 7.0 or later). Click Next.
In the Connect to SQL Server Database wizard page:
From the SQL Server pull down list, select a server.
To use integrated security, be sure a checkmark appears in the Use Integrated Security field.
If not using integrated security, enter a login name and password.
Click Connect. When the icon next to this button changes from red to green, you can select a database from the Database pull down list.
The Select Data for Dataset wizard page displays. Use this wizard page to define the dataset. Specify how you want to define the dataset:
by table
by SQL query
by stored procedure
To use table selection to define the datatset, select the Dataset from table selection option. All options available to the SQL Server are shown in the Available Items field and denoted by . All available views are shown in the Available Items field as .
To select a table or view, use the mouse to drag an item in the Available Items column to the Selected Items column, or double-click on the table/view item. You can also use the buttons located between the Available Items and Selected Items fields:
Use >> to copy all items
Use > to copy a single highlighted item
Use < to remove individual items from the Selected Items Column
Use << to remove all items from the Selected Items Column
To use an SQL query, select the Define dataset from SQL query option, then either type in the SQL query or use the right mouse button and the Paste Query context menu option. Click Next.
NOTE: The Define dataset by SQL query option is an advanced feature. To use this option, you need to be familiar with data and with SQL syntax. Your SQL query must be valid (known to retrieve data) and must be submitted with proper syntax. We recommend pasting a SQL query string that you created in an application such as Query Analyzer.
To use a stored procedure, select the Dataset from stored procedure option, then type in the name of the stored procedure to use in the Procedure Name edit box. For each parameter in the stored procedure, click on the Add New … button and then type in the parameter name in Parameter Name column, select the parameter data type from the Data Type pulldown list, and specify the parameter value in the Value column.
Click Next.
The Configure the Dataset wizard page displays. In this page, you can:
Edit the table name or change field properties via the Configure View.
Preview the first 25 rows of data in the dataset via the Data View.
Two views appear on the top left side of the wizard page:
By clicking the Configuration View you can:
Edit the display name for the table.
Configure which fields to include in the dataset by checking or clearing the checkbox in the first column.
Change field properties, such as display name for the field, field data type, and the level of precision, whether or not to group digits or show the values as a currency for numeric fields.
The Data View displays the data values for the first 25 rows in the dataset. This is a view-only mode to help confirm it is the desired dataset and is the default view for the page.
To edit the table name, type the new table name in the Table Name edit box.
To select a field to include in the dataset, if a checkmark appears in the first column of the row, the field is already included in the dataset. If no checkmark appears, click on the check box to select the field. Repeated mouse clicks on the checkbox will toggle field selection between including in the dataset and excluding from the dataset.
TIP: Click on the first column header to select all or exclude all fields from the dataset. To deselect a field for inclusion in the dataset, if a checkmark appears in the first column of the row, the field is already included in the dataset. Click the check box to clear the checkmark, thus excluding the field.
To change properties of a field, highlight the cell to be changed. For the Display Name column, type in the desired name. For the Data Type, Precision and Currency Symbol columns, note the drop down arrow that displays to the right of the cell. Click the arrow to display a list of options to choose from and make a selection from the pulldown list. For the Group Digits and As Currency columns, click on the checkbox to toggle whether or not to use these options.
TIP: For currency columns, the currency symbol defaults to the symbol used by the computer's regional settings, which can change if the project is opened on a different computer. To use a specific symbol regardless of the computer's settings, select a symbol from the pulldown list.
Click Next.
Review the information displayed in the Dataset Summary window. This information includes:
Source Type - Microsoft SQL Server Database.
Data Source – Server: <server name> Database: <database name>
Source Table - the name of the table in the database.
Display Name - the name of the table as it will appear in ADVIZOR.
Rows – the number of rows of data to be imported.
Selected Fields – the number of fields you selected in the Configure the Dataset wizard page.
Click Next to proceed.
The Loading Dataset window displays. It shows the progress as data is loaded into ADVIZOR. The Completing ADVIZOR Data Wizard page is displayed. In this page, a default data source name is included to uniquely identify the dataset.
To use a different name to identify the data source, type in a new name in the Data Source Name editbox. You can also add a description of the data source. When the Remember Data Source Definition checkbox is checked (the default setting), the dataset and all of its configured properties will be remembered for “one-click” data loading the next time you use the ADVIZOR application.
Click Finish to close the wizard.
