最近在使用RuoYi系统开发ERP系统，由于需要使用SQLServer数据库来作为系统数据库所以有了今天的文章。

先给出完整代码，包含数据库脚本：https://github.com/elnujuw/RuoYi-SQLServer

本文使用的数据库版本是Microsoft SQL Server 2019，下面将介绍具体实现步骤：

1. 正在执行的慢SQL

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC;

2. 过去执行的慢SQL

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT t.text, (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time, (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time, ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE t.text like '<Your Query>%' -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

问题

SQL Server 2017在使用“STRING_SPLIT”方法时报错：

1

select * from STRING_SPLIT('1,2,3,4,5',',')

1
2

消息 208，级别 16，状态 1，第 3 行
对象名 'STRING_SPLIT' 无效。

原因

STRING_SPLIT方法要求数据库的兼容级别至少为130。当级别小于130时，SQL Server无法找到STRING_SPLIT函数。

STRING_SPLIT方法介绍可参考：https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

使用以下SQL语句查看数据库的兼容级别：

1

SELECT compatibility_level FROM sys.databases WHERE name = 'DBName'

解决方法

使用以下SQL语句修改数据库兼容级别：

1

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL=140

140代表SQLServer2017

注意：SQLServer的master数据库只能还原到版本号相同的SQLServer实例

执行命令select @@version;查询当前SQLServer实例的版本号：

1

Microsoft SQL Server 2005 - 9.00.5324.00

报错

1

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.” 

解决方法

1. 重新运行安装程序到图一报错的地方。

2. 下载文件http://eddiejackson.net/apps/Fix_Error_SQL2005.zip

3. 复制对应版本的文件到安装目录Program Files (x86) -> Microsoft SQL Server > MSSQL.2 > MSSQL > Binn替换原文件。

4. 返回图一点击Retry。

安装完成。

参考：https://eddiejackson.net/wp/?p=16941

您可以使用sp_cycle_errorlog存储过程定期重新初始化错误日志：

1
2

EXEC sp_cycle_errorlog ;  
GO 

重新初始化前：

重新初始化后：

清理sql server数据库日志方法：

1. 打开查询分析器，输入命令DUMP TRANSACTION 数据库名 WITH NO_LOG

2. 再打开企业管理器–右键你要压缩的数据库–所有任务–收缩数据库–收缩文件–选择日志文件–在收缩方式里选择收缩至: ,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了。

使用BCP命令导出表数据到电子表格文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

--EXEC master..xp_cmdshell 'BCP test..person out f:\backup\person.xls -c -T'

declare @tblname varchar(100)
declare @dbname varchar(100)
declare @dir varchar(200)
declare @str varchar(500)

set @dir = 'f:\backup\'
set @dbname = 'test'
set @tblname = ''

while exists(select name from sys.tables where SCHEMA_NAME(schema_id)=N'dbo' and @tblname < name)
begin
    SELECT @tblname = min(name) from sys.tables where SCHEMA_NAME(schema_id)=N'dbo' and @tblname < name
    set @str = 'BCP '+@dbname+'..'+@tblname+' out '+@dir+@tblname+'.xls -c -T'
    exec master..xp_cmdshell @str
end