How to Connect to Microsoft SQL Server

---

Purpose of this article

This article aims to show you how to download, install and connect Microsoft SQL Server 2017 Express to ADISRA SmartView.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications that can run on the same computer or on another computer on a network.

There are some limitations of features available in these software as it is free software.

Maximum database size limit: 10 GB
This limit is only applied to data (logs are not considered).
Maximum limit of RAM memory used: 1 GB
This limit is for the Buffer Pool (cache) only.
Maximum CPU limit: 1 CPU. If the server has 8 quad-core processors, only 1 quad-core processor will be used.

Downloading SQL Server 2017 Express and SQL Server Management Studio (SSMS)

In order for knowledge, SQL Server 2017 Express software is responsible for running the database. SQL Server Management Studio Software is a tool for configuring, managing and administering all components in Microsoft SQL Server. The tool includes script editors and graphical tools that work with server objects and resources. For our article, we will download both software.

1. Download SQL Server 2017 Express from the link below.
   Link: Click Here

2. Download SQL Server Management Studio (SSMS) from the link below.
   Link: Click Here

Installing SQL Server 2017 Express

1. Run SQL Server 2017 Express install.

2. Select the “Custom” installation type.

3. Enter the installation path. It is advisable to keep to the suggested path. Then click on “Install”.

4. After downloading the installation files, a window for installing SQL Server 2017 Express will open. Click on the first option “New SQL Server stand-alone installation or add features to an existing installation”.

5. Accept the terms and click Next.

6. Click Next.

7. Click Next.

Note: The firewall warning does not compromise the installation.

8. Keep the features selected as shown in the image below. Then click Next.

9. Select the “Named Instance” option.

10. Click Next.

11. The next step is the most important of the installation. Select the “Mixed Mode” option (SQL Server and Windows authentication). Set the initial password of the user “sa”, which is the default admin user.

Attention! This password will be used when connecting to the ADISRA SmartView.

Note:If you want, you can use Windows authentication by selecting the first option “Windows Authentication mode”.

12. Accept the prerequisite installation and click Next.

13. Accept one more prerequisite installation and click Next.

14. Wait for the installation to finish.

Installing SQL Server Management Studio (SSMS)

1. Run the SQL Server Management (SSMS) installer.

2. Set the installation path. It is advisable to stick to the suggested path. Then click Install.

3. Wait for the installation.

4. After installation, restart your computer.

Creating the SQL Server Database

After installing SQL Server 2017 Express and SQL Server Management Studio, let’s create a database and then a table.

1. Open the SQL Server Management Studio Software by searching the start menu. Type “SSMS” and then run it.

2. In the “Authentication” field, select the “SQL Server Authentication” option. Then put the login “sa” and the same password entered at the time of installation.

3. After opening the SSMS, in the navigation tree located on the left side, right-click on the “Databases” item and then click on the “New Databases…” option.

4. Insert the new database name as “DB_ADISRA”. In the table below you can view and change the location where the database files will be saved. Then click OK.

5. Below the expected result.

6. Now, expand the new database and right-click on the item “Tables”, “New” and then on “Table…”.

7. Insert two columns. The first called “ID” and with the Data Type as “INT”. The second column called “Names” with the Data Type as “varchar(50)”.

8. Then click on the save button located on the top menu.

9. Save the table as “Person”.

10. Refresh the database by clicking the “Refresh” button.

11. Expand the item “Tables”, right-click on the new table “dbo.Person” and then click on the Edit top 200 Rows option.

12. Insert the values “1, 2 and 3” in the ID column and fill in the “Names” column with the names “George, Amelia and John” as shown in the image below. Once that’s done, keep SSMS open and follow the next steps.

Configuring ADISRA SmartView

After creating the “DB_ADISRA” database and the “Person” table with some data, we will learn how to create an ADISRA SmartView connection with the SQL Database and how to query the data in the “Person” table through the DataGrid object.

1. Open ADISRA SmartView and create a new application. On the top menu, click on the “Settings” tab and then click on the “Database Connections” option.

2. Click the “+” button to add a new connectivity.

3. Select the “Microsoft SQL Server” option.

4. In the “Server name:” field we will need to put the server name. To know the name of the SQL server, follow the next step.

5. Open SSMS, right-click on the Database located in the navigation tree as shown in the image below and click on the “Properties” option.

6. In the first option of the table called “Name”, copy the name of the database.

7. Go back to ADISRA SmartView and paste the database name.

8. Select the “user SQL Server Authentication” option and enter the login “sa”, and then the password entered when installing SQL Server.

9. Select the option “Select or enter a database name:” and click on the “DB_ADISRA” database.

10. Test communication with the database, then click OK.

11. Save the connection name as suggested.

Querying the “DB_ADISRA” database

1. Create a new Graphic and insert a “Datagrid” object.

2. In the property list of the datagrid object, in the “Datagrid Config” property, select the option “Table”, “SQLServer001” and “Person”.

Note: When the table is selected, all columns will be shown.

3. Below the expected result.

4. Now, save the application and set “Graphic1” as the initial Graphic of the Runtime and then start the Runtime. Below the expected result.