Ms SQL常见问题收集整理集(不断收集中...)

ccwlm741212

比如在web端，我每次只想读出10行，如何控制？  
第一次select  1  -  9行  
第二次select  10-19行  
第三次......  
---------------------------------------------------------------  

key为表中的关键字段，n是截取的次数，10  为每次截取的记录数试试看，我这里测试通过。  
select  top  10  *    from  table   
               where  key  not  in(select  top  10*（n-1）  key  from  table)  
---------------------------------------------------------------  

为什么这样做啊！ADO可以很好的实现你的需求。你只要一次性的读出来。设置pagesize  =  10,一次从recordset中读一页就可以了。

ccwlm741212

对含有自动增长类型identify字段的table导入数据的方法

文章来源：本网 文章编辑：admin 添加时间：2004-1-15 19:03:41




作者：陶清 email: taoqing@163.net

identity字段其会自动增长，是做为一个表的主关键字的好办法，在设计MIS系统时表与表之间用这个identity作为搜索和关联用，这样对于一个字典表来说，编码可以允许最终客户随时修改，非常灵活和方便。
但用到数据的导入时，就会遇到一些麻烦，因为identity字段是由后台数据库直接增加的。想直接修改后台数据库默认是不允许的。
这时候就要在pb中调用后台数据库的命令 set identity on (本方法在sql anywher和ms sql server中都有, 对oracle的命令你可以自行查询相关的命令)，然后才可以对identity进行直接的更新。同时在设计dw的update属性的时候也要注意，不要在identity选择任何字段（默认情况下pb会自动将identity字段给选中，你需要在dw的设计状态下指定其为none即可)，如下图所示





演示代码如下：
假设你已经将一张表导出到一个名为 backup.txt 的文件中，利用dw_1.saveas() 就可以将数据选择存为.txt文件

string ls_tablename, ls_sql

ls_tablename = "dict1" //假设你的table名称为 dict1
ls_sql = "SET IDENTITY_INSERT " + ls_tablename + " ON "
sqlca.autocommit = true  //调用系统命令必须将autocommit置为true
EXECUTE IMMEDIATE :ls_sql;
//--------------1------------------//
dw_1.ImportFile("backup.txt"

If dw_1.update() = 1 Then
//更新成功
commit;

Else
rollback;
//失败
End If
//将identity恢复为off
ls_sql = "SET IDENTITY_INSERT " + ls_tablename + " OFF "
sqlca.autocommit = true
EXECUTE IMMEDIATE :ls_sql;
sqlca.autocommit = False

WTO

本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。

问：我需要将Microsoft Word文档导入至SQL Server并索引这些文档，以便在关系查询中使用这些文档。怎样导入和索引文档呢？

答：SQL Server允许您以多种方式导入Word文档。让我们看看几种最常用的方法。请注意，在将文档导入至SQL Server之前，您需要创建一个image数据类型列，用于存放数据。然后，您可以使用textcopy.exe命令行工具将image文件读入数据库，从而完成文档的导入操作。如果需要该工具的基本说明文档，请在命令提示状态下键入textcopy /?。将Word文档导入SQL Server的另一个方法是使用Microsoft ActiveX Directory Object（ADO）Stream接口编写导入代码。您可以在Microsoft产品支持服务（PSS）的通过使用ADO Stream对象访问和修改SQL Server BLOB数据一文中找到示例代码。

此外，您也可以将二进制数据移到SQL Server中。有关这一方法的详细说明，请参阅PSS的使用ADO检索和更新SQL Server文本域一文。移动二进制数据允许您将数据的一部分存放在数据库中，这在需要控制数据格式时尤其有用。例如，如果只需要数据中1,000到1,010之间的字节，导入二进制数据的速度远远高于使用ADO Stream接口的速度，这是由于SQL Server从磁盘上检索数据的量大为减少。人们通常使用这一技术来存储位掩码，用于表示应用程序的开或关标志位。

SQL Server 2000自带了说明了如何移动二进制数据的示例代码。如需查看该代码，只需在安装有SQL Server 2000光盘上代码示例的驱动器上选择 \Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\ado路径。展开该可执行文件，在Visual Basic目录下查找Samples子目录。在Employee示例下，注意一下该代码是如何使用FillDataFields()函数的。

如需索引Word文档，SQL Server 7.0和SQL Server 2000都提供了全文搜索组件。该组件混合使用了多种技术，用以索引大型文本和image列。在执行全文搜索时，您需要指定image列所含的文件类型，以及从二进制数据中析取信息所需的筛选（filter）。有关使用全文索引的详细信息，请参阅SQL Server在线书籍中的相关主题，并阅读David Jones 2000年7月发表在SQL Server杂志网站上的名为构建更好的搜索引擎一文。请注意，索引Word文档并不会自动生成包含文档中关键字的一组关系表。但是，索引文件让您将这些Word文档包含在您的搜索中。以下是从数据中析取关键字的可行方式：

使用OLE自动处理从文档中读取用户定义的关键字。在装载该文档时将这些关键字保存在关系表中。
使用OLE自动处理打开文档并将其保存为文本（.txt）格式。为了析取重要的词语，您可以使用自己的“词语分析器”遍历该文本文件。文字分析器查看文档中的每个词语，去除一些无用文字，并将每个唯一的词语与其出现的次数一道存储起来。
在新的全文索引文档中搜索特定的关键字，并将这些关键字输入到关系表中。
SQL Server 2000提供了多个强大的工具和接口，用于帮助用户实现二进制Microsoft Office文档的快速装载、搜索和检索。

—SQL Server 开发团队

ccwlm741212

*create proc up_010
as
begin
declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
declare cur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
--删除所有外键
open cur_fk
fetch cur_fk into @fk,@tbl
while @@fetch_status =0
begin
select @sql='alter table '+@tbl+' nocheck constraint '+@fk
exec(@SQL)
select @sql='delete from '+@tbl
exec(@SQL)
fetch cur_fk into @fk,@tbl
end
close cur_fk
declare cur_fks cursor local for
select name from sysobjects where xtype='U'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
begin
select @sql='delete from ['+@tbl+']'
exec(@SQL)
fetch cur_fks into @tbl
end
close cur_fks
end

exec up_010

這個過程是nocheck所有外建﹐然后再刪除所有業務數據﹐
然后再check所有外鍵。*/
有几个错误，一是游标的资源没有被释放，而且表之间如果有触发器的话那还是有部分的数据没有删除干净２：也没有ｃｈｅｃｋ所有的外键，不过我修改了部分的代码，也不知道对不对
create proc up_010
as
begin
declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
declare cur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
--删除所有外键
open cur_fk
fetch cur_fk into @fk,@tbl
while @@fetch_status =0
begin
set @sql='alter table '+@tbl+' nocheck constraint '+@fk
exec(@SQL)
fetch cur_fk into @fk,@tbl
end
close cur_fk
deallocate cur_fk
--删除数据
declare @times int
set @times=10
while @times<>0
begin
declare cur_fks cursor local for
select name from sysobjects where xtype='U'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
begin
select @sql='delete from ['+@tbl+']'
exec(@SQL)
fetch cur_fks into @tbl
end
close cur_fks
deallocate cur_fks
set @times=@times-1
end
--增加所以的外键
declare addcur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
open addcur_fk
fetch addcur_fk into @fk,@tbl
while @@fetch_status=0
begin
set @sql='alter table '+@tbl+' check constraint '+@fk
exec(@sql)
fetch addcur_fk into @fk,@tbl
end
close addcur_fk
deallocate addcur_fk
--
end

ccwlm741212

比较2表结构


这个语句只适用于双方记录一样的情况，
至于双方互不存在的记录是比较好处理的

--删除n1,n2中若干条记录
delete from n1 where orderID in ('10728','10730')
delete from n2 where orderID in ('11000','11001')

--*************************************************************
-- 双方都有该记录却不完全相同
select * from n1 where orderid in
(
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
)
union
--n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
--n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
--*************************************************************

ccwlm741212

视图分可更新视图与不可更新视图，
如果视图是从一个表得来的，一般来说各种大型数据库都支持，
如果视图是从多个表得来的，一次只能修改其中的一个表MSSQL支持，ORACLE也可以。

修改视图一般使用instead of触发器。
create table a1(id int,name varchar(20))
craete table a2(id int,age tinyint)
go
create view v_a1_a2(id,name,age)
as
select a.id,a.name,b.age from a1 a,a2 b where a1.id=a2.id
go
create trigger tri_ins_v1
instead of insert
as
begin
insert into a1 select id,name from inserted
insert into a2 select id,age from inserted
end

ccwlm741212

--查看锁信息
create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)
        ,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for
        select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
        from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
        set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
        exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
        insert into #t values(@rid,@objname)
        fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb

select 进程id=a.req_spid
        ,数据库=db_name(rsc_dbid)
        ,类型=case rsc_type when 1 then 'NULL 资源（未使用）'
                when 2 then '数据库'
                when 3 then '文件'
                when 4 then '索引'
                when 5 then '表'
                when 6 then '页'
                when 7 then '键'
                when 8 then '扩展盘区'
                when 9 then 'RID（行 ID)'
                when 10 then '应用程序'
        end
        ,对象id=rsc_objid
        ,对象名=b.obj_name
        ,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go
drop table #t

ccwlm741212

Transact-SQL 参考


sysobjects
在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。

列名 数据类型 描述
name sysname 对象名。
Id int 对象标识号。
xtype char(2) 对象类型。可以是下列对象类型中的一种：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程

uid smallint 所有者对象的用户 ID。
info smallint 保留。仅限内部使用。
status int 保留。仅限内部使用。
base_schema_
ver int 保留。仅限内部使用。
replinfo int 保留。供复制使用。
parent_obj int 父对象的对象标识号（例如，对于触发器或约束，该标识号为表 ID）。
crdate datetime 对象的创建日期。
ftcatid smallint 为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为 0。  
schema_ver int 版本号，该版本号在每次表的架构更改时都增加。
stats_schema_
ver int 保留。仅限内部使用。
type char(2) 对象类型。可以是下列值之一：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
FN = 标量函数
IF = 内嵌表函数
K = PRIMARY KEY 或 UNIQUE 约束
L = 日志
P = 存储过程
R = 规则
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
V = 视图
X = 扩展存储过程

userstat smallint 保留。
sysstat smallint 内部状态信息。
indexdel smallint 保留。
refdate datetime 留作以后使用。
version int 留作以后使用。
deltrig int 保留。
instrig int 保留。
updtrig int 保留。
seltrig int 保留。
category int 用于发布、约束和标识。
cache smallint 保留。

ccwlm741212

请问数据库日志文件占满了硬盘空间，怎么办？

--不能直接删除日志文件,按下面的步骤处理:
--压缩日志及数据库文件大小

1.清空日志
        DUMP  TRANSACTION  库名  WITH  NO_LOG   

2.截断事务日志：
        BACKUP LOG 数据库名 WITH NO_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小
        企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
                --选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
                --选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

        也可以用SQL语句来完成
        --收缩数据库
        DBCC SHRINKDATABASE(客户资料)
       
        --收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
        DBCC SHRINKFILE(1)

4.为了最大化的缩小日志文件
        a.分离数据库:
                企业管理器--服务器--数据库--右键--分离数据库

        b.在我的电脑中删除LOG文件

        c.附加数据库:
                企业管理器--服务器--数据库--右键--附加数据库

        此法将生成新的LOG，大小只有500多K

        或用代码：
        下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。
       
        a.分离
        EXEC sp_detach_db @dbname = 'pubs'

        b.删除日志文件
       
        c.再附加
        EXEC sp_attach_single_file_db @dbname = 'pubs',
           @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5.为了以后能自动收缩,做如下设置:
        企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"
       
        --SQL语句设置方式:
        EXEC sp_dboption '数据库名', 'autoshrink', 'TRUE'       

6.如果想以后不让它日志增长得太大
        企业管理器--服务器--右键数据库--属性--事务日志
                --将文件增长限制为xM(x是你允许的最大数据文件大小)

        --SQL语句的设置方式:
        alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)

ccwlm741212

下面我们依次介绍如何来解决这三个最常见的连接错误。
>
> 　　第一个错误"SQL Server 不存在或访问被拒绝"通常是最复杂
> 的，错误发生的原因比较多，需要检查的方面也比较多。一般说
> 来，有以下几种可能性：
>
> 　　1、SQL Server名称或IP地址拼写有误；
>
> 　　2、服务器端网络配置有误；
>
> 　　3、客户端网络配置有误。
>
> 　　要解决这个问题，我们一般要遵循以下的步骤来一步步找出导
> 致错误的原因。
>
> 　　首先，检查网络物理连接：
>
> 　　　ping ＜服务器IP地址＞
> 　
> 　　或者
>
> 　　　ping ＜服务器名称＞
>
> 　　如果 ping ＜服务器IP地址＞ 失败，说明物理连接有问题，这
> 时候要检查硬件设备，如网卡、HUB、路由器等。还有一种可能是由
> 于客户端和服务器之间安装有防火墙软件造成的，比如 ISA
> Server。防火墙软件可能会屏蔽对 ping、telnet 等的响应，因此
> 在检查连接问题的时候，我们要先把防火墙软件暂时关闭，或者打
> 开所有被封闭的端口。
>
> 　　如果ping ＜服务器IP地址＞ 成功而 ping ＜服务器名称＞ 失
> 败，则说明名字解析有问题，这时候要检查 DNS 服务是否正常。有
> 时候客户端和服务器不在同一个局域网里面，这时候很可能无法直
> 接使用服务器名称来标识该服务器，这时候我们可以使用HOSTS文件
> 来进行名字解析，具体的方法是：
>
> 　　1、使用记事本打开HOSTS文件（一般情况下位于
> C:\WINNT\system32\drivers\etc）.
>
> 　　2、添加一条IP地址与服务器名称的对应记录，如：
>
> 　　　　172.168.10.24 myserver
>
> 　　也可以在 SQL Server 的客户端网络实用工具里面进行配置，
> 后面会有详细说明。
>
> 　　其次，使用 telnet 命令检查SQL Server服务器工作状态：
>
> 　　　telnet ＜服务器IP地址＞ 1433
>
> 　　如果命令执行成功，可以看到屏幕一闪之后光标在左上角不停
> 闪动，这说明 SQL Server 服务器工作正常，并且正在监听1433端
> 口的 TCP/IP 连接；如果命令返回"无法打开连接"的错误信息，则
> 说明服务器端没有启动 SQL Server 服务，也可能服务器端没启用
> TCP/IP 协议，或者服务器端没有在 SQL Server 默认的端口1433上
> 监听。
>
> 　　接着，我们要到服务器上检查服务器端的网络配置，检查是否
> 启用了命名管道，是否启用了 TCP/IP 协议等等。我们可以利用
> SQL Server 自带的服务器网络使用工具来进行检查。
>
> 　　点击：程序 -＞ Microsoft SQL Server -＞ 服务器网络使用
> 工具，打开该工具后看到的画面如下图所示：
>
> 　　
>
>
> 　　从这里我们可以看到服务器启用了哪些协议。一般而言，我们
> 启用命名管道以及 TCP/IP 协议。
>
> 　　点中 TCP/IP 协议，选择"属性"，我们可以来检查 SQK
> Server 服务默认端口的设置，如下图所示：
>
> 　　
>
>
>
>
> 　　一般而言，我们使用 SQL Server 默认的1433端口。如果选
> 中"隐藏服务器"，则意味着客户端无法通过枚举服务器来看到这台
> 服务器，起到了保护的作用，但不影响连接。
>
> 　　检查完了服务器端的网络配置，接下来我们要到客户端检查客
> 户端的网络配置。我们同样可以利用 SQL Server 自带的客户端网
> 络使用工具来进行检查，所不同的是这次是在客户端来运行这个工
> 具。
>
> 　　点击：程序 -＞ Microsoft SQL Server -＞ 客户端网络使用
> 工具， 打开该工具后看到的画面如下图所示：
>
> 　　
>
>
> 　　从这里我们可以看到客户端启用了哪些协议。一般而言，我们
> 同样需要启用命名管道以及 TCP/IP 协议。
>
> 　　点击 TCP/IP 协议，选择"属性"，可以检查客户端默认连接端
> 口的设置，如下图所示。
>
> 　　
>
>
> 　　该端口必须与服务器一致。
>
> 　　单击"别名"选项卡，还可以为服务器配置别名。服务器的别名
> 是用来连接的名称，连接参数中的服务器是真正的服务器名称，两
> 者可以相同或不同。如下图中，我们可以使用myserver来代替真正
> 的服务器名称sql2kcn-02，并且使用网络库 Named Pipes。别名的
> 设置与使用HOSTS文件有相似之处。
>
> 　　
>
>
>
>
> 　　通过以上几个方面的检查，错误 1 发生的原因基本上可以被排
> 除。下面我们再详细描述如何来解决错误 2。
>
> 　　当用户尝试在查询分析器里面使用sa来连接SQL Server，或者
> 在企业管理器里面使用sa来新建一个SQL Server注册时，经常会遇
> 到如图 2 所示的错误信息。该错误产生的原因是由于SQL Server使
> 用了"仅 Windows"的身份验证方式，因此用户无法使用SQL Server
> 的登录帐户（如 sa ）进行连接。解决方法如下所示：
>
> 　　1、 在服务器端使用企业管理器，并且选择"使用 Windows 身
> 份验证"连接上 SQL Server；
>
> 　　2、 展开"SQL Server组"，鼠标右键点击SQL Server服务器的
> 名称，选择"属性"，再选择"安全性"选项卡；
>
> 　　3、 在"身份验证"下，选择"SQL Server和 Windows "。
>
> 　　4、 重新启动SQL Server服务。
>
> 　　在以上解决方法中，如果在第 1 步中使用"使用 Windows 身份
> 验证"连接 SQL Server 失败，那么我们将遇到一个两难的境地：首
> 先，服务器只允许了 Windows 的身份验证；其次，即使使用了
> Windows 身份验证仍然无法连接上服务器。这种情形被形象地称之
> 为"自己把自己锁在了门外"，因为无论用何种方式，用户均无法使
> 用进行连接。实际上，我们可以通过修改一个注册表键值来将身份
> 验证方式改为 SQL Server 和 Windows 混合验证，步骤如下所示：
>
> 　　1、点击"开始"-"运行"，输入regedit，回车进入注册表编辑
> 器；
>
> 　　2、依次展开注册表项，浏览到以下注册表键：
>
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLSer
> ver]
>
> 　　3、在屏幕右方找到名称"LoginMode"，双击编辑双字节值；
>
> 　　4、将原值从1改为2，点击"确定"；
>
> 　　5、关闭注册表编辑器；
>
> 　　6、重新启动SQL Server服务。
>
> 　　此时，用户可以成功地使用sa在企业管理器中新建SQL Server
> 注册，但是仍然无法使用Windows身份验证模式来连接SQL Server。
> 这是因为在 SQL Server 中有两个缺省的登录帐户：
> BUILTIN\Administrators 以及 ＜机器名＞\Administrator 被删
> 除。要恢复这两个帐户，可以使用以下的方法：
>
> 　　1、打开企业管理器，展开服务器组，然后展开服务器；
>
> 　　2、展开"安全性"，右击"登录"，然后单击"新建登录"；
>
> 　　3、在"名称"框中，输入 BUILTIN\Administrators；
>
> 　　4、在"服务器角色"选项卡中，选择"System
> Administrators" ；
>
> 　　5、点击"确定"退出；
>
> 　　6、使用同样方法添加 ＜机器名＞\Administrator 登录。
>
> 　　以下注册表键
>
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServ
> er\LoginMode
>
> 　　的值决定了SQL Server将采取何种身份验证模式。该值为1，表
> 示使用Windows 身份验证模式；该值为2，表示使用混合模式
> （Windows 身份验证和 SQL Server 身份验证）。
>
> 　　看完如何解决前两个错误的方法之后，让我们来看一下如图 3
> 所示的第三个错误。
>
> 　　如果遇到第三个错误，一般而言表示客户端已经找到了这台服
> 务器，并且可以进行连接，不过是由于连接的时间大于允许的时间
> 而导致出错。这种情况一般会发生在当用户在Internet上运行企业
> 管理器来注册另外一台同样在Internet上的服务器，并且是慢速连
> 接时，有可能会导致以上的超时错误。有些情况下，由于局域网的
> 网络问题，也会导致这样的错误。
>
> 　　要解决这样的错误，可以修改客户端的连接超时设置。默认情
> 况下，通过企业管理器注册另外一台SQL Server的超时设置是 4
> 秒，而查询分析器是 15 秒（这也是为什么在企业管理器里发生错
> 误的可能性比较大的原因）。具体步骤为：
>
> 　　1、在企业管理器中，选择菜单上的"工具"，再选择"选项"；
>
> 　　2、在弹出的"SQL Server企业管理器属性"窗口中，点击"高
> 级"选项卡；
>
> 　　3、在"连接设置"下的"登录超时（秒）"右边的框中输入一个比
> 较大的数字，如 20。
>
> 　　查询分析器中也可以在同样位置进行设置。