SQL Server资料收集(不断更新中…)
本帖最后由 roy_88 于 2011-08-29 10:36:48 编辑 一、SQL Server访问时常见错误
1.1、SQL Server不存在或访问被拒绝
（1）、能否PING通服务器（检查网络）
基础网络知识：
序号 目标现象 结果
1 127.0.0.1能通说明TCP/IP说明没有问题
2 本机网卡IP能通说明TCP/IP已与网卡正确绑定
3对方IP或网关（同一网段）能通说明网线和对方设置都没有问题
4对方IP或网关（同一网段）不通作如下软硬件方面的检查
硬件：
1：网卡问题：插槽坏了，网卡没插好，网卡坏了，网口坏了；
2：网线问题：水晶头，水晶头与网卡的接触不好（劣质水晶头），网线内部断了，或者没有插网线；
软件：
1：网卡驱动；
2：网卡设置（IP，掩码，网关，DNS）；
3：错误的设置路由（win2000/win2003）；
4：防火墙的原因（包括XP自带或者第三方的）；
5：如果TCP/IP协议的[卸载]选项是灰色的，说明WinXP 系统的TCP/IP协议已经被卸载。PING 127.0.0.1的时候就会出现错误,此时无法通过重装TCP/IP协议还原系统的TCP/IP，可以利用NETSH命令的重置TCP/IP协议堆栈功能
格式如下
c:\>netsh
netsh>interface
netsh interface>ip
netsh interface ip>RESET C:\RESETTCPIP.LOG
说明：
NETSH 在重置TCP/IP堆栈的时候必须指定一个记录文件
其中C:\RESETTCPIP.LOG 为一个记录文件.用来记录NETSH 在重置过程中的所有操作
这样重置以后TCP/IP协议就可以回复为正常时候的状态了。
6：如何自动分配IP
用ipconfig /release,ipconfig /renew命令看看能不能获取IP，可运行ipconfig/all查看，如果显示了DHCP Server的地址，而ip地址仍然为0.0.0.0，那么就运行"services.msc",确保[DHCP Client]服务已经启动。
（2）、能否TELNET通服务器（检查SQL Server服务及端口）
运行"services.msc"，确保服务器上TELNET服务启用的情况下，运行telnet 192.168.0.1 1433，1433是SQL Server 2000对于TCP/IP的默认侦听端口。如果有问题，则提示“……无法打开连接,连接失败"。
如果这一步有问题，应该检查以下选项：
2 服务器是否启动了SQL Server 2000服务；
2 检查服务器端的网络配置，在服务器上打开：开始>程序>Microsoft SQL Server>服务器网络实用工具，检查是否启用TCP/IP协议，以及TCP/IP协议的端口是否与上面TELNET时的端口一致。同样，如果存在防火墙的话，也要打开该端口。
2 检查服务器是否在侦听SQL Server的TCP/IP端口：在服务器命令行下面输入netstat -a -n 或者是netstat -an，在结果列表里看是否有类似 tcp 127.0.0.1 1433 listening 的项。如果没有，则需要给SQL Server 2000打上至少sp3的补丁。其实在服务器端，可以按照《SQL Server 2000 基础（1）——安装》中的方法查看当前补丁的版本。
如果以上都没问题，再次TELNET测试，将会看到屏幕一闪之后光标在命令行窗口左上角闪动，这表明TELNET测试成功。
但有的时候TELNET通了，上面的检查全正常但还是报这个错，后来我发现用JDBC可以连接上，于是，就猜想可能是ODBC的原因（查询分析器用的是ODBC驱动连到数据库的），就装了个MDAC2.8，然后再去控制面板中用ODBC数据源测试，还是不通，无意间改了一下数据源中的客户端配置，将其中的端口由动态改为固定，就可以连接成功了，至于其中原因还没有分析清楚。
（3）、检查客户端的网络配置
在客户端打开：开始>程序>Microsoft SQL Server>客户端网络使用工具。
检查是否启用TCP/IP协议，以及TCP/IP协议的端口是否与服务器端的一致。
附注：在连接本地服务器时，通常使用的是命名管道协议(在服务器网络实用工具里可以看到启用的协议有这个)，默认端口是445，因此在本地能连通是不能说明什么问题的，连接远程服务器是使用的TCP/IP协议。
1.2、无法连接到服务器,用户xxx登陆失败
错误产生的原因有两种：
（1）密码错误，如果密码中有字母，可检查大小写；
（2）由于SQL Server使用了"仅 Windows"的身份验证方式，因此用户无法使用SQL Server的登录帐户(如sa )进行连接，解决办法如下：
企业管理器>服务器>编辑SQL Server注册属性>使用windows身份验证，以连接上SQL Server；
企业管理器>服务器>属性>安全性，以设置身份验证模式为SQL Server和Windows；
重新启动SQL Server服务，用SQL Server用户重新连接。
以上设置可通过修改注册表来实现，如下：
运行REGEDIT，打开注册表；
打开到
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
将右侧窗口的”LoginMode”项的值从1改为2；
重新启动SQL Server服务，用SQL Server用户重新连接。
以下注册表键:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode的值决定了SQL Server将采取何种身份验证模式.
1.表示使用Windows身份验证模式 ；
2.表示使用混合模式（Windows身份验证和SQL Server身份验证）。
1.3、提示连接超时
遇到这个错误，表示客户端已经找到了这台服务器且可以连接，不过是由于连接的时间大于允许的时间而导致出错。
一般当用户在Internet上用客户端连接服务器时，如果网速慢，有可能会导致以上的超时错误。某些情况下，由于局域网的网络问题，也可能会导致这样的错误。
默认情况下，通过企业管理器注册另外一台SQL Server的超时设置是4秒，而查询分析器是15秒，这也是为什么在企业管理器里发生错误的可能性比较大的原因。
要解决这样的错误，可以修改客户端的连接超时设置，如下：
企业管理器中的设置：企业管理器>工具>选项>高级>连接设置>登录超时；
查询分析器中的设置：查询分析器>工具>选项>连接>登录超时。
1.4、SQL Server 2000无法打开1433端口
如果你是WINXP 或WIN2003,那么一定要安装SQL SERVER SP3及以上的补丁。
注意：SP4补丁执行的时候是解压，要在解压后的目录中执行setup.bat才是真正的安装。
二、禁止别的电脑访问本机的SQL Server服务器
（1）设置用户密码（包括WINDOWS和SQL SERVER），不提供用户及密码给不想让其访问的用户；
（2）防火墙中设置限制，只允许指定IP访问你的SQL Server服务器端口(默认1433)，当然，从更为安全的角度来考虑，应该把1433端口改成其他的端口；
（3）如果使用SQL Server 2005，还可以通过端点限制的方法来实现，此方法要求一块专门的网卡，所有可以连接SQL Server的客户端均通过此网卡接入（假设此网卡的IP是192.168.1.1）：
1． 在“SQL Server 配置管理器”的“SQL Server 2005网络配置中”，禁止除TCP/IP之外的所有协议；
2． 使用如下的T-SQL禁止默认的TCP端点
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED
3． 使用如下的T-SQL建立新的TCP端点和授权
USE master
GO
-- 建立一个新的端点
CREATE ENDPOINT [TSQL User TCP]
STATE = STARTED
AS TCP(
LISTENER_PORT = 1433,
LISTENER_IP = (192.168.1.1) -- 侦听的网络地址
)
FOR TSQL()
GO
-- 授予所有登录(或者指定登录)使用此端点的连接权限
GRANT CONNECT ON ENDPOINT::[TSQL User TCP]
TO [public]
完成上述配置之后，只有通过网络地址配置为192.168.1.1的网卡接入的客户端才能访问SQL Server；另外，如果只授予指定登录对端点的连接权限，则只有指定的登录才能接入SQL Server实例。
（4）当SQL Server 2005升级到SP2或者更高的版本的时候，还可以通过服务器级的DDL触发器来实现控制。
执行下面的T-SQL后，将使除IP地址为192.168.1.1之外的客户端连接失败。
USE master
GO
CREATE TRIGGER tr_LoginCheck
ON ALL SERVER
FOR LOGON
AS
IF EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(15)') <> '192.168.1.1'
ROLLBACK TRAN
GO
[解决办法]
一、如何断开用户连接
1.1、停掉SQL SERVER服务
此操作对整个SQL SERVER服务器都生效，有如下几种方法：
（1）SQL Server服务管理器；
（2）控制面板>>管理工具>>服务，或者，运行services.msc；
（3）命令提示行下的net stop mssqlserver /y命令，在SQL SERVER中可这样写：
（4）SQL语句：ShutDown，虽然也是最终停掉SQL SERVER服务，不过这个命令有一些参数可供选择（如：WITH NOWAIT），更加人性化一些，详见SQL SERVER BOL；
或者exec master..xp_cmdshell 'net stop mssqlserver /y'
1.2、设置单用户模式
SINGLE_USER 每次允许一个用户连接到数据库，所有其它用户连接均中断。
中断连接的时间段由 ALTER DATABASE 语句的WITH <termination>子句控制，如果省略了 termination 子句，那么将允许事务自主提交或回滚。
新的连接尝试将被拒绝。即使设置此选项的用户注销，数据库仍保持 SINGLE_USER 模式。这时，其他用户（但只能是一个）可以连接到数据库。
此操作只对当前数据库生效，语句如下：
alter database db_name set single_user
把用户模式设回数据库默认状态，语句如下：
alter database db_name set multi_user
错误方法：
脱机，因为当前有连接在线，而脱机是一个要求无连接的操作，
在企业管理器中给数据库脱机，出现如下错误：
错误5070：如果其他用户正在使用数据库“test”，则数据库状态不能更改
ALTE DATABASE语句失败。
sp_dboption命令失败。
在查询分析器中，在master运行alter database语句，出现如下错误：
ALTER DATABASE 语句失败。
有关数据库选项设置的细节，请见《SQL Server 2000 管理——选项设置》series。
1.3、KILL
此操作对整个SQL SERVER服务器都生效，但可以根据查看的SPID，有选择的KILL掉需要断开的连接，注意KILL是不能取消自己的进程的，如果需要自己也断开，退出连接即可。方法如下：
exec sp_who
--或者
exec sp_who2
--或者
select * from master..sysprocesses
--然后
kill 111 --假如要kill的连接SPID为111
1.4、物理方法
此操作对整个SQL SERVER服务器都生效。
比如：拨掉网线，禁用网卡，关掉所在网络的交换机等一切可以断开网络的方法都行。
二、SQL SERVER连接限制
（1）设置SQL SERVER的最大连接数，就是建立的连接数（connection）
企业管理器>服务器>属性>连接，0为默认设置（即不受限，32767）；
或者
exec sp_configure N'user connections',0 —-需要重启服务生效
注意：这里的连接数是指不大于等于，即小于设置的值。
将连接最大数设为１，重启SQL SERVER，连接时提示“一般网络性错误，ConnectionOpen(PreLoginHandshake())，请验证SQL SERVER 是否在运行...”，这样就出现自己也连不上的情况，最大为1，也就是连接数只能是小于1的整数，那就是0个。解决方法如下：
1、停止sql服务
2、用单用户模式启动SQL SERVER
在命令行模式下输入sqlservr –c -f -m或者输入sqlservr -m
sqlservr.exe在SQL SERVER安装目录下，可以搜一下，我的是在如下路径：
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
3、用osql登录重新配置:
osql /U"sa" /P"密码" /Q"exec sp_configure N'user connections', 0"
4、然后重新启动操作系统。
（2）设置SQL SERVER的最大线程数，连接或者其他SQL SERVER操作（比如select），都是以线程的形式在工作的（多线程）。
有关服务器选项设置的细节，请见《SQL Server 2000 管理——选项设置》series。
（3）假设SQL SERVER服务器只允许最多10个客户端联接，超过１０个则不允许连接，如何控制？
这种情况只出现在两层的时候，三层是通过中间的一个或几个CONN来实现的，不能达到连接限制的，当然可以通过程序以计数的形式来控制，但不是通过SQL SERVER。
（4）查年SQL Server当前连接数
以下各种方法都无法准确地获取到SQL Server的当前ACTIVE连接数，不知道在SQL Server 2005中这样的情况有没有改善。
1、系统变量：
SQL Server提供了一些系统变量返回连接值，个人觉得，很容易产生误解。
@@CONNECTIONS 返回自上次启动SQL Server以来连接或试图连接的次数。
@@MAX_CONNECTIONS 返回SQL Server上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。
2、系统存储过程
SP_WHO /SP_WHO2
提供关于当前SQL Server 用户和进程的信息。可以筛选返回的信息，如下：
列出所有活动的用户：SP_WHO 'active'
列出某个特定用户的信息：SP_WHO 'sa'
3、系统表
sysprocesses
sysprocesses 表中保存关于运行在SQL Server上的进程的信息。这些进程可以是客户端进程或系统进程。连接与进程是一以多的关系，而且这个系统表中的系统是个累计量，而不是最新的统计量。
sysperfinfo
包括一个SQL Server表示法的内部性能计数器，可通过 Windows NT 性能监视器显示。
同样，这里得到的信息也只是所有时间内的累计量。
[解决办法]
一、系统表
数据字典的详细信息请查SQL SERVER BOL，这里仅列出一部分。
1.1、sysservers
1、查看所有本地服务器及链接服务器
select * from master..sysservers
1.2、sysdatabases
1：查询非sa创建的所有数据库
select * from master..sysdatabases
where sid not in(select sid from master..syslogins where name='sa')
--或者
select dbid, name AS DB_NAME from master..sysdatabases
where sid <> 0x01
1.3、sysobjects
1：获取当前数据库中的所有用户表
select name from sysobjects where xtype='U' and status>0
为什么要加status>0，因为表dtproperties，虽然该表的xtype为U，实质上它是系统表。
dtproperties这个表里保存的是关系图，如果没建关系图，就是空的。
注意：这个表只是数据库对象的属性，如果想要看表、索引详细的属性，查看sysindexes。
2：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P' and status>0
为什么要加status>0，是为了去掉当前数据库中的系统存储过程。
注：该系统表中type与xtype的区别是什么？
Type是在SQL SERVER 6.0就有的，xType在SQL SERVER 7.0才出现，Type的保留只是为了向后兼容。每种数据库对象的类型详见SQL SERVER BOL。
1.4、syscolumns
1：获取表或视图的所有字段，存储过程或函数的所有参数
select name from syscolumns where id=object_id('表名')
1.5、sysproperties
1：怎么把SQL SERVER中表设计和表注释读出来
--表的注释全在sysproperties里
select b.name,value from sysproperties as a,sysobjects as b
where a.id=b.id and b.name='表名'
1.6、sysindexes
1：根据聚集索引，快速查询表的行数
SELECT rowcnt,indid FROM sysindexes WHERE id=OBJECT_ID('tableName')and indid < 2
注意：使用这种方法可能不精确，因为系统的统计信息在某些时候不一定是准确的。关于统计的维护(dbcc updateusage())，详见《SQL SERVER 性能优化——查询优化》series。
2：查看索引表信息
select
table_Name=sysobjects.Name,
index_Name=sysindexes.Name,
Type=sysobjects.type,
分配索引页=sysindexes.reserved,
使用索引页=sysindexes.used,
叶子层页=sysindexes.Dpages,
非叶子层页=sysindexes.used-sysindexes.Dpages,
rows=sysindexes.rowcnt
from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id
where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0
注意：若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息
1.7、syslogins
SQL SERVER 服务器的登录信息，比如：sa，有关登录、用户、角色的信息详见《SQL SERVER 2000 管理——安全——用户权限》。
select * from syslogins
select * from sysxlogins
sysxlogins是syslogins的精简版，BOL中没有说明，不推荐使用。
1.8、sysprocesses
1：查看用户进程信息
select spid,uid,syslogins.name,login_time,net_address from sysprocesses,syslogins where sysprocesses.sid=syslogins.sid
2：查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
1.9、sysdepends
1：查看与某一个表相关的视图、存储过程、函数
select * from sysdepends where depid=object_id('表名')
--或者
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
注意：这种查法，只适用在没有with Encryption选项，即没有加密该对象时。
--或者
sp_depends
注意：这个表的统计信息并不准确，没有什么好的办法，查询结果只可用于参考。
1.10、sysmessages
SQL SERVER返回的内部错误都有在这里，可自行定义进行错误的添加，但一般我习惯于新建一个错误的表来定义自己程序中的错误。
select * From master..sysmessages where error=5037
1.11、sysfiles、sysfilegroups
1、查询当前数据库的文件使用情况
select name,filename,size/128 as 'used(M)',case maxsize/128 when 0 then 'no limit' else cast(maxsize/128 as varchar(10)) end as 'total(M)' from sysfiles
2、查询当前数据库的表所在文件组
select distinct a.id,a.name,b.groupid,c.groupname from sysobjects a inner join sysindexes b on a.id=b.id
inner join sysfilegroups c on b.groupid=c.groupid
where a.xType='U' and a.status>0 order by a.name
sysfiles1是sysfiles的精简版，BOL中没有说明，不推荐使用。
二、系统视图
在master数据库中有INFORMATION_SCHEMA和system_function_schema两个用户，它们的登录是<无>，这是系统内置的两个用户。
INFORMATION_SCHEMA拥有自已的视图，在SQL Server 2000中没有被广泛使用，因为很多时候都可以从系统表中得到我们想要的结果，同样到了SQL Server 2005中，被广泛使用的仍然是sys所拥有的视图，SQL Server 2005的相关内容详见后续《SQL Server 2005》series文章。举例如下：
1、查询某个表的哪些字段不允许为空
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE='NO' and TABLE_NAME='stb_User'
2、查询某个表的键约束
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME='stb_User'
注：键约束，是指除了CHECK、NOT NULL外的约束，即PK，FK，UNIQUE，DEFAULT不是约束。
system_function_schema拥有自己的函数，在SQL Server 2000中没有被广泛使用，详见《SQL Server 2000 基础——系统函数》。
[解决办法]
结贴的时候别忘了加分哦！
[解决办法]
顶一下！
[解决办法]
一、系统函数
1、查看信息
1.1、查看编号和名称
select @@SERVERNAME
--SQL SERVER服务器的连接字符串，如：computername\instancename
select @@SERVICENAME
--若为默认实例，则返回MSSQLSERVER；若是命名实例，则返回实例名
改变机器名称后，实例的名称也会跟着变。但是之后会导致本机的内部实例名会跟网络服务名不一致，解决办法如下：
select @@SERVERNAME, SERVERPROPERTY('ServerName')
if serverproperty('servername')<>@@servername
begin
declare @server sysname
set @server=@@servername
exec sp_dropserver @server=@server
--删除这个服务器注册
set @server=cast(serverproperty('servername') as sysname)
exec sp_addserver @server=@server,@local='LOCAL'
--添加当前改过名的服务器注册
end
select HOST_ID() --主机编号
select HOST_NAME()--主机名
select DB_ID('master') --数据库编号
select DB_NAME(1) --数据库名称
select OBJECT_ID('sysobjects') --数据库对象编号
在临时表上用object_id()时，要在临时表前加数据库名
如下：
方法1(错误)
--#temp不是存在当前数据库中的
select object_ID('#temp')
方法2(错误)
--是存在tempdb中，但temdb中没有这个函数
use tempdb
select object_ID('#temp')
方法3(正确)
--MSQL的临时表是存在tempdb中的
select object_ID('tempdb..#temp')
select OBJECT_NAME(1) --数据库对象名称
select File_ID('master') --返回该逻辑文件名的编号
select File_NAME(1) --返回该文件编号的逻辑文件名
select FILEGROUP_ID('primary') --返回文件组编号
select FILEGROUP_NAME(1) --返回文件组名称
select COL_NAME(OBJECT_ID('sysobjects'),1)
--表中列的名称，每个表中列是从…n顺序编号
select INDEX_COL('sysobjects',1,1)
--返回sysobjects中第一个索引第一个键的名称
select APP_NAME() --返回当前使用的工具名称，如：SQL 查询分析器
select @@SPID --返回当前连接在SQL SERVER中的编号
select @@PROCID --在SP中使用，查看自己的ID
1.2、查看登录和用户
select Suser_Sid() --返回登录的SID，可传参，默认返回当前值
select Suser_Sname() --返回登录的名称，可传参，默认返回当前值
select System_User --等价于不带参数的SUER_SNAME()
select User_Id() --返回数据库用户ID，可传参，默认返回当前值
select User_Name() --返回数据库用户名，可传参，默认返回当前值
select User --等价于不带参数的USER_NAME()
select Current_User --等价于不带参数的USER_NAME()
select Session_User --等价于不带参数的USER_NAME()
1.3、查看属性
select Serverproperty('Collation') --查看服务器的属性
select Databasepropertyex('master','Collation') --查看数据库的属性
select Sessionproperty('ANSI_NULLS') --查看当前会话的SET选项
select Objectproperty(1,'IsTable') --查看当前对象的信息
select Filegroupproperty('Primary','IsReadOnly') --查看文件组的属性
select Fileproperty('master','SpaceUsed') --查看文件的属性
select Columnproperty(1,'name','AllowsNull') --查看列属性
select Indexproperty(1,'ncsysobjects','IsClustered') --查看索引属性
select Indexkey_Property(1,2,1,'ColumnId') --查看索引键信息
select Collationproperty('Chinese_PRC_CI_AS','LCID')
--查看排序规则的信息
select Sql_Variant_Property('abc12345','BaseType')
--返回sql_variant类型值的信息
2、NULL相关的
select COALESCE(null,'first',null,'second',null)
--返回众多表达式中第一个非NULL 表达式的值
select NULLIF('123','123')
--在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
select ISNULL(null,'not null value')
--使用指定的替换值替换NULL
select GetANSINull('master')
--查看SET ANSI_NULL_DFLT_ON 的设置
3、字符相关
3.1、获取长度
select DATALENGTH('s我s') --返回表达式占用的字节数
select COL_LENGTH('sysobjects','name') --返回列的定义长度(byte)
3.2、返回有效的 SQL Server 分隔标识符
SELECT QUOTENAME('abc[]def')
--结果集：[abc[]]def]，]]类似于''两个表示一个
--该函数对于SQL注入的防护非常有效，各种前台开发工具也有类似函数
3.3、加密syscomments中的字符内容
select encrypt('原文') --未公开的函数,syscomments中的ctext即用此加密
3.4、加密sysxlogins中的登陆密码
select pwdencrypt('原文') --未公开的函数，sysxlogins中的password即用此加密
select pwdcompare('原文','密文') = 1--相同；否则不相同
3.5、校验和函数
if (select checksum_agg(binary_checksum(*)) from A)
=(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
--此处binary_checksum()也可以换成checksum()，只是算法不同而已，效果一样
4、日期时间
4.1、日期时间转换为字符串
select convert(varchar(20),getdate(),23)
--转换为纯日期，格式如：2007-12-18，BOL无记载
select convert(varchar(20),getdate(),24)
--转换为纯时间，格式如：22:19:20，BOL无记载
4.2、计算日期时间
通常， 我们把日期进行分割，然后利用分割出来的年、月、日等计算或组合出自己所需要的日期，下面的例子巧妙利用0即1900-01-01 00:00:00.000作为基准进行日期计算。
其中设置@@DATEFIRST为7，即星期天是一周的第一天。
--本年的第一天，当前日期与0相差的年数再加给0，默认为1号
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--本季的第一天，当前日期与0相差的季数再加给0，默认为1号
Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--本月的第一天，当前日期与0相差的月数再加给0，默认为1号
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--本周的星期一，当前日期与0相差的周数再加给0，默认为1，星期到星期天的值为1-7
--datepart(dw,getdate())中的返回值表示当前日为一星期中的第几天
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--今天的第一秒，当前日期与0相差的天数再加给0，默认时间为0点
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--去年的最后一天，本年第一天减掉3ms，在Sql Server中datetime是精确到3ms
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
--上个月的最后一天，本月第一天减掉3ms
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
--本年的最后一天，明年第一天减掉3ms
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
--本月的最后一天，下个月第一天减掉3ms
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
--本月的第一个星期一，以本月第6天与0作差
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
4.3、当前日期
CURRENT_TIMESTAMP等同于getdate()，返回类型都为datetime
--与getdate()一样，在函数中也是无法使用
5、标识属性相关
先讲一个概念问题，identity属性与identity函数的区别，前者是列的属性，后者是一个只可用于select into语句的函数，用于生成序号，使用如下：
select identity(int, 1, 1) as Ordinal into newtable from oldtable
SQL Server 中，可以使用SCOPE_IDENTITY()、@@IDENTITY 、IDENT_CURRENT() 来取得最后插入记录的值值，它们的区别在于：
SCOPE_IDENTITY()和@@IDENTITY 都将返回在当前会话的所有表中生成的最后一个标识值，但是，SCOPE_IDENTITY 只在当前作用域内返回值，而@@IDENTITY 不限于特定的作用域。一个作用域就是一个模块——存储过程、触发器、函数或批处理。
也就是说：SCOPE_IDENTITY()只返回当前所运行的insert返回的标识值，对于由当前insert所触发的insert产生的标识值不作返回，在一些强并发的OLTP系统或存在触发器的系统中，往往因为数据库开发人员使用了@@IDENTITY来捕获标识值，会得到比预期标识值大或小的值；
IDENT_CURRENT() 返回的是任何会话和任何作用域中的指定表最后生成的标识值。
-- 创建测试表
CREATE TABLE t1(id int IDENTITY,col int)
INSERT t1
SELECT 1
UNION ALL
SELECT 2
CREATE TABLE t2(id int IDENTITY,col int)
CREATE TRIGGER TR_insert_t2 ON t2
FOR INSERT
AS
INSERT t1 SELECT 3
-- 测试一，当前连接
INSERT t2 VALUES(1)
SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),
[@@IDENTITY]=@@IDENTITY,
[IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),
[IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')
-- 测试二，当前连接
INSERT t1 VALUES(10)
SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),
[@@IDENTITY]=@@IDENTITY,
[IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),
[IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')
-- 测试三，开启一个新连接
SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),
[@@IDENTITY]=@@IDENTITY,
[IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),
[IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')
-- 删除测试表
DROP TABLE t1,t2
可以看出：
IDENT_CURRENT() 始终返回指定表最后的标识值
@@IDENTITY 始终返回当前会话最后的标识值，无论是否在同一个作用域，在测试1、2中，可以看到它返回的是触发器中插入记录的标识值，而在测试3中，因为当前会话无插入记录，所以返回NULL
SCOPE_IDENTITY() 始终返回当前会话同一作用域的标识值，所以在测试1、2中，它返回的值不受触发器的影响，而在测试3中，因为当前会话无插入记录，所以返回NULL
二、系统自定义函数
系统自定义函数都保存在master数据库中以fn开头，调用时from后面加二个冒号(::)，不需要指定所有者，如下：
SELECT * FROM ::fn_helpcollations() --系统所支持的所有的语言
select * from ::fn_trace_gettable('c:\trace_file.trc', default)
--跟踪文件可以通过Profiler工具或运行系统的存储过程sp_trace_create建立
事实上很多系统自定义函数只是简单的调用一个扩展存储过程。
[解决办法]
一、一般存储过程
1、信息查看类
1.1、查看系统对象
过程名
系统表或视图
描述
sp_helpserver
sysservers
查看所有服务器信息
sp_helpdb
sysdatabases
查看所有数据库信息，如：名称、大小
sp_helplogins
syslogins
查看所有数据库用户登录信息
sp_helpuser
sysusers
查看当前数据库用户和角色信息
sp_helprole
sysusers
查看当前数据库用户和角色信息
sp_helprolemember
sysmembers
返回有关当前数据库中某个角色的成员的信息
sp_helpsrvrolemember
spt_values
固定服务器角色成员的信息
sp_helpremotelogin
sysremotelogins
查看远端数据库用户登录信息
sp_helplinkedsrvlogin
sysservers
查看链接服务器的登录
sp_helprotect
sysprotects
查看当前数据库下，对象级用户权限
sp_helpindex
sysindexes
查看当前数据库下某个数据对象的索引信息
sp_helpconstraint
sysconstraints
查看当前数据库下某个数据对象的的约束信息
sp_stored_procedures
sysobjects
查看当前数据库里所有的存储过程和函数
sp_tables
sysobjects
查看当前数据库下所有的表
sp_helptext
syscomments
查看当前存储过程和函数的源代码
sp_configure
sysconfigures
用户设置的每个配置选项在表中各占一行
sp_dboption
spt_values
显示或更改数据库选项
sp_helpfile
sysfiles
数据库中的每个文件在表中占一行
sp_helpfilegroup
sysfilegroups
数据库中的每个文件组在表中占一行
注：
（1）几乎所有sp_help系列的存储过程都有对应的系统表或视图，往往通过过程返回的结果集比系统表或视图更直观，但存储过程返回的结果集并不方便使用select操作；
（2）在调用sp打头的存储过程时，SQL SERVER会默认先去master库中查找，如果不存在再回到当前库中找，这就是为什么当前库中没有以上过程，而我们仍然可以直接运行，同样在自定义存储过程时，最好不要以sp打头，因为这样就多了一次到master中查找的运行成本；
（3）SQL SERVER服务相关的系统过程都在master库中，如：各种ADD、DROP、CHANGE、HELP类的系统过程，另外master库中还有基于DLL实现的扩展存储过程；
SQL Agent服务相关的系统过程都在msdb库中，如：作业、维护计划、日志传送、复制等。
1.2、查看某数据库下表的行数和使用空间信息
sp_spaceused @objname
注：
（1）该过程根据sysindexes表中的信息返回结果，但由于统计信息的更新不及时，可能会有不精确的地方，可以DBCC UPDATEUSAGE来更新sysindexes表中的统计；
1.3、查看数据库里用户和进程的信息
sp_who/sp_who2
注：
（1）查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
（2）推荐使用网友改进的sp_who3过程可以直接看到进程运行的SQL语句，当然也可自己在sysprocesses中根据进程ID查看
（3）SPID 50以内是SQL Server系统内部用的,进程号大于50的才是用户的连接进程
1.4、查看SQL Server数据库里的锁的情况
sp_lock
注：
（1）推荐使用网友改进的sp_who_lock过程检查死锁信息，结合了sysprocesses的进程信息和syslockinfo的锁定信息
1.5、查看 SQL Server、数据库网关或基础数据源的特性名和匹配值的列表。
sp_server_info
2、数据库维护类
2.1、重命名数据库对象
sp_rename ObjectName,NewObjectName –-数据库对象
sp_rename [tableName.colName],'NewColName'--字段
2.2、重命名数据库
exec sp_renamedb 'oldname','newname'
或
alter database dbname modify name='newdbname'
注：
用SQL语句进行数据库重命名时，需要设置数据库为单用户模式，设置详见《SQL SERVER 2000 管理——设置数据库选项》。
2.3、扩展属性（注释）
sp_addextendedproperty
--adds a new extended property to a database object
sp_dropextendedproperty
--removes an extended property from a database object
sp_updateextendedproperty
--updates the value of an existing extended property
::fn_listextendedproperty
/*retrieves the value of an extended property or the list of all extended properties from a database object*/
注：
（1）关于注释这个问题，之前没用SQL语句去添加过，都是在Enterprise Manager里面添加的：
查了一下资料，得知SQL Server 2000中引入了扩展属性，用户可在各种自定义数据库对象上定义这些属性。这些扩展属性可用于存储与数据库对象有关的应用程序或站点特有的信息。
（2）在Oracle中可用COMMENT语句给栏位加注释，如下：
COMMENT ON COLUMN employees.job_id
IS 'abbreviated job title';
删除注释：
COMMENT ON COLUMN employees.job_id IS ' ';
2.4、刷新以优化或防止失效
sp_refreshview
--解决重建视图的问题，还可以通过重建，发现系统中视图的语法错误。
sp_recompile
--若调整了表结构或索引，使存储过程和触发器在下次运行时重新编译。
2.5、消除孤立用户
一般登录被删除，数据库用户会被级联删除，只有在登录被非正常删除时才会存在孤立用户。
sp_change_users_login 'Report'
--列出当前数据库中未链接到任何登录的用户
sp_change_users_login 'Auto_Fix','samenamelogin'
--将当前数据库的 sysusers 表中的用户条目链接到 syslogins 中同名的登录上。sp_change_users_login 'Update_One', 'user', 'login'
--将当前数据库中指定的 user 链接到 login。login 必须已经存在。
2.6、更改数据所有者（数据库级所有者）
大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户。如果想使用当前用户，则可通过如下方法更改所有者。
--更改当前数据库的所有者。
exec sp_changedbowner 'newLogin'
--更改表、存储、试图对象的所有者，'dbo'处可以使用其他的数据库账号，但非系统账号
exec sp_changeobjectowner 'objectname','newowner'
--批量更改
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
3、其他
3.1、动态SQL语句
在T-SQL 应用中，有时我们无法立即确定字段名或表名，我们希望字段名或表名能像变量一样自由变换，如：
declare @s varchar(255)
select @s = 'username'
select @s from tablename
上述代码试图用@s 变量代替字段名userName，但在实际使用中却会换错，同样在表名也无法直接用变量，要解决此问题，我们可以用存储过程：sp_executesql，如下：示例：
declare @s nvarchar(255)
declare @sql nvarchar(255)
select @s = 'userName'
select @sql = 'select ' + @s + ' from tablename'
exec sp_executesql @sql
exec()可以实现与sp_executesql同样的功能，只是后者还支持传参数。如下：
exec sp_executesql N'select * from authors where state=@state and contract=@contract', N'@state char(2), @contract bit', 'CA', 1
注：
（1）在拼完串的时候不要急着exec这个存储过程，而是应该先select @SQLStatement或print @SQLStatement，以检查语句正确性；
（2）对于在语句中需要用到的某些符号要利用转义字符进行转换，比如'号；
（3）本该存在的空格也要作为字符来处理，不能少，防止因为空格而引起语句的不合法；
（4）该过程不支持表变量参数类型；
（5）动态的DDL语法不支持传参，只能像在exec()中一样，通过转化为字符串类型用+连接；
（5）除了@statement外，参数定义也必须是可以隐式转换成ntext 的Unicode 常量或变量，所以这里字符串前面的N 不能省略。
3.2、循环执行
sp_MSForEachDB对服务器上的每个数据库执行三条命令：
@command1：第一个执行的命令
@replacechar：用另一个占位赋替换“?”
@command2：第二个执行的命令
@command3：第三个执行的命令
@precommand：进入循环前执行的命令
@postcommand：循环结束后执行的命令
exec sp_MSForEachDB @command1 = 'use ? exec sp_SpaceUsed'
sp_MSForEachTable对数据库上的每个表执行三条命令：
@command1：第一个执行的命令
@replacechar：用另一个占位符替换“？”
@command2：第二个执行的命令
@command3：第三个执行的命令
@whereand：Where条件语句 (或 Order By 语句)
@precommand：进入循环前执行的命令
@postcommand：循环结束后执行的命令
exec sp_MSForEachTable
@command1 = "Print '?'",
@command2 = "select count(*) from ?",
@whereand = "ORDER BY 1"
注：
其实，这两个过程都是用的游标，完全可以自己实现。
[解决办法]
二、扩展存储过程
注：
（1）不推荐在开发的应用程序中使用未公布的扩展存储过程，因为在以后的SQL SERVER版本中可能会被删除，如果真的要在数据库端实现，可以自己封装COM标准的DLL，建立扩展存储过程，但是要在能保证兼容性与执行效率的前提下；
（2）即便是公布了的扩展存储过程，也要慎用，尤其是要考虑到其安全性。
1、查看信息
1、查看数据库所在机器操作系统参数
exec master..xp_msver
2、获得SQL SERVER的版本号
exec master..sp_MSgetversion
3、得到当前SQL SERVER服务器的计算机名称
execute master..xp_getnetname
4、查看硬盘上的文件、目录、分区及服务器的驱动器
--参数说明:目录名,目录深度,是否显示文件
execute master..xp_dirtree 'c:'
execute master..xp_dirtree 'c:',1
execute master..xp_dirtree 'c:',1,1
--列出指定目录的所有下一级子目录
exec master..xp_subdirs 'C:\'
--查看硬盘分区及可用空间
exec master..xp_fixeddrives
--列出驱动器信息
exec master..xp_availablemedia
name
low free
high free
media type
C:\
2846720
1
2
D:\
-156913664
0
2
E:\
654663680
0
2
F:\
854589440
0
2
G:\
0
0
8
H:\
0
0
8
以字节为单位的空闲空间
软驱(1),硬盘(2),cd-rom(8)
5、列出服务器上安装的所有OLEDB提供的程序
exec master..xp_enum_oledb_providers
6、列出服务器上安装的所有代码页
exec master..xp_enumcodepages
7、列出服务器上配置的dsn（odbc数据源名称）
exec master..xp_enumdsn
8、列出sql server错误日志列表,最后更新时间
exec master..xp_enumerrorlogs
9、列出服务器上所有windows本地组
exec master..xp_enumgroups
10、列出SQL SERVER错误日志的具体内容
exec master..xp_readerrorlog
11、检测文件存在性
execute master..xp_fileexist 'c:\a.bak'
declare @flag int
exec master..xp_fileexist 'c:\abc.bak',@flag out
if @flag=1
begin
print 'exist'
end else
begin
print 'no exist'
end
2、注册表
注：
SQL Server中包含了几个可以访问注册表的扩展存储过程，而且很多人在利用SQL Server进行系统攻击时，往往都会用到这几个扩展存储过程，所以最好在SQL Server中禁用掉它们。
1、读取指定键的值
使用方法：
xp_regread 根键,子键,键值名
实例：
use master
exec xp_regread 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','CommonFilesDir'
2、写入指定键的值
使用方法：
xp_regwrite 根键,子键, 值名, 值类型, 值
实例：
use master
exec xp_regwrite 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','TestValueName','reg_sz','hello'
--注意值类型有两种：REG_SZ 表示字符型,REG_DWORD 表示整型
3、删除某个值
使用方法：
xp_regdeletevalue 根键,子键,值名
实例：
use master
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','TestValueName'
4、删除键,包括该键下所有值
使用方法：
xp_regdeletekey 根键,子键
实例：
use master
exec xp_regdeletekey 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion\Testkey'
--如果没有Testkey这个注册表项，则会提示：系统找不到指定的文件。
5、写入多字符串值（即多行的字符串值）
使用方法：
xp_regaddmultistring 根键,子键,值名,值
--不需要写值类型，固定为REG_MULTI_SZ
实例：
use master
exec xp_regaddmultistring 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','test','hello'
6、删除多字符串值，但不删除这个键值
使用方法：
xp_regremovemultistring 根键,子键,值名,值
实例：
use master
exec xp_regremovemultistring 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','test','hello'
--删除多字符串值
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion','test'
--删除该键值
7、以多个记录集方式返回当前键下所有键值
使用方法：
xp_regenumvalues 注册表根键,项
实例：
use master
exec xp_regenumvalues 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion\run'
8、以行集方式返回当前键下的所有子键
使用方法：
xp_regenumkeys 注册表根键,项
实例：
use master
exec xp_regenumkeys 'HKEY_LOCAL_MACHINE',
'software\Microsoft\Windows\CurrentVersion\run'
3、OLE自动化
注：
SQL SERVER存在着非常严重的安全隐患。sa的密码就像Administrator的密码一样重要。
假如别人获得了数据库超级用户sa的密码，那么就可以在查询分析器里使用下面的SQL语句轻易在服务器上创建一个超级用户：
declare @object int
--创建WScript.Shell
EXEC sp_OACreate 'WScript.Shell',@object OUT
--运行net user iamhacker opendoor /add
--添加一个名字为iamhacker,密码为opendoor的用户
EXEC sp_OAMethod @object,'Run',NULL,'net user iamhacker opendoor /add'
--把iamhacker添加到Administrators组中
EXEC sp_OAMethod @object,'Run',NULL,'net localgroup Administrators iamhacker /add'
--把iamhacker从User组中删除
EXEC sp_OAMethod @object,'Run',NULL,'net localgroup Users iamhacker /del'
其实现方法是SQL SERVER调用COM Automation的强大功能。再配合WSH中的一个对象WScript.Shell的一个方法：Run。
一般SQL SERVER都是配置在SYSTEM的权限下运行的。也就是说，Run所调用的程序也是具有SYSTEM的权限。用Run调用一下NOTEPAD，那么任务管理器下将有用户为SYSTEM的NOTEPA.EXE进程存在。
注：
ActiveX自动脚本的存储过程如下：
sp_OACreate
sp_OADestroy
sp_OAMethod
sp_OAGetProperty
sp_OASetProperty
sp_OAGetErrorInfo
sp_OAStop
另外还有一个扩展过程xp_cmdshell，也存在同样的BUG，建议全部删除。
4、维护扩展存储过程
1、删除扩展存储过程
exec master..sp_dropextendedproc 'xp_cmdshell'
2、新建扩展存储过程
exec master..sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'
3、查看扩展存储过程信息
exec master..sp_helpextendedproc 'xp_cmdshell'
xp_cmdshell屏蔽的方法为：
sp_dropextendedproc 'xp_cmdshell'
如果需要的话，再进行恢复：
sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
如果你不知道xp_cmdshell使用的是哪个.dll文件的话：
sp_helpextendedproc 'xp_cmdshell'
所以，将xp_cmdshell屏蔽后，我们还需要做的是将xpsql70.dll文件进行改名，以防止获得sa的攻击者将它进行恢复。
[解决办法]
间隔一下，小三继续...
[解决办法]
小三继续！
[解决办法]
一、SQL SERVER 2000安装时常见问题
1、配置服务器时中断；
2、注册ActiveX 时中断；
3、显示到%的时候中断；
4、提示：command line option syntax error, type command /? for help，继续安装,最后在配置服务器的时候出现：无法找到动态链接SQLUNIRL.DLL于指定的路径……
5、以前进行的程序创建了挂起的文件操作，运行安装程序前，必须重新启动。
1、2、3的解决办法如下：
（1）建议先备份数据库；
（2）先把SQL Server卸载（卸载不掉也没有关系，继续下面的操作）
（3）把Microsoft SQL Server文件夹整个删掉。
（4）运行注册表,删除如下项：
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
（5）需要的话就重新启动系统
（6）重新安装
4的解决办法如下：
去掉SQL SERVER安装路径里的中文
5的解决办法如下：
（1）在开始>运行中输入regedit；
（2）到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager 位置，在右边窗口删除PendingFileRenameOperations项；
可用F3反复查找该键值，直至全部删除。
（3）如果问题依旧,试试先修复操作系统，命令提示符下执行:sfc /scannow；
（4）最后的方法，重装系统，重装SQL SERVER。
二、SQL SERVER 2000版本问题
有关WIN操作系统与SQL SERVER版本兼容的细节，请参见BOL或MSDN，这里只是平日里的一点点总结。
2.1、WIN2000
WIN2000 SVR可以安装SQL Server 2000的任何版本；
WIN2000 PRO只能安装SQL Server 2000的个人版、开发版。
2.2、WINXP
XP下只能安装SQL Server 2000个人版和开发版，也可以通过变通的手法装上企业版，见2.4。
XP的系统安装SQL Server2000需要打上SQL SERVER SP3及以上的补丁,我一般是打上SQL SERVER SP4补丁，否则SQL SERVER客户端可能无法访问，详见《SQL SERVER 2000 访问》；
XP 下也只能安装SQL Server 2005 开发版，安装的企业版有很多服务启动不了。
2.3、WIN2003
在WIN2003上安装SQL SERVER2000，一定要打补丁（同WINXP）；
2.4、WINXP上安装SQL SERVER 2000企业版
1、在SQL服务器的安装盘中找到MSDE这个目录，并且点击setup.exe安装它，过程简单直接下一步就ＯＫ了；
2、重启系统WINDOWSXP,这下就可以看到SQL服务的图标出现了；
3、再拿出SQL服务器版的安装光盘，直接安装客户端工具（这个不要多说吧？最简单的方法就是直接点击光盘根目录下的autorun.exe），根据提示安装，自检过程中知道系统不是SERVER版，会提示只安装客户端工具。（哈哈，服务端我已有了）；
4、打开企业管理器，试用SA用户连一下看看，是不是发现SA用户登陆失败？因为你还没有与信任SQL SERVER连接相关联。还好这个只要对系统注册表稍加修改就可以啦：
在运行中输入regedit打开注册表编辑器，
找到[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\
MSSQLSERVER]，
这个项里面有一个键值LoginMode，默认下，值是1，现在将值改为2，重启电脑。
5、再打开企业管理，再连接试试，是不是OK了？
三、SQL SERVER 2000补丁问题
3.1、在安装 SQL Server 2000 Service Pack 3 时可能收到错误消息“Setup was unable to validate the logged user”（安装程序无法验证登录的用户）
症状
在安装了 Microsoft 数据访问组件 (MDAC) 2.6 版或更早版本的计算机上安装 Microsoft SQL Server 2000 Service Pack 3 (SP3) 时，安装可能会失败。您可能会收到以下错误消息：
错误消息：1
Setup was unable to validate the logged user.Press Retry to enter another option, or Cancel to exit setup.
错误消息 2：
Setup was unable to verify the state of the server for an upgrade.Verify the server can start and that you provided a valid sa password and restart setup.
方法 1
这时查看临时目录下的sqlsp.out，会发现以下描述：
[TCP/IP Sockets]Specified SQL server not found.
[TCP/IP Sockets]ConnectionOpen (Connect()).
其实这是SQL Server SP3的一个小bug，在安装sp3的时候，没有监听tcp/ip端口，可以按照以下步骤进行：
1、打开SQL server客户器网络实用工具和服务器网络工具，确保启用的协议中包含name pipe，并且位置在第一位.
2、确保[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB".如果没有，请自己建立
3、停止mssql.
4、进行安装.
这样就可以进行正确安装了。
建议安装SP4，我也是因为有次手头没SP4，就从同事那拿个SP3过来装，然后就遇到这个错了。
方法 2
确保将注册表项 NtfsDisable8dot3NameCreation 设置为 0。该值为 0 可确保能够使用短名称访问带有空格的文件夹。要设置该注册表项，请按照下列步骤操作：
1.在“开始”菜单上，单击“运行”。
2.键入“Regedit”，然后单击“确定”。
3.在“注册表编辑器”中，找到以下注册表项路径：
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
4.在“注册表编辑器”的右侧，双击“NtfsDisable8dot3NameCreation”项。
5.在“编辑字符串”对话框中的“数值数据”文本框中，键入 0。单击“确定”。
6.关闭“注册表编辑器”。
确保 TEMP 或 TMP 环境变量没有空格。要确认这一点，请按照下列步骤操作：
1.右键单击“我的电脑”，然后单击“属性”。
2.在“高级”选项卡上，单击“环境变量”。
3.在“<logged user> 的用户变量”列表中，选择“TMP”，然后单击“编辑”。确保“变量值”文本框中没有空格，尤其是没有任何尾随空格，然后单击“确定”。
4.选择“TEMP”，然后单击“编辑”。确保“变量值”文本框中没有空格，尤其是没有任何尾随空格，然后单击“确定”。
5.如果任一变量值有空格，请创建一个新的没有任何空格的临时文件夹（例如，C:\Temp），然后将其指定为 TEMP 和 TMP 环境变量的变量值。
详见：www.microsoft.com
另外，在XP上安装SQL SERVER 2KSP4时也会出现这样的错误。不管是MSSQL还是ORACLE，在XP系统上经常出现异常的问题，所以建议装数据库的系统最好为2000系列的 （WIN系统的话）。
在安装之前检查计算机上所有关于数据库的文件是否已经删除，特别是注册表，因为安装过程中，安装程序会检查注册表，如果已经存在的健值可能会不加以改写。
3.2、SQL Server 2000补丁版本的检查
版本号
补丁号
8.00.194
(RTM，也就是没补丁)
8.00.384
(SP1)
8.00.534
(SP2)
8.00.760
(SP3)
8.00.2039
(SP4)
（1）语句查看
select @@version
或者
exec master..xp_msver
（2）查询分析器>帮助>关于
（3）企业管理器>服务器>属性>一般
四、服务器实例名
命名实例
安装时，可以指定SQL SERVER的实例名，安装完成后，使用hostname\instancename进行连接。
[解决办法]
http://daimajishu.iteye.com/blog/1078455
----------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-07-06 23:13:19
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258 (转载保留此信息)
-- Subject: SQL 系统存储过程用法整理
----------------------------------------------
-- 更详细的介结参考联机帮助文档
xp_cmdshell --*执行DOS各种命令，结果以文本行返回。
xp_fixeddrives --*查询各磁盘/分区可用空间
xp_loginconfig --*报告SQL Server 实例在Windows 上运行时的登录安全配置
xp_logininfo --*返回有关Windows 认证登录的信息。
xp_msver --*返回有关Microsoft SQL Server 的版本信息
xp_enumgroups --返回Windows用户组列表或在指定域中的全局组列表。
xp_sendmail --将电子邮件发送给指定的收件人(后续版本将删除该功能)。
xp_readmail --阅读SQL Mail收件箱中的邮件(后续版本将删除该功能)。
xp_deletemail --删除Microsoft SQL Server 收件箱中的邮件(后续版本将删除该功能)。
xp_startmail --通过该过程启动SQL Mail将返回两条消息,主要用于故障排除。
xp_stopmail --停止SQL 邮件客户端会话(后续版本将删除该功能)。
xp_grantlogin --授予Windows 组或用户对SQL Server 的访问权限(后续版本将删除该功能)。
xp_revokelogin --撤消Windows 组或用户对SQL Server 的访问权限(后续版本将删除该功能)。
xp_logevent --将用户定义消息记入SQL Server 日志文件和Windows 事件查看器。
xp_sprintf --设置一系列字符和值的格式并将其存储到字符串输出参数中。每个格式参数都用相应的参数替换。
xp_sqlmaint --使用包含sqlmaint 开关的字符串调用sqlmaint 实用工具(后续版本将删除该功能)。
xp_sscanf --将数据从字符串读入每个格式参数所指定的参数位置。
sp_ActiveDirectory_Obj --控制数据库在Windows活动目录中的注册。
sp_ActiveDirectory_SCP --控制已连接实例的数据库在Windows活动目录中的注册。
sp_add_agent_parameter --将新参数及其值添加到代理配置文件中。
sp_add_agent_profile --为复制代理创建新的配置文件。
sp_add_alert --创建一个警报。
sp_add_category --将指定的作业、警报或操作员类别添加到服务器中。
sp_add_job --*添加由SQLServerAgent 服务执行的新作业。
sp_add_jobschedule --*创建作业计划。
sp_add_jobserver --在指定的服务器中，以指定的作业为目标。
sp_add_jobstep --*在作业中添加一个步骤（操作）。
sp_add_log_shipping_alert_job --检查是否已在此服务器上创建了警报作业，无则创建。
sp_add_log_shipping_primary_database --设置日志传送配置（包括备份作业、本地监视记录及远程监视记录）的主数据库。
sp_add_log_shipping_primary_secondary --在主服务器上添加辅助数据库项。
sp_add_log_shipping_secondary_database --为日志传送设置辅助数据库。
sp_add_log_shipping_secondary_primary --为指定的主数据库设置主服务器信息，添加本地和远程监视器链接，并在辅助服务器上创建复制作业和还原作业。
sp_add_maintenance_plan --添加维护计划并返回计划ID(后续版本将删除该功能)。
sp_add_maintenance_plan_db --将数据库与维护计划关联(后续版本将删除该功能)。
sp_add_maintenance_plan_job --将维护计划与现有作业关联(后续版本将删除该功能)。
sp_add_notification --设置警报通知。
sp_add_operator --创建用于警报和作业的操作员（通知收件人）。
sp_add_proxy --添加指定SQL Server 代理的代理帐户。
sp_add_schedule --创建一个可由任意数量的作业使用的计划。
sp_add_targetservergroup --添加指定的服务器组。
sp_add_targetsvrgrp_member --将指定的目标服务器添加到指定的目标服务器组。
sp_addapprole --向当前数据库中添加应用程序角色(后续版本将删除该功能)。
sp_addarticle --创建项目并将其添加到发布中。
sp_adddistpublisher --配置发布服务器以使用指定的分发数据库。
sp_adddistributiondb --创建新的分发数据库并安装分发服务器架构。
sp_adddistributor --在分发服务器上对主数据库执行以注册服务器，并将其标记为分发服务器。
sp_adddynamicsnapshot_job --创建一个代理作业，该代理作业可为具有参数化行筛选器的发布生成筛选数据快照。
sp_addextendedproc --向Microsoft SQL Server 注册新扩展存储过程的名称(后续版本将删除该功能)。
sp_addextendedproperty --将新扩展属性添加到数据库对象中。
sp_addlinkedserver --*创建链接服务器。
sp_addlinkedsrvlogin --*添加链接服务器登录映射。
sp_addlogin --创建新的SQL Server 登录(后续版本将删除该功能)。
sp_addlogreader_agent --为给定数据库添加日志读取器代理。
sp_addmergealternatepublisher --为订阅服务器添加使用备用同步伙伴的功能。
sp_addmergearticle --在现有的合并发布中添加项目。
sp_addmergefilter --添加新合并筛选以创建基于与另一个表的联接的分区。
sp_addmergepartition --为在订阅服务器上按HOST_NAME 或SUSER_SNAME 的值进行筛选的订阅创建动态筛选分区。
sp_addmergepublication --创建新合并发布。
sp_addmergepullsubscription --添加对合并发布的请求订阅。
sp_addmergepullsubscription_agent --向合并发布添加一个用于计划请求订阅同步的新代理作业。
sp_addmergepushsubscription_agent --添加一个新代理作业，用于制定合并发布推送订阅的同步计划。
sp_addmergesubscription --创建推送合并订阅或请求合并订阅。
sp_addmessage --将新的用户定义错误消息存储在SQL Server 数据库引擎实例中。
sp_addpublication --创建快照或事务发布。
sp_addpublication_snapshot --为指定的发布创建快照代理。
sp_addpullsubscription --将请求订阅添加到快照或事务发布。
sp_addpullsubscription_agent --向事务发布添加用于同步请求订阅的全新预定的代理作业。
sp_addpushsubscription_agent --添加新的预定代理作业，以使推送订阅与事务发布同步。
sp_addqreader_agent --为给定分发服务器添加队列读取器代理。
sp_addremotelogin --在本地服务器上添加新的远程登录ID(后续版本将删除该功能)。
sp_addrole --在当前数据库中创建新的数据库角色(后续版本将删除该功能)。
sp_addrolemember --为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows 登录名或Windows 组。
sp_addscriptexec --将SQL 脚本（.sql 文件）投递到发布的所有订阅服务器。
sp_addserver --定义SQL Server 本地实例的名称(后续版本将删除该功能)。
sp_addsrvrolemember --添加登录，使其成为固定服务器角色的成员。
sp_addsubscriber --向发布服务器添加新的订阅服务器，使其能够接收发布。
sp_addsubscriber_schedule --为分发代理和合并代理添加计划。
sp_addsubscription --订阅添加到发布并设置订阅服务器的状态。
sp_addsynctriggers --在订阅服务器上创建与所有类型的可更新订阅一起使用的触发器。
sp_addtabletocontents --将源表中当前不在跟踪表内的任何行的引用插入合并跟踪表中。
sp_addtype --创建别名数据类型(后续版本将删除该功能)。
sp_addumpdevice --将备份设备添加到SQL Server 数据库引擎的实例中。
sp_adduser --向当前数据库中添加新的用户(后续版本将删除该功能)。
sp_adjustpublisheridentityrange --调整发布上的标识范围，并基于发布上的阈值重新分配新的范围。
sp_altermessage --更改SQL Server 数据库引擎实例中用户定义消息的状态。
sp_apply_job_to_targets --将作业应用于一个或多个目标服务器或属于一个或多个目标服务器组的目标服务器。
sp_approlepassword --更改当前数据库中应用程序角色的密码(后续版本将删除该功能)。
sp_article_validation --启动对指定项目的数据验证请求。
sp_articlecolumn --用于指定项目中包含的列以垂直筛选已发布表中的数据。
sp_articlefilter --基于表项目筛选发布的数据。
sp_articleview --在垂直或水平筛选表时创建用于定义已发布项目的视图。
sp_attach_db --将数据库附加到服务器(后续版本将删除该功能)。
sp_attach_schedule --设置一个作业计划。
sp_attach_single_file_db --将只有一个数据文件的数据库附加到当前服务器。
sp_attachsubscription --将现有的订阅数据库附加到任何订阅服务器。
sp_autostats --显示或更改特定索引或统计信息的自动UPDATE STATISTICS 设置。
sp_batch_params --显示有关Transact-SQL 批处理中所含参数的信息。
sp_bindefault --将默认值绑定到列或绑定到别名数据类型(后续版本将删除该功能)。
sp_bindrule --将规则绑定到列或别名数据类型(后续版本将删除该功能)。
sp_bindsession --将会话绑定到同一SQL Server 数据库引擎实例中的其他会话或取消它与这些会话的绑定(后续版本将删除该功能)。
sp_browsemergesnapshotfolder --返回为合并发布生成的最新快照的完整路径。
sp_browsereplcmds --返回分发数据库中存储的可读版本复制命令的结果集，并将其用作诊断工具。
sp_browsesnapshotfolder --返回为发布生成的最新快照的完整路径。
sp_can_tlog_be_applied --验证事务日志是否可应用于数据库。
sp_catalogs --返回指定链接服务器中目录的列表。
sp_cdc_add_job --在当前数据库中创建变更数据捕获清理或捕获作业。
sp_cdc_change_job --修改当前数据库中变更数据捕获清除或捕获作业的配置。
sp_cdc_cleanup_change_table --根据指定的low_water_mark 值从当前数据库的更改表中删除行。
sp_cdc_disable_db --对当前数据库禁用变更数据捕获。
sp_cdc_disable_table --对当前数据库中指定的源表和捕获实例禁用变更数据捕获。
sp_cdc_drop_job --从当前数据库中删除变更数据捕获清除或捕获作业。
sp_cdc_enable_db --对当前数据库启用变更数据捕获。
sp_cdc_enable_table --为当前数据库中指定的源表启用变更数据捕获。
sp_cdc_generate_wrapper_function
[解决办法]
等小F或是大版看到后，希望可以帮小三换个标题。
[解决办法]
来吧！都来贴！弄个比较全面的收藏贴！而且是蛋帖哦！
[解决办法]
留名学习
[解决办法]
可惜我没有料
[解决办法]
强帖留名
[解决办法]
顶，换个好标题是必须的！！
[解决办法]
日，前几楼我都看不懂！杯具！
顶下，灰溜溜的走了
[解决办法]
我偷走咯
[解决办法]
菜鸟来袭 三姐散分消灾吧
[解决办法]
该回复于2011-08-23 16:39:04被版主删除
[解决办法]
收藏！
无限蛋疼中。。
[解决办法]
随便看看，谢谢分享
[解决办法]
好多啊 ，要收藏
[解决办法]
超的很详细，谢谢楼主。
[解决办法]
你的榜样是谁？写下来- 扩充话题/ 程序人生
[解决办法]
相关问题
你的榜样是谁？写下来- 扩充话题/ 程序人生 昨晚和老婆XXOO很尽兴，于是想夸老婆一下下，结果我说了句“还是和老婆 ... 关于操蛋的listView.setAdapter， 求解- 移动平台/ Android 求教关于Dispose的接口问题- .NET技术/ VB.NET 求建议：即将大四，想考oca无企业经验的女子很迷茫！ - Oracle / 认证与考试 咨询WIDE-DHCP的问题? - VC/MFC / 网络编程
[解决办法]
谢了，最近没时间，先收下了
[解决办法]
T-SQL: 15 个与日期时间相关的精典语句函数
T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响
都是从老文章里收集或提炼出来的!
提示:
(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与 @@DateFirst 无关,与语言版本无关
@@DateFirst 可能会导致 datepart(weekday,@Date) 不一样!
无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!
(@@Datefirst + datepart(weekday,@Date)): 2、3、4、5、6、0、1 分别代表 周一 到 周日
-- */
create function udf_GetAge(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回精确年龄 select dbo.udf_GetAge('1949-10-01',getdate())
begin
return datediff(year,@StartDate,@EndDate)
- case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
then 0
else
1
end
end
go
create function udf_DaysOfYearByDate(@Date datetime)
RETURNS integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date )+1,0))
end
go
create function udf_DaysOfYear(@Year integer)
RETURNS integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))
end
go
create function udf_HalfDay(@Date datetime)
returns datetime
-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点
as
begin
return case when datepart(hour,@Date)
go
create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天
begin
return datediff(week,@StartDate,@EndDate) -- + 1
+ case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1
then 1
else
0
end
- case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1
then 1
else 0
end
end
go
create function udf_WeekOfMonth(@Date datetime)
-- 返回 @Date 是所在月的第几周 周日是当周的最后一天
returns integer
as
begin
return datediff(week
,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1
then dateadd(month,datediff(month,0,@Date),0) - 1
else
dateadd(month,datediff(month,0,@Date),0)
end
,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then @Date-1
else @Date
end
) + 1
end
go
create function udf_WeekOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度的第几周 周日是当周的最后一天
returns int
as
begin
return datediff(week
,case when (@@Datefirst + datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0))) % 7 = 1
then dateadd(Quarter,datediff(Quarter,0,@Date),0) - 1
else
dateadd(Quarter,datediff(Quarter,0,@Date),0)
end
,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then @Date - 1
else
@Date
end
) + 1
end
go
create function udf_WeekOfYear(@Date datetime)
-- 返回 @Date 是所在年的第几周 周日是当周的最后一天
returns int
as
begin
return datediff(week
,case when (@@Datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))) % 7 = 1
then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))
else
dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) --date 所在年的第一天 即: 一月一号
end
,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then dateadd(day,-1,@Date)
else
@Date
end
) + 1
end
go
create function udf_WeekDay(@ int,@Date datetime)
returns datetime
-- 返回 @Date 映射到 所在周的其他天 周日是当周的最后一天
begin
/*
--周日算作(上一)周的最后一天
当 @ = 7 代表将 @Date 映射到 所在周的星期日
可用于按周汇总 Group by,均支持跨年跨月数据
*/
return dateadd(day
,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 --周六
then case when @ between 1 and 6
then @ - 6
else
1
end
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日(七)
then case when @ between 1 and 6
then @ - 7
else
0
end
when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周一至周五
then case when @ between 1 and 6
then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7
else
8 - (@@Datefirst + datepart(weekday,@Date)) % 7
end
end
,@Date)
end
go
create function udf_WeekdayDiff(@Weekday integer,@StartDate datetime,@EndDate datetime)
returns integer
-- -- 返回 [@StartDate , @EndDate] 之间周一 到 周日的个数 周日是当周的最后一天
begin
-- @Weekday: 1: Monday , ... ,7: Sunday
return datediff(week,@StartDate,@EndDate)
+ case when (@@Datefirst + datepart(weekday,@StartDate)) % 7
+ case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 0
then 7
else
0
end > @Weekday % 7 + 1
then 0
else 1
end
- case when (@@Datefirst + datepart(weekday,@EndDate)) % 7
+ case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 0
then 7
else 0
end >= @Weekday % 7 + 1
then
0
else
1
end
/* test:
declare @b datetime
declare @e datetime
set @b = '2004-01-29'
set @e = '2004-09-05'
select @b as BeginDate ,@e as EndDate
,dbo.udf_WeekdayDiff(1,@b,@e) as CountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e) as CountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e) as CountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e) as CountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e) as CountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e) as CountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e) as CountOfSunday
*/
end
go
create function udf_WeekdayID(@Date datetime)
returns integer
-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1
begin
--1: Monday , ... ,7: Sunday
return (@@Datefirst + datepart(weekday,@Date)) % 7
+ case when (@@Datefirst + datepart(weekday,@Date)) % 7
go
create function udf_NextWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的下一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = '2005-01-02'
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
then dateadd(day,3,@Date)
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
then dateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
end
go
create function udf_PreviousWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的上一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = '2005-01-02'
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
then dateadd(day,-3,@Date)
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
then dateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
go
create function udf_WorkDateAdd(@i integer,@Date datetime)
returns datetime
-- 返回 @Date 加上一段 @i 个工作日的新值
begin
declare @ int
set @ = 0
while @ = 0
then --dbo.udf_nextworkdate(@Date)
case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
then dateadd(day,3,@Date)
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
then dateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
else
--dbo.udf_previousworkdate(@Date)
case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
then dateadd(day,-3,@Date)
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
then dateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
set @ = @ + 1
end
return @Date
end
go
[解决办法]
SQL Server提供了大量的函数,
但是在一些常见的如,
字符串拆分,
字符提取,过滤等没有对应的处理,
本帖主要收集一些常见的函数,
整理如下:
------------------------------
http://topic.csdn.net/u/20080306/23/d3c100f2-cda1-4efa-927d-f1f7968884ce.html
/*
功能:拆分字符串.
作者:.....
*/
http://topic.csdn.net/u/20080724/11/dacb530f-62ba-4417-a11a-fe2dee2172b4.html
/*
功能:根据身份证号码取得此CID所在省份
作者:happyflystone
*/
http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html
/*
功能:提取数字
功能:提取英文
功能:提取中文
功能:过滤重复字符
功能:过滤重复字符2
作者:wzy_love_sly
*/
MS SQL Server vs Oracle函数
http://topic.csdn.net/u/20071022/09/477d57b8-1127-4159-8975-2055343c624f.html
sql 函数 md5
http://topic.csdn.net/u/20080619/09/4fb22d1b-f88e-487e-8b74-cf394263fc40.html
求sql取字符串的首字母的函数
http://topic.csdn.net/u/20080623/11/22a3f568-d37e-4bf5-9c13-64171066f582.html
形如1,2,3,4,5,6的字符串转换成行
http://topic.csdn.net/u/20080621/08/3397be02-47cd-40c3-9834-600cbb84c0bf.html
小写金额转换成大写
http://topic.csdn.net/u/20080118/11/80ff7847-20d9-45d8-9ed6-79933ced2cf8.html
整数转换成二进制的函数
http://topic.csdn.net/u/20071108/22/1650e0ef-3c14-4710-b2db-075f1e0badf3.html
SQLserver中实现一个函数，能够将10进制转化为36进制
http://topic.csdn.net/u/20070612/16/05e8b690-0818-479d-a34d-ee6258da4027.html
http://blog.csdn.net/roy_88/archive/2007/11/13/1882106.aspx
/*
功能:十进制/十八进制的互转换(此方法应用于所有进制与10进制的转换)
作者:roy_88
*/
http://topic.csdn.net/u/20080715/10/548c6e12-731f-4e54-ac13-d05325068032.html
/*
功能:整数转换成二进制的函数
作者:happyflystone
*/
SQL Server里面有十六进制转化为十进制的函数吗
http://topic.csdn.net/u/20070116/10/0c7d9ec1-acaa-4918-86cd-51516f7f517f.html
SQL2000加密解密函数
http://topic.csdn.net/t/20041202/20/3610469.html
想通过一个函数或sql语句，随机生成任意6个字母组成的字符
http://topic.csdn.net/u/20070627/17/ea906673-9821-46c6-b351-ab98a0b3d2c4.html
求一个sql函数：计算时间差（除周六和周日外）的天数
http://topic.csdn.net/u/20070322/12/2a8d5fcf-2d5b-49e1-b58a-c7277e15ee95.html
Ip算法匹配
http://topic.csdn.net/u/20080711/15/66639249-52d9-40c6-8f5c-131e49c1a6cf.html
http://topic.csdn.net/u/20080801/11/506af00d-b882-41a2-99d9-4dc6927101fd.html
http://blog.csdn.net/dobear_0922/archive/2007/12/18/1947219.aspx
/*
功能:SQL日历函数.
作者:dobear_0922
*/
http://topic.csdn.net/u/20080505/20/d2dffbbe-6d6b-41cf-b29a-41149540eafa.html
/*
功能:两个支持text字段内文字替换的存储过程
作者:realgz
*/
MS SQL Server 2000 中文模糊搜寻存储过程及函数
http://www.cppfans.com/d_vcl_files/mssqlxp_chn.asp
http://topic.csdn.net/t/20050419/10/3948336.html
/*
功能:全角/半角转换
*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
/*
功能:合并与拆分
整理者:roy_88
*/
http://topic.csdn.net/u/20080605/11/55c273aa-2206-4ded-b6f6-508a7a755c6a.html
/*
功能:自定义函数，用户可以调用这个函数判断指定的字符串是否符合正则表达式的规则.
作者:ranzj
*/
http://topic.csdn.net/u/20080804/20/09f3d937-d0b9-4892-ab01-fae3f38f0525.html
/*
功能:查找某个字符串第N次出现的位置
作者:.....
*/
[解决办法]
好长啊 我表示看不下去了....
[解决办法]
create function udf_GetStar (@ datetime)
RETURNS varchar(100)
-- 返回日期所属星座
BEGIN
RETURN
(
--declare @ datetime
--set @ = getdate()
select max(star)
from
(
select '魔羯座' as star,1 as [month],1 as [day]
union all select '水瓶座',1,20
union all select '双鱼座',2,19
union all select '牡羊座',3,21
union all select '金牛座',4,20
union all select '双子座',5,21
union all select '巨蟹座',6,22
union all select '狮子座',7,23
union all select '处女座',8,23
union all select '天秤座',9,23
union all select '天蝎座',10,24
union all select '射手座',11,22
union all select '魔羯座',12,22
) stars
where dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 =
(
select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1)
from (
select '魔羯座' as star,1 as [month],1 as [day]
union all select '水瓶座',1,20
union all select '双鱼座',2,19
union all select '牡羊座',3,21
union all select '金牛座',4,20
union all select '双子座',5,21
union all select '巨蟹座',6,22
union all select '狮子座',7,23
union all select '处女座',8,23
union all select '天秤座',9,23
union all select '天蝎座',10,24
union all select '射手座',11,22
union all select '魔羯座',12,22
) stars
where @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1
)
)
end
SQL Server 日期算法
一周的第一天
select @@DATEFIRST
一个月的第一天
select dateadd(mm,datediff(mm,0,getdate()),0)
本周的星期一
select dateadd(wk,datediff(wk,0,getdate()),0)
一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
当天的零时
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
上个月的最后一天 ：本月第一天减2ms.
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
本月的最后一天
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
本月的第一个星期一
去掉时分秒
DATEADD(day, DATEDIFF(day,0,getdate()), 0)
显示星期几
select datename(weekday,getdate())
如何取得某个月的天数
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
判断是否闰年：
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end
一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
/*=============================================*/
/* Author: roy_88 */
--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N' ',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N' ')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
declare T_cursor cursor local for
select
a.Name, b.Name
from
sysobjects a
join
syscolumns b on a.ID=b.ID
join
systypes c on c.xusertype=b.Xtype
where
a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0'
fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
---------------------------------------
--改列的数据全角为半角
declare T_cursor cursor local for
select
a.Name, b.Name
from
sysobjects a
join
syscolumns b on a.ID=b.ID
join
systypes c on c.xusertype=b.Xtype
where
a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了)
fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
go
--改列名全角为半角
declare T_cursor cursor local for
select
a.Name, b.Name
from
sysobjects a
join
syscolumns b on a.ID=b.ID
where
a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
exec('exe sp_rename '''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''')
fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
------------------------------------------------------
--改列名全角为半角
declare T_cursor cursor local for
select
a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)
from
sysobjects a
join
syscolumns b on a.ID=b.ID
where
a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
exe sp_rename @tabName,@ColName
fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
[解决办法]
--山东省
[解决办法]
--烟台市
[解决办法]
---------------------------
就用树型表,可到N级.参考如下:
create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
（所影响的行数为 3 行）
*/
drop table tb
drop function dbo.f_cid
--------------------------------
--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02
CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GO
CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b
WHERE a.ChildId=b.ID
AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND a.Level=@Level-1
END
RETURN
END
GO
--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId
/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4
(24 row(s) affected)
*/
drop table item
drop table bom
drop function f_Bom
-------------------------
就用树型表,可到N级.参考如下:
create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
（所影响的行数为 3 行）
*/
drop table tb
drop function dbo.f_cid
-----------------------------------------------------
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
--显示结果
SELECT SPACE(b.Level*2)+'
[解决办法]
--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
[解决办法]
--四会市
[解决办法]
--清远市
[解决办法]
--招远市
[解决办法]
--青岛市
[解决办法]
学习而来，谢谢楼主 辛苦了
87161.com
[解决办法]
留名，占位
[解决办法]
--小分市
--*/
-----------------------------------------
[解决办法]
--以上转自苦行僧
[解决办法]
谢谢啦。先收着啦
[解决办法]
推荐。。。
[解决办法]
路过 帮顶 学习 ~~
[解决办法]
呵呵 说得很详细的
[解决办法]
非顶不可。。
谢谢楼主了
一大早就起来学习了一下
[解决办法]
000000000分
[解决办法]
持续更新，大家一起贴收藏，共享下学习笔记。
[解决办法]
学习 + 围观~
[解决办法]
楼主 这貌似是好帖！接分
[解决办法]
总结+收藏+实践=成功
[解决办法]
好帖子，顶，收藏
[解决办法]
太多了
[解决办法]
好多 mark
[解决办法]
好多，眼晕，烟云，浮云
[解决办法]
好多 mark
[解决办法]
bu neng baikan
[解决办法]
..
[解决办法]
真的DANTENG~~
[解决办法]
大力支持。
[解决办法]
强帖留名
[解决办法]
此贴必火,留名
[解决办法]
hoho 报道 谁给按解决下问题先http://topic.csdn.net/u/20110829/16/d1db6a9a-5b3d-44b1-9c10-c78f410615c1.html?23857
[解决办法]
好贴 顶
[解决办法]
什么叫蛋帖?蛋疼什么意思?
[解决办法]
以后用得着mark
[解决办法]
強力支持
小三换个什麼标题?
[解决办法]
额。。。这个看大版了，当时没仔细考虑，反正是资料收集了，以后看能不能继续更新。
[解决办法]
不错 很好
[解决办法]
好多呀，为什么就是看不懂呢！！
[解决办法]
全是知识啊，眼泪哗哗的
[解决办法]
牛X，我觉得可以整成一本chm
[解决办法]
小三开课了，继续学习……
[解决办法]
很好，学习了。
[解决办法]
不错，灰常好。学习啦！
[解决办法]
强势mark~
[解决办法]
强势mark~
[解决办法]
新手上路····
[解决办法]
很好，很强大