sqlserver数据库相关

最新推荐文章于 2020-06-16 14:37:05 发布

chenzhenjie2015

最新推荐文章于 2020-06-16 14:37:05 发布

阅读量325

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/chenzhenjie2015/article/details/77090149

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQLServer控制用户访问权限表：http://www.cnblogs.com/yxyht/archive/2013/03/22/2975880.html

图解PowerDesigner通过数据源生成物理数据模型：http://jingyan.baidu.com/article/ceb9fb10b68abd8cad2ba0eb.html

jdbc执行sql 不支持“variant”数据类型：

 select top 100 [value] from sys.extended_properties   出现

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 不支持“variant”数据类型。

改成下面几个
 select top 100 cast([value] as varchar(500)) [value] from sys.extended_properties

SQL Server 2008 中收缩数据库(DUMP,TRANSACTION,TRAN,无效,语法错误)

ALTER DATABASE 数据库名 SET RECOVERY SIMPLE
ALTER DATABASE 数据库名 SET RECOVERY FULL
DBCC SHRINKDATABASE(数据库名, 0)

删除重复数据，保证一条

  delete  from [bjnkquji].[dbo].[tb_o_monitorData_Histroy] where (stcd+CONVERT(varchar, receiveTime, 120 )) in
  (
  	select stcd+CONVERT(varchar, receiveTime, 120 ) from [bjnkquji].[dbo].[tb_o_monitorData_Histroy]
  	group by stcd,receiveTime
 	 having count(*)>1
  
  )
  and id not in
  (
  	select MAX(id) from [bjnkquji].[dbo].[tb_o_monitorData_Histroy]
  	group by stcd,receiveTime
 	 having count(*)>1
  )

SQL Server数据库日志已满的解决方法总结篇：http://database.51cto.com/art/201108/281840.htm

数据库自动备份和删除：

declare @path  varchar(1000);
declare @fullPath  varchar(1000);
set @path ='E:\北京农污项目\数据库自动备份\('+convert(varchar,GETDATE(),23)+')_';
set @fullPath =@path+'bjnw_shi_nei.bak';
backup database bjnw_shi_nei to disk= @fullPath WITH  INIT



DECLARE @OLDDATE DATETIME
SELECT @OLDDATE=GETDATE()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\北京农污项目\数据库自动备份',N'',@olddate