‘数据库’ 分类下的所有文章
2014四月26

自动安装sqlserver2012及补丁

1,配置ini文件,即是手工安装中上一步下一步中做的设置
ini文件关键点说明:
SQLSYSADMINACCOUNTS=".\Administrator"指明了使用当前机器的计算机名,这样就不会因为第一台机器的计算机名复制到其他机器里

FEATURES=SQLENGINE,REPL…:指定要安装、卸载或升级的功能。

安装全部功能
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK
安装除了Analysis Services,Reporting Services – Native,Reporting Services – SharePoint,Reporting Services Add-in for SharePoint Productsa,Integration Services功能
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,DQC,CONN,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK,MDS
 
对应列表如下:Feature list
 
 

2,执行cmd命令安装

mkdir "d:\Program Files\Microsoft SQL Server"
mkdir "d:\Program Files (x86)\Microsoft SQL Server"
mkdir "d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"
mkdir "d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"
.\SQLFULL_x64_CHS\Setup.exe /qs /ACTION=Install /SAPWD="passwordxxxxxxxxxxxxxxx123!$^"  /PID="FH666-Y346V-7XFQ3-V69JM-RHW28"  /IACCEPTSQLSERVERLICENSETERMS   /ConfigurationFile="sql2012.ini"

 
/q:完全没有界面
 
/qs:有界面,界面只是作为显示进度的用途
 
/IACCEPTSQLSERVERLICENSETERMS:接受许可条款
 
/PID:产品密钥  指定 SQL Server 版本的产品密钥。如果未指定此参数,则将使用 Evaluation。

/SAPWD:指定sa的密码

3,安装补丁
补丁.exe /allinstances  /qs /IACCEPTSQLSERVERLICENSETERMS 

参考:您还在用下一步下一步的方式安装SQLSERVER和SQLSERVER补丁吗?

4,ini文件如下

;SQL Server 2012  Configuration File
;安装到D盘,不安装report services,ssis等不用的功能

[OPTIONS]

; 指定安装程序的工作流,如 INSTALL、UNINSTALL 或 UPGRADE。这是必需的参数。 

ACTION="Install"

; 尚未定义命令行参数 ENU 的详细帮助。 

ENU="False"

; 用于控制用户界面行为的参数。有效值对于完整 UI 为 Normal,对于简化的 UI 为 AutoAdvance,为 EnableUIOnServerCore 则跳过 Server Core 安装程序 GUI 块。 

;UIMODE="Normal" 

; 安装程序将不会显示任何用户界面。 

QUIET="False"

; 安装程序将只显示进度,而不需要任何用户交互。 

QUIETSIMPLE="False"

; 指定 SQL Server 安装程序是否应发现和包括产品更新。有效值是 True 和 False 或者 1 和 0。默认情况下,SQL Server 安装程序将包括找到的更新。 

UpdateEnabled="False"

; 指定要安装、卸载或升级的功能。顶级功能列表包括 SQL、AS、RS、IS、MDS 和工具。SQL 功能将安装数据库引擎、复制、全文和 Data Quality Services (DQS)服务器。工具功能将安装管理工具、联机丛书组件、SQL Server Data Tools 和其他共享组件。 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,DQC,CONN,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK,MDS

; 指定 SQL Server 安装程序将获取产品更新的位置。有效值为 "MU" (以便搜索产品更新)、有效文件夹路径以及 .\MyUpdates 或 UNC 共享目录之类的相对路径。默认情况下,SQL Server 安装程序将通过 Window Server Update Services 搜索 Microsoft Update 或 Windows Update 服务。 

UpdateSource="MU"

; 显示命令行参数用法 

HELP="False"

; 指定应将详细的安装程序日志传送到控制台。 

INDICATEPROGRESS="False"

; 指定安装程序应该安装到 WOW64 中。IA64 或 32 位系统不支持此命令行参数。 

X86="False"

; 指定共享组件的安装根目录。在已安装共享组件后,此目录保持不变。 

INSTALLSHAREDDIR="d:\Program Files\Microsoft SQL Server"

; 指定 WOW64 共享组件的安装根目录。在已安装 WOW64 共享组件后,此目录保持不变。 

INSTALLSHAREDWOWDIR="d:\Program Files (x86)\Microsoft SQL Server"

; 指定默认实例或命名实例。MSSQLSERVER 是非 Express 版本的默认实例,SQLExpress 则是 Express 版本的默认实例。在安装 SQL Server 数据库引擎(SQL)、Analysis Services (AS)或 Reporting Services (RS)时,此参数是必需的。 

INSTANCENAME="MSSQLSERVER"

; 为您已指定的 SQL Server 功能指定实例 ID。SQL Server 目录结构、注册表结构和服务名称将包含 SQL Server 实例的实例 ID。 

INSTANCEID="MSSQLSERVER"

; 指定可以收集 SQL Server 功能使用情况数据,并将数据发送到 Microsoft。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 

SQMREPORTING="False"

; 用于授予分布式重播控制器服务权限的 Windows 帐户。 

CTLRUSERS=".\Administrator"

; 分布式重播控制器服务使用的帐户。 

CTLRSVCACCOUNT="NT Service\SQL Server Distributed Replay Controller"

; 分布式重播控制器服务的启动类型。 

CTLRSTARTUPTYPE="Manual"

; 分布式重播客户端服务使用的帐户。 

CLTSVCACCOUNT="NT Service\SQL Server Distributed Replay Client"

; 分布式重播客户端服务的启动类型。 

CLTSTARTUPTYPE="Manual"

; 分布式重播客户端服务的结果目录。 

CLTRESULTDIR="d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"

; 分布式重播客户端服务的工作目录。 

CLTWORKINGDIR="d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"

; 指定是否可将错误报告给 Microsoft 以便改进以后的 SQL Server 版本。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 

ERRORREPORTING="False"

; 指定安装目录。 

INSTANCEDIR="d:\Program Files\Microsoft SQL Server"

; 代理帐户名 

AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"

; 安装后自动启动服务。  

AGTSVCSTARTUPTYPE="Automatic"

; CM 程序块 TCP 通信端口 

COMMFABRICPORT="0"

; 矩阵如何使用专用网络 

COMMFABRICNETWORKLEVEL="0"

; 如何保护程序块间的通信 

COMMFABRICENCRYPTION="0"

; CM 程序块使用的 TCP 端口 

MATRIXCMBRICKCOMMPORT="0"

; SQL Server 服务的启动类型。 

SQLSVCSTARTUPTYPE="Automatic"

; 启用 FILESTREAM 功能的级别(0、1、2 或 3)。 

FILESTREAMLEVEL="0"

; 设置为 "1" 可为 SQL Server Express 启用 RANU。 

ENABLERANU="False"

; 指定要用于数据库引擎的 Windows 排序规则或 SQL 排序规则。 

SQLCOLLATION="Chinese_PRC_CI_AS"

; SQL Server 服务的帐户: 域\用户或系统帐户。 

SQLSVCACCOUNT="NT Service\MSSQLSERVER"

; 要设置为 SQL Server 系统管理员的 Windows 帐户。 

SQLSYSADMINACCOUNTS=".\Administrator"

; 默认值为 Windows 身份验证。使用 "SQL" 表示采用混合模式身份验证。 

SECURITYMODE="SQL"

; 将当前用户设置为 SQL Server 2012 Express 的数据库引擎系统管理员。 

ADDCURRENTUSERASSQLADMIN="False"

; 指定 0 禁用 TCP/IP 协议,指定 1 则启用该协议。 

TCPENABLED="1"

; 指定 0 禁用 Named Pipes 协议,指定 1 则启用该协议。 

NPENABLED="0"

; Browser 服务的启动类型。 

BROWSERSVCSTARTUPTYPE="Disabled"

; 添加输入参数 FTSVCACCOUNT 的描述 

FTSVCACCOUNT="NT Service\MSSQLFDLauncher"
2014四月23

查询指定变量的表sqlcmd

sql server 评论关闭
0,优势:可在查询编辑器下引用变量,执行cmd命令
1,查询编辑器开启sqlcmd:在“查询”菜单中,单击“SQLCMD 模式”
2,编写脚本

:setvar table "person"

SELECT *
FROM [$(table)]

!!DIR
GO

使用查询编辑器编辑 SQLCMD 脚本

2014四月12

SQLServer2012 IntegratiionServices高级教程

sql server 评论关闭
比SQL强悍之处:
1,可以使用脚本处理任务,比如C#
2,数据源可以多源,如web service
3,FTP传送数据,生成文件并邮件、FTP
4,可以通过转换,将不标准的数据传到另一路径处理,标准数据按正常路径执行,通过unionall转换来合并数据
5,若是同一sqlserver中,用sql job完全可以使用,若是数据源来自不同服务器,有的是文本这些用SSIS效率会更高, 第8章有个典型例子
      windows services的地方都可用ssis来替换,windwos servies的脚本ssis中的脚本任务可完整实现 ,并且ssis放在agent job中执行,可以按多种执行计划来调度,还有错误报告机制,并不用再费心对每个windows services写错误机制,错误可写到表中,这样通过数据库连接就能看到错误信息
 6,通过数据转换与查找,利用提供的现有函数,简单完成复杂的数据抽取
7,可替换:windows services,账单上传,本质是涉及到多数据源的数据操作,就是SSIS的用武之地
8,可在asp.net调用包执行数据
9,当出错是,可进行配置,将错误信息自动记录到表中
 
 

第二章 

1,在sqlserver上通过向导,选择将包保存到文件,最后可生成.dtsx文件,打开sqlserver data tools2012,新建一个项目,可将前面生成的包copy到项目的包中,进行编辑
2,执行包:在解决方案管理器中右击该包,并选择执行包
3,控制流中执行进程任务可用来执行exe这些项目
4,数据流中有两个箭头,绿字的表示本次执行成功后执行哪一个,而红色的表示执行失败后执行哪一个
5,任务流的箭头上双击,可以指定按哪种方式转到下一个,若表达式,成功,失败等,其中完成约束表示无论前一个成功与否,都执行下一个。
6,选择多个,右键可以分组
7,批注,在空白处点右键,批注
 
第三章 SSIS任务
1,属性
  • DelayValidation:设置为True,则不会对任务中的属性集进行验证
  • Disable:禁用任务
  • FailPackageOnFailure:设置为True时,只要单个任务失败,整个包就会失败
  • FailParentOnFailure:设置为True时,单个任务失败,该任务的上层如包或容器也会失败
2,任务类型
  • 控制流中执行进程任务可用来执行exe这些项目
  • 脚本任务可以通过C#来编写代码,设置ReadOnlyVariables和WriteVariables属性,可将SSIS变量以逗号分隔的列表形式传送到脚本
  • 文件系统任务,复制移动目录等
  • FTP任务,获取或发送文件
  • Web服务任务:接收WebService数据
  • XML任务:可以用来验证XML任务是否正确
  • 大容量插入任务:将来自文本的数据插入到关系数据库中,类似于sqlserver中的导入文本文件
  • SQL任务:执行sql,sql脚本,存储过程
  • 发送消息任务:与ssb通信等
  • 发送邮件任务
  • WMI数据读取器任务:读取日志信息
  • WMI事件观察器任务:在OS某个指定事件触发后执行相关任务,如某目录下新增了文件后再执行
  • SMO任务
    • 传输job任务
    • 传输登陆名任务
    • 传输错误信息任务
    • 传输SQLServer对象任务

第四章 容器
1,容器:可以视为一个小型的包,将变量范围缩小到一个容器内,注意容器内的任务不能与容器外的任务相连接
2,For循环容器:遍历执行,直到满足某个条件跳出
3,Foreach循环容器,遍历一个对象集合,如文件,对象,通过定义变量来与索引匹配,在属性变量映射中设置【debug不成功】
4,Foreach dao容器,将Foreach的属性设置为ADO即可
 
第五章 数据流
1,数据流主要在内存中工作,提供了更快的速度,虽然也需要更好的内存,以空间换速度,数据从源中流出,默认大小为10M或1万行(以先达到为准)开始进行转换
2,数据查看器:在运行时查看数据,方法,右击一个路径,选择“启用数据查看器”,默认显示1万行
3,OLE DB源:可以在错误输出时,选择错误、截断时执行忽略还是继续执行
4,平面文件源:
  • 文本限定符,若指定按逗号分隔,以引号文本限定符包含中的逗号将能被忽略
  • 默认情况下,SSIS将扫描文件中的前100条记录,从而猜测合适的数据类型
  • FastParse选项,对数据进行类型检查,一般会增加20%~30%的时间来验证,若文件格式比较整齐的话,可以忽略,在右键-高级编辑器-输入属性和输出属性-平面文件源输出-输出列 ,右边FastParse
5,多平面文件连接管理器:在连接管理器中右键添加 

6,目标:目标与源的区别在于在目标中有一个映射属性页,来指定数据来源
7,OLE DB目标:
  • 每批行数,指定了在每次发送到目标的批处理有多少行
  • 最大提交大小,指写了在在发出提交语句之前批处理的大小,可设置为一个小的数字,以便快速提交
  • 表锁:在目标表中设置一个锁,以便提高加载速度,但若有多线程,此选项将会耗时
8,原始文件目标:通常作为部分转换数据的中间点,以本机格式编写,读取速度非常快。

9,SQLServer目标:它需要用内存中的一个接口,需要将包运行在sqlserver所在的服务器中才行
10,转换
  • 聚合转换,对源数据进行聚合
  • 有条件拆分转换:相当于加了一个if,else,比如当某字段首字母为“A”时,执行一个路径,其它执行另一路径
  • 数据转换:对字段进行convert,转化为指定类型
  • 派生列转换:对数据源增加新列,此新列可以为一计算值
  • 查找转换:在数据流和第二个数据集之间执行join
  • 行计数转换:记录转换的行数,需要定义一个变量存储行计数的结果
  • 排序转换:对指定数据进行排序,是一种异步转换,比较慢,若想排序的话,在TSQL语句中,高级编辑器-输入属性和输出属性中指定IsSorted为True
  • UNION转换:对多个数据源执行UNION,将源数据都指向此转换即可
  • 字符映射表转换:对列进行字符操作,如大小写,字节反转,简繁体转换
  • 复制列转换:对列进行复制,是派生列转换的简化版
  • 导出列转换:将数据库中BLOB类型的字段转化成物理文件,它要求输入有两个,一个是BLOB类型字段,一个是生成的对应文件
    • 允许追加,指定了如果文件存在,是否应将输出附加到现有文件
    • 强制截断,当文件存在时就重写
    • 编写字节顺序标记,是否将字节顺序记录文件
  • 导入列转换:将文件导入到数据库
  • 模糊查找转换:比如对于文本,将它的列与数据的列进行相似匹配,正确的话就替换文本的列,然后存入目标,没有查找转换效率高
  • 模糊分组转换:浏览一组相似的文本进行转换
  • 合并联接转换,对数据进行JOIN,生成结果表
  • 多播转换:将源数据下放到多个目标中,多播转换会向下游的每一个目标发送所有行,而有条件拆分是将满足条件的行输出到下一目标
  • 百分比抽样转换:按百分比抽取数据
  • 行抽样转换:指定行数抽取数据
  • 透视转换:进行行转列
    • 透视键:原来的一列数据作来新数据中的列名
    • 设置键:左边的行值
    • 透视值:据设置键与透视键进行聚合
  • 逆透视:列转行
  • 字词提取转换:提取一字符串字段,对于进行分析,生成各字词的频次表
  • 字词查换转换:类似于查找转换,指定一数据源进行匹配
第六章 使用变量、参数和表达式
1,变量区分大小写,按F4查看属性,其中将EvaluteAsExpression属性设置为True时,通过Expression属性提供的表达式来定义变量,相对于表达式,不过更好的一点是更通用的表达式。
2,2012中提供了参数,可以设置是否敏感,这样ssis可对包进行加密。
3,表达式与T-SQL有部分不一样:IsNull 若为null,则为true,而不是tsql中的二元。datepart要加引号,如:DATEPART( "mm",getdate())
4,字符串要有双引号,而不是单引号

5,在SSIS中变量不能被设置为NULL,都有默认值 ,如datetime的默认值为12/30/1899
 
第10章 加载数据仓库
1,事件查看器:对数据质量进行分析,将数据事件探查器任务放到控制流,属性快速配置文件中指定数据源,常规中指定目标,执行,就能生成xml分析文件,用sqlserver中Integration Services下的数据配置文件查看器就能查看此XML文件
     注此工具在2012才开始提供,在查看器中,树的右上角可按列显示数据
     使用说明:
  • 候选键配置文件,显示 哪些列是唯一的
  • 列Null比率,验证数据完整性
  • 列长度分布配置文件,查看最大最小长度
  • 函数依赖关系配置文件:看列与列是否1对1的关系
      
 
第22章 SSIS部署
1,在sqlserver上有个Integration Services目录,要建立一个SSISDB,在ssis设计时,指定此部署包即可,部署上后,通过job指定要运行的包,设定好时间就可运行
  • SSISDB是一个数据库,在数据库里面能找到此数据库,可看到他的表
  • 在Integration Services目录,项目里右键属性可看到版本,历史版本都有记录,可以还原,在SSISDB属性上有个选项可设置保留的最大版本数
  • 部署有两种,项目部署模型,2012新的默认的,包部署模型,历史兼容
  • 部署到sqlserver上的包可以通过tsql来执行,在包上右键执行,生成脚本,就能得到
  • 环境是参数的集合,单独抽取出来方便调用,引用环境就是在包上右键-配置-引用即可
  • 报表已经内部生成好,在包上右键-报表,可看到包的运行情况,详细的运行到每一步的情况及报表
  • 若报表不足够,可在这里下载或者自己开发http://ssisreportingpack.codeplex.com/,下载后用ssrs打开修改数据源,在自定义报表中指定即可
 
2014二月27

复制分发常见问题

sql server 评论关闭

1、
问题描述:
分发代理无法在“C:Program FilesMicrosoft SQL Server100COM”目录中创建临时文件。系统返回的错误代码为 5。 (源: MSSQL_REPL,错误号: MSSQL_REPL21100)
 
解决办法:
在订阅服务器上的SQLSERVER安装根文件夹“C:Program FilesMicrosoft SQL Server100COM”,为分发代理账户(xxxdistagentadmin)添加修改的权限。
 
 
2、
问题描述:
正在连接到分发服务器,代理消息代码 14080。 远程服务器不存在,未被指定为有效的发布服务器,或您无权查看可用的发布服务器。
 
解决办法:
在分发服务器上创建分发代理登录名和账户;在发布属性下的访问列表中,添加分发代理账户。
 
 
3、
问题描述:
所有者 xxxsql_admin_cc (拥有作业 )没有服务器访问权限。
 
解决办法:
将该作业的所有者更改为sa,重启分发代理;在发布属性下的访问列表中,添加分发代理账户。
 
 
4、
问题描述:
由于出现操作系统错误 3,进程无法读取文件。 (源: MSSQL_REPL,错误号: MSSQL_REPL20024)
 
解决办法:
将复制模式设置为推送订阅;在FTP共享文件夹上添加分发代理启动账户和FTP用户读取权限,并共享该文件夹。
 
 
5、
问题描述:
对带有“$”符号的共享文件夹的访问被拒绝。
 
解决办法:
更改分发服务器属性,重新设置网络共享文件夹路径为不带“$”;在发布属性中,不使用默认快照文件夹,将快照生成到其他有访问权限的文件夹中; 修改ftp站点属性,允许任何人访问,重启IIS服务。
 
6、
问题描述:
用服务器名连接服务器的时候报无法生成SSPI上下文,但是用IP地址可以用客户端登陆。
 
解决办法:
在hosts文件中增加服务器的IP地址和服务器名。
 
7、
问题描述:
应用复制的命令时在订阅服务器上找不到该行(源: MSSQLServer,错误号: 20598)
 
解决办法:
  (1)修改配置文件,然后新建一个新的配置文件,新建后修改-SkipErrors这一项,在“值”栏后输入20598,确定,然后使用新建的这个配置文件。重启代理。 
(2) 使用sp_setsubscriptionxactseqno 存储过程,跳过一个或多个导致错误的事务。
(3)在发布中取消该表的复制,然后再添加复制。
 
 
8、
问题描述:
快照代理无法启动,对路径“”的访问被拒绝。
 
解决办法:
更改快照代理启动账户为有权限的账户,或更改快照代理作业为sa ,主要是发布包中有推送订阅,也有请求订阅。
 
 
9、
问题描述:
代理消息代码 20033。 进程无法从 FTP 站点检索文件。
 
解决办法:
主要是FTP问题,发布和订阅要通过公网连接,不采用请求订阅,改为推送订阅模式。
 
 
10、
问题描述:
消息 21618,级别 16,状态 1,过程 sp_MSreplagentjobexists,第 116 行
发布服务器不存在。若要查看发布服务器的列表,请使用存储过程 sp_helpdistpublisher。
解决办法:
发布的数据库是拷贝其他数据库的,包括复制的一些系统表也复制过来了,在发布库上执行如下代码:
Exec sp_removedbreplication @dbname =<dbname>
 
 
11、
问题描述:
尝试的命令:if @@trancount > 0 rollback tran(事务序列号: 0x000019AB0001E7A7019200000000,命令 ID: 7275),进程无法从 FTP 站点“DB-DISTBACK01”检索文件“”。 (源: MSSQL_REPL,错误号: MSSQL_REPL20033)
 
解决办法:
使用sp_change_subscription_properties更改订阅属性,不使用ftp。
 
 
12、
问题描述:
进程无法在“”上执行“sp_repldone/sp_replcounters”。 (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
 
解决办法:
在发布数据库上执行 sp_replflush存储过程。
 
 
13、
问题描述:
进程无法在“”上执行“sp_replcmds”。
 
 
解决方法:
权限问题:将数据库所有者改为sa或其它配置分发时指定的账号。即账号要在数据库上有dbo_owner权限
ALTER AUTHORIZATION ON DATABASE::[OA] TO [sa]
 

2014一月19

对象依赖关系查询

sql server 评论关闭
--查询指定对象引用的对象或列,如存储过程引用的表
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
    o.type_desc AS referencing_desciption,
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
    referencing_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name,
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'bdorder_sel');

--查询引用指定对象的对象,如引用某表的存储过程
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name,
    o.type_desc AS referencing_desciption,
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name,
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID(N'dbo.bdorder');

--查询引用其它数据库的对象,连接服务器
SELECT OBJECT_NAME (referencing_id),referenced_server_name,referenced_database_name,
    referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name ='OA' AND referenced_server_name='172.16.88.231'

sys.sql_expression_dependencies

2014一月9

sqlserver进服务器

sql server 评论关闭

sqlserver的sa密码记得,但windows的忘记了,通过如下方法增加windows权限账号。

EXEC [sys].[sp_configure] @configname = 'show advanced options', -- varchar(35)
@configvalue = 1 -- int
RECONFIGURE WITH override
GO
--如果没有就开启
EXEC [sys].[sp_configure] @configname = 'Ole Automation Procedures', -- varchar(35)
@configvalue = 1 -- int
RECONFIGURE WITH override
GO

--添加一个hack用户,所属用户组为Administrators
DECLARE @shell INT
EXEC SP_OAcreate 'wscript.shell',@shell out
EXEC SP_OAMETHOD @shell,'run',null, 'net user hack2 123 /add'
EXEC SP_OAMETHOD @shell,'run',null, 'net localgroup Administrators hack2 /add'
2013十二月16

增加新列赋值问题

sql server 评论关闭

指定了默认值
若增加了 not null限制,则自动将所有的新列值赋予默认值。
或者不加 not null限制,但指定 with values修饰,也会自动将所有的新列值赋予默认值。
只有指定了默认值,既没有not null,有没有with values时,才只将新insert的列赋值。
参考:column_definition

USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.test','U') IS NOT NULL
    DROP TABLE dbo.test;
GO

--Create the test table
CREATE TABLE dbo.test ( Col1 INT );
GO

--Insert some test data
INSERT INTO dbo.test ( Col1 )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5;
GO

--Add a new, NOT NULL column with default constraint
--Use with WITH VALUES clause to also populate the columns
ALTER TABLE dbo.test
    ADD col2 INT NOT NULL DEFAULT(0),
    col3 INT  DEFAULT(0) WITH VALUES,
    col4 INT DEFAULT(0)
    ;
GO

SELECT *
FROM test
2013十二月15

域名解析数据库

sql server 评论关闭

需求:数据库服务器经常变动,每次管理的时候都要更改IP,特别在管理人员比较多的时候,大家都在改IP,比较复杂一些。
解决:做一个域名,解析到服务器IP,大家访问数据库时用域名而不是IP访问。这样,在服务器IP更改时,只需将域名解析更改一下,就全部都更改了。
注:sqlserver会获取域名解析后的IP,当作IP访问,只是多增加了一道解析操作。

2013十二月13

查询谁删除了表数据

sql server 评论关闭
SELECT  AllocUnitName,
[Transaction SID], SUSER_NAME([Transaction SID]) Who,
Operation,[Transaction Id],[Transaction SID],
[Transaction Name],[Begin Time],[End Time],[SPID],Description
FROM fn_dblog(NULL, NULL) 
WHERE Operation = 'LOP_DELETE_ROWS'
AND AllocUnitName NOT LIKE 'sys.%' AND AllocUnitName NOT LIKE 'Unknown Alloc Unit%'
 
2013十二月11

SQLServer job日志丢失

sql server 评论关闭

查看job日志,发现只有当天的部分,其它的没有了。
原因是默认job日志记录是1000条,修改下即可。
SQLServer代理-属性-历史记录中调整,或者使用如下sql

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000
GO
2013十二月6

检查超过7天未备份的db

sql server 评论关闭
SELECT
dbs.[name] AS DatabaseName
FROM    master.sys.databases dbs
where dbs.database_id <> 2
AND dbs.[name] NOT IN
	(	SELECT
		bus.database_name as 'name'
		FROM msdb.dbo.backupset bus
		INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
		WHERE
		dbs.database_id <> 2
		AND dbs.is_in_standby = 0
		AND dbs.source_database_id IS NULL
		AND dbs.[state] <> 1
		AND bus.backup_finish_date >= DATEADD(d, -7, GETDATE())

	)
2013十一月28

主键与计算列

sql server 评论关闭

--主键表
SELECT TOP 50 *
FROM sys.key_constraints

--没有主键的表
SELECT TOP 50 *
FROM sys.tables a
WHERE a.object_id NOT IN(
	SELECT parent_object_id
	FROM sys.key_constraints
)

	SELECT TOP 50 *
	FROM sys.parameters 

--查询计算列
SELECT TOP 50 *
FROM sys.computed_columns
2013十一月28

查询有自增长列的表及目前自增长值

sql server 评论关闭
Select  Object_Name(id.object_id) As [table_name]
    , id.name As [column_name]
    , t.name As [data_type]
	,seed_value
	,increment_value
    , Cast(id.last_value As bigint) As [last_value]
    , Case
        When t.name = 'tinyint'   Then 255
        When t.name = 'smallint'  Then 32767
        When t.name = 'int'       Then 2147483647
        When t.name = 'bigint'    Then 9223372036854775807
        End As [max_value]
From sys.identity_columns As id
Join sys.types As t
    On id.system_type_id = t.system_type_id
Where id.last_value Is Not NULL
ORDER BY last_value DESC
2013十一月26

sqlserver安全设置

sql server 评论关闭
1,禁止sa
4,域策略阻止非法IP访问
 
2013十一月26

隐藏 SQL Server 数据库引擎的实例

sql server 评论关闭
  1. “SQL Server 配置管理器”中,展开“SQL Server 网络配置”,右键单击“<server instance> 的协议”,然后选择“属性”

  2. “标志”选项卡的“隐藏实例”框中,选择“是”,然后单击“确定”关闭对话框。 对于新连接,更改会立即生效。

    隐藏 SQL Server 数据库引擎的实例

2013十一月23

[Except]How to compare the rows of two tables and fetch the differential data.

In this blog I would like to demonstrate a scenario where users want to move the changes between the tables in two different databases.
Let’s say we would like to compare and move the changes between the databases for some tables using T-SQL
The below example talks about moving the changes between the two databases in the same instance However the same can be extended across instances if you use linked server or SSIS packages.
Also we can write queries to move the DML changes from source to destination and vice versa. Let’s look at the below example

--creating a source database
create database source

--create source  table
use source

create table Product(

Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int ,
location varchar(10))

--create destination database

create database Destination

--create destination table

use Destination

create table Product(

Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int,
location varchar(10) )

--Insert data into source table
use source

insert into product values  ( 1,'rdbms',100,200,'ind')
insert into product values  ( 2,'dbm',20,100,'US')
insert into product values  ( 3,'arp',30,250,'UK')
insert into product values  ( 4,'mqr',40,100,'ind')
insert into product values  ( 5,'ttp',50,200,'us')

-- EXCEPT returns any distinct values from the left query that are not also  found on the right query.
--The below query gives us difference between sourec and destination
-- we can use except ket word to look at selected columns or entire table

select * from source.dbo.product

except

select * from [Destination].dbo.product

--updating destination table with the changes from source

insert into [Destination].dbo.product
select * from source.dbo.product
except
select * from [Destination].dbo.product

-- We see that the destination is populated with all the rows from source

select * from [Destination].dbo.product

--Now lets update the row in the source and see how it works

update source.dbo.product
set pname='sql'
where pid =1
--run the below query
select * from source.dbo.product

except

select * from [Destination].dbo.product

-- the result gives us the only row which was changed in source

-- loading the deiffrences to a temp table
select * into #temp from source.dbo.product

except

select * from [Destination].dbo.product

--updating the destination with changes

update [Destination].dbo.product
set [Destination].dbo.product.pname= #temp.pname
from #temp where #temp.pid= [Destination].dbo.product.pid

--lets run the statement to see the difference between these tables

select * from source.dbo.product

except

select * from [Destination].dbo.product

--lets see how the delete works

delete from source.dbo.product where pid= 2

-- to see the rows which were deleted at source or inserted at destination only
select * from [Destination].dbo.product
except
select * from source.dbo.product
--based on the application logic either we will insert it back in the source or delete from dest

--lets say we want to delete from dest as well ,

select * into  #temp from [Destination].dbo.product
except
select * from source.dbo.product

delete from [Destination].dbo.product where pid in ( select pid from #temp)

-- Now lets see that difference between the tables
select * from [Destination].dbo.product
except
select * from source.dbo.product

来自:How to compare the rows of two tables and fetch the differential data.

2013十一月21

sqlserver内存设置

sql server 评论关闭
SQL Server会把用过的数据放入cache,以便加速数据的访问。如果没有其它进程竞争,那么SQL Server会使用几乎全部的内存,直到有其它进程需要内存,才会释放内存。 
在并发度不大的情况下,不用去设置SQL Server的内存是没什么问题的。但是在高并发度/大数据量的情况,这样可能会导致大量的页交换。推荐的做法是,设置SQL Server使用75%的内存,比如64G内存的服务器,我们一般设置SQL的最大内存为48G,然后观察SQL的Buffer Cache Hit Ratio,如果低于99%,再增加2G内存,直到Buffer Cache Hit Ratio高于99% 
查询Buffer Cache Hit Ratio代码 
 
Sql代码 
SELECT  
(CAST(SUM(CASE LTRIM(RTRIM(counter_name))    
WHEN 'Buffer cache hit ratio'    
THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /   
CAST(SUM(CASE LTRIM(RTRIM(counter_name))    
WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER)ELSE NULL END) AS FLOAT)) * 100   
AS BufferCacheHitRatio   
FROM sys.dm_os_performance_counters    
WHERE LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND    
[counter_name] LIKE 'Buffer Cache Hit Ratio%' 
2013十一月20

该表已为了复制而被发布,所以无法重命名。

场景:从发布库上将一数据库移到另一服务器,在对表改名时提示“该表已为了复制而被发布,所以无法重命名。”
原因:移的数据库原来参与了复制分发,需要在新服务器上去掉。
方法:sp_removedbreplication 'mydb'

sp_removedbreplication
该存储过程在发布服务器的发布数据库中或在订阅服务器的订阅数据库中执行。 该过程将从执行它的数据库中删除所有复制对象,但它不会从其他数据库(例如,分发数据库)中删除对象
只有当其他删除复制对象的方法都失败后,才应当使用此过程。

2013十一月16

即时设置数据库状态

sql server 评论关闭

在设置数据库状态时
with子句忽略,则当数据库中存在任何锁时,ALTER DATABASE 语句将无限期等待。
with NO_WAIT ,指定若有事务还没有提交,则立即失败,不再进行下面的操作
with ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:指定是在指定秒数之后回滚还是立即回滚。

注意:并非所有数据库选项都使用 WITH <termination> 子句,也不是所有数据库选项都能结合其他选项指定。

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

ALTER DATABASE SET 选项 (Transact-SQL)

2013十一月11

redis:windows连虚拟机

redis 评论关闭

redis不支持windows,而现在的windows版本的redis还不是很成熟。故选择虚拟机下安装centeros,跑redis,在windows下访问。

一、windows连接虚拟机的redis

1,配置vmware的转发
用本机的某个端口转发到虚拟机上的端口

http://www.server110.com/vmware/201309/1703.html

 
2,关闭虚拟机上的防火墙
http://os.51cto.com/art/201003/192193.htm

相关:

service stack wiki

https://github.com/ServiceStack/ServiceStack.Redis/wiki

 
service stack 操作方法

http://www.cnblogs.com/daizhj/archive/2011/02/17/1956860.html

 
例子:利用redis建立一个博客

https://code.google.com/p/servicestack/wiki/DesigningNoSqlDatabase

 
Redis工具篇
Redis Admin UI

http://www.servicestack.net/mythz_blog/?p=381