SqlServer 2000 开发问题

1.NULL永不=NULL .如何设置让 NULL=NULL呢? declare @tab table (id int ) insert into @tab select 1 select * from @tab where null = null 问题解决: 比较前用 set ANSI_NULLS off ,别忘了比较完后再设置为原来的默认值.:) set ANSI_NULLS on 2.分布式事

1.NULL永不=NULL .如何设置让 NULL=NULL呢?

declare @tab table (id int )
insert into @tab select 1

select * from @tab where null = null

问题解决: 比较前用 set ANSI_NULLS off ,别忘了比较完后再设置为原来的默认值.:) set ANSI_NULLS on
2.分布式事务总是出错.
现象:

begin tran
select * from [192.168.1.100].aigdollar.dbo.shoporderinfo
commit

该操作未能执行，因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。
[OLE/DB provider returned message: 新事务不能登记到指定的事务处理器中。 ]

问题解决:
打开MSDTC的安全配置,选中:
网络DTC访问.
允许入站.
允许出站.
不要求进行验证.
启用事务 Ineternet 事务 (TIP事务).
启用 XA 事务.
DTC登录帐户: NT Authority\NetworkService
防火墙打开 135 端口( RPC ),允许 MSDTC ( 或打开1885 端口).
OK!
微软资料: http://support.microsoft.com/kb/Q899191
3.如何在SQL-Server的系统表中找出主键而非索引?
下列代码只能找出索引:

CREATE TABLE [abc] (
[id] [int] NOT NULL ,
[con] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_abc] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [ic] ON [dbo].[abc]([con]) ON [PRIMARY]
GO

select object_name(id),*
from dbo.sysindexkeys
where object_name(id)='abc'

问题解决:
http://www.itdb.cn/n/200605/22/n20060522_6914.shtml
分析晕了.呵呵.分析后关键代码是这样的.

select * from sysobjects where parent_obj = object_id('abc')

select * from sysindexes where id = object_id('abc')

select * from sysindexkeys where id = object_id('abc')

所以下面这样是有道理的.

SELECT *
FROM syscolumns as Col join sysobjects as Obj on ( Col.id = Obj.parent_obj )
WHERE (Obj.xtype = 'PK')
and Obj.parent_obj = object_id('abc')
and Obj.name IN
(
SELECT name
FROM sysindexes as ix
WHERE ix.id = object_id('abc') AND ix.indid IN
(
SELECT indid
FROM sysindexkeys as ik
WHERE (id = object_id('abc') ) AND ik.colid IN
(
SELECT colid
FROM syscolumns as col2
WHERE col2.id = object_id('abc') AND col2.name = Col.name
)
)

)