Connect to MS SQL Server

1) You will learn to connect to a SQL Server database through the designer.

2) This article uses Microsoft SQL Server 2008 as an example.

1. Version

2. Database configuration

Official advice:

For better fetch performance, you are advised to replace the SQL Server database driver with sqljdbc.jar for SQL Server 2005, 2008, 2012, 2014, 2016, and 2017 versions.

3. Place the driver

Note: For versions 10.0.16 and later, skip this step.

If the version before 10.0.16, you can choose to replace and update the driver package: sqljdbc.jar

Delete driver package sqljdbc.jar under %FR_HOME%\webapps\webroot\WEB-INF\lib. And copy the downloaded driver package to the directory.

1. Create JDBC data connection

1) Create a new JDBC connection, select SQL Server, and the Driver and the default URL will be loaded automatically. You can enter your database information.

2) Click Connection Pool Attributes. Generally, you just need to keep the default settings.

3) Modify the URL by entering the actual address of the database server. Fill in the User Name and the Password. Click Test Connection. Confirm if the connection is successful. If the connection is successful, click OK.

1. Create a new data connection

1) Log in to the decision-making platform as an administrator, click Manage > Data Connection > Data Connection Management, and click New Data Connection, as shown in the following figure:

Note: If a non-admin user wants to configure a data connection, the administrator needs to assign him the authority to manage the data connection node under the system. For details, please refer to Data Connection Authorization

2) Select Microsoft SQL Server under the Commonly Used option, as shown in the following figure:

2. Configure data connection

1) Enter the corresponding data connection information according to the actual situation, as shown in the following figure:

2) Click to Test Connection, or Click theClick to Connect Database under Pattern, if a successful connection prompt box pops up, it means that the database is successfully connected, as shown in the following figure:

After the connection is successful, the mode will automatically select the default value, as shown in the following figure:

Note 1: The original intention of the schema is to make it easier for other functions to read the data in the database, just select the "default value".

Note 2: When using SQL statements when connecting to a multi-schema database, you need to search with the schema name prefix, such as SELECT * FROM [dbo].[asdfg]

3) Click the save button in the upper right corner.

3. Effect View

After the Microsoft SQL Server database connection is successful, you can edit, rename, copy, delete and other operations on the platform, as shown in the following figure:

At the same time, the successfully configured data connection is automatically generated in the FineReport designer, as shown in the following figure:

1. Check if URL is correct

1) Cut the field: ;databaseName=, enter the User Name and the Password of SQL Server, click [Test Connection], and confirm IP and port are correct.

2) Paste the field: ;databaseName=, enter the name of database to be connected, click [Test Connection], and confirm the instance name is correct.

2. Check the instance name

Check if the instance name of database is consistent with the databaseName in URL.

3. Check if connection to the remote server is enabled

For SSMS, right click on the server, go to [Server Attributes > Connection], check Allow remote connection to this server.

4. Check user status

For SSMS, right click on the user, go to [Login Attributes > Status], check Allow to connect to database engine, and Enable login.

5. Check if TCP/IP protocol is enabled

6. Check if database service is enabled

7. Check network configurations

Open sqlserver Configuration Manager, and check if Named Pipes and TCP/IP are enabled in sqlserver network configuration.

8. Check if the Port Number is correct

9. Check if sp4 installation package is installed