Microsoft SQL Server data connection

Microsoft SQL Server is a relational database management system, which has the advantages of easy use, good scalability, and a high degree of integration with related software.

In this chapter, we will introduce how to connect to the Microsoft SQL Server database in FineBI.

Note: For detailed description of each setting item of data connection, please refer to Configure Data Connection.

Note: If users want to connect to a non-default instance, they can change the URL to: jdbc:sqlserver://ip:port;instanceName=instance name; databaseName=database name

The data connection needs to select different driver according to the database version:

1) If you use SQL Server 2000, you need to download

sqljdbc.zip, unzip the compressed package, and place it under %FineBI%\webapps\webroot\WEB-INF\lib, delete the original built-in sqljdbc42.jar package, and restart FineBI.

2) For other versions, use the driver package sqljdbc42.jar that comes with FineBI directly.

3.1 Create a data connection

Log into the data decision system as an administrator, click "Manage>Data Connection>Data Connection Management", click "New Data Connection", as shown in the following figure:

Note: If a non-administrator user wants to configure a data connection, the administrator needs to assign the authority to the "Data Connection" under the "Manage". For specific operations, please see: Data Connection Control.

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

3.2 Configure the data connection

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

Note: In general, the default port number of SQL server is 1433. Users can use the statement exec sys.sp_readerrorlog 0, 1,'listening' to view their own port number.

2) Click "Test Connection", or click "Click to Connect Database" under "Pattern", if it prompts "Connection Successful" , it means that the database is successfully connected, as shown in the following figure:

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

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

Note 2: When connecting to a multi-pattern database, the SQL statement needs to be searched with the pattern name prefix, for example, SELECT * FROM [dbo].[asdfg]

3) Click "Save" in the upper right corner.

3.3 Preview effect

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

1) Use a JDBC data connection and add a dataset, and the data type of the field in the database is "datetime". If the field with the time between "1900-01-01 08:00:00" and "1900-01-01 08:05:42" appears, then The added dataset will become "1900-01-01 08:05:43", so critical data like "1900-01-01" should be avoided as much as possible.

2) When the Spider engine turns on real-time data:

• In SQL Server 2000 and SQL Server 2005, the date type does not exist. When the year, month, and day are taken, the cast(datetimeCol as date)in jooq will report an error. Use convert(varchar(10),datetimeCol,120) to run normally.

• The time type will report an error when performing date type-related conversions.

3) Configure data connection error: The driver cannot establish a secure connection with SQL Server by using Secure Socket Layer (SSL) encryption.

 Solution: Open %JAVA_HOME%\jre\lib\security\java.security , delete or comment out 3DES_EDE_CBC, save and restart FineBI to connect normally.

4) The count(*) statement cannot be used in "SQL Dataset" after connecting to SQL Server, otherwise an error: 6240001 empty field names found.