SqlServer资料收集

发表于:2011-08-23 13:31:46
一、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的默认侦听端口。如果有问题,则提示“……无法打开连接,连接失败"。
  如果这一步有问题,应该检查以下选项:
² 服务器是否启动了SQL Server 2000服务;
² 检查服务器端的网络配置,在服务器上打开:开始>程序>Microsoft SQL Server>服务器网络实用工具,检查是否启用TCP/IP协议,以及TCP/IP协议的端口是否与上面TELNET时的端口一致。同样,如果存在防火墙的话,也要打开该端口。
² 检查服务器是否在侦听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 —-需要重启服务生效
注意:这里的连接数是指不大于等于,即小于设置的值。
将连接最大数设为1,重启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个客户端联接,超过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 code
            
一、系统表 数据字典的详细信息请查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 基础——系统函数》。

 

 

 

 

 

 

转载出处: http://topic.csdn.net/u/20110823/13/23018853-5cdf-4165-add3-f6fea845cf3e.html

  • 1
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值