MS SQL Server Data Source


Apps - Datasource - SQL Server icon.png This type of data source belongs to the Standard category in the data source library. It is used to retrieve data by running a query in a Microsoft SQL Server database

   Open url.png  See the documentation. Logo Microsoft SQL Server.png

Notes

Open url.png  See General remarks on data sources

  • You must first define the connector for connecting to the SQL server.
  • You require a license to use the SQL Server connector. If the license is missing, the following message will appear: MySQL License not found. Please contact your sales rep if you need it.

List of properties

Content

Alias/Connector: Connector used to retrieve the connection settings to the SQL server. Note: If the connector you want is not available in the drop-down list, click EVApps - GUI - Connector icon.png to create it.

Database: Used to display a list of the databases available on the SQL server that users are authorized to access.

SQL Query: SQL query run on the database. It can query one or more tables and return the results. You can enter the query directly in the field or click Apps - Edit icon.png to open the text editor. Note: The query must end with a semi-colon (;).

     Example documentation icon EN.png

SELECT name,surname,status,
      CONVERT(VARCHAR, date, 105) AS datefr
FROM   famille_tbl;

     Note: 105 is the date format dd-mm-yyyy.

Preview

Configure Field Types: Used to open the field editor that enables you to select data source fields and specify the type of each field, e.g. date, integer, etc. when this is not automatically detected.

  • The Apps - datasources Properties - Preview button.png button below the property shows the number of rows (max. 15) and columns extracted. Click to open the editor.
  • The button will display No data available as long as you have not selected any of the mandatory elements for the data source.
  • In edit mode, only the first 15 data source records are displayed.
  • In execute mode, all of the data source records are displayed.
  • Data in static data sources comes from the last file upload.
  • Data in dynamic data sources is refreshed when you open the app in edit mode.
SelectFirstRowAsCurrentRecord

Select First Row As Current Record: Used to indicate if the first record of the data source, when displayed in widgets, is selected by default (box is checked) or not (box is not checked).

AutoRefresh

Auto Refresh (Note: Only for dynamic data sources): Used to indicate the automatic refreshment interval for data from the data source in hours/minutes.

  • Data is automatically refreshed each time the app is run and each time the page is redisplayed, regardless of whether it is in execute mode or edit mode.
  • Caution: The updating of data requires additional activity on the Product name - ev sas.png platform.

    Best Practice icon.png

  • You should define an automatic refreshment interval only if the app is required to constantly display real-time data for long periods of time on screen.
             Example documentation icon EN.png  View dashboard data
  •  You should not specify an automatic refreshment interval if the app is only used for displaying a snapshot of data.
             Example documentation icon EN.png  Display a portal where catalogs can be consulted
NoDataMessage

No Data Message: This message is displayed when the data source does not contain any data.

  • Enter the code directly in the field or click Apps - Edit Html icon.png to open the text editor

Examples of MS SQL Server commands

  • To view the list of tables
SELECT *
FROM   information_schema.tables;
  • To view the data in the MyTable table
SELECT information_schema.columns
WHERE  table_name = 'MyTable'
Tags:
Last modified by Unknown User on 2017/09/01 16:44
Created by Administrator XWiki on 2015/06/02 09:36

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2019