当然了,我刚才已经说过了。
sql server 2000
sql server 2000
解决方案 »
- 数据库操作 adcmdtext命令疑问?
- 如何撤销对表的更新操作??急,急
- sql2000用文件组放在不同的物理盘上怎么性能没有提高?
- 高手进来看看,关于数据库和二叉树的问题
- 太奇怪的问题了,1个(64->99)*1加上10个(0.01*1)不等于xx.1
- sqlserver 分页请教
- 我有一个实验的代码,谁能帮我运行下,搞下截图吗?
- 关于SQL Server 的数据导入的问题
- 寻找高手,trigger 311 错误 怎样变通实现
- 在XP上第一次安装SQL Server 2000 Personal是好的,可是删除后重装报错。启动服务器出错
- 如何单击任意位置,将系统托盘中弹出来的菜单关闭?
- 如何查找数据库中两个字段的内容都相同的项!
Note Using distributed queries in SQL Server is similar to the linked table functionality through ODBC, which was supported previously by Microsoft Access. This functionality is now built into SQL Server with OLE DB as the interface to external data.
CREATE TRIGGER Del_VIPInfo ON dbo.VIPUser
FOR DELETE
AS
declare @UID int
EXEC RemoteDB.dbcustom.dbo.sp_DelVIP @UID
--RemoteDB为链接服务器名,dbcustom为链接服务器上对应的数据库名链接服务器上的存储过程:
CREATE PROCEDURE sp_DelVIP(@UID int)
as
delete from d_userVIP where ID=@UID
'test2',
'',
'SQLOLEDB',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=cys;PWD=aaa;',
'cys'go
EXEC sp_addlinkedsrvlogin 'test2', 'false', 'sa', 'cys', 'aaa'
goset ANSI_NULLS on
set ANSI_WARNINGS on
set xact_abort offdeclare @UID intselect @UID=230
BEGIN DISTRIBUTED TRANSACTION
delete from d_used_product where user_id=@UID
delete from RentMain where UserID=@UID
if exists(select * from VIPSeniorUser Where VIPUserID=@UID)
begin
update VIPSeniorUser set PayedFlag='0' where VIPUserID=@UID
end
--删除链接服务器中信息--
delete from test2.cys.dbo.d_UserVIP where ID=@UID
COMMIT
go
exec sp_droplinkedsrvlogin 'test2','sa'
go
exec sp_dropserver 'test2'
go结果出错,还是一样的错误信息
服务器: 消息 7391,级别 16,状态 1,行 27
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]大家帮个忙吧!
'test2',
'',
N'SQL Server',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=cys;PWD=aaa;',
'cys'go
EXEC sp_addlinkedsrvlogin 'test2', 'false', null, 'cys', 'aaa'
goset ANSI_NULLS on
set ANSI_WARNINGS on
set xact_abort offdeclare @UID intselect @UID=230
BEGIN DISTRIBUTED TRANSACTION
delete from d_used_product where user_id=@UID
delete from RentMain where UserID=@UID
if exists(select * from VIPSeniorUser Where VIPUserID=@UID)
begin
update VIPSeniorUser set PayedFlag='0' where VIPUserID=@UID
end
--删除链接服务器中信息--
delete from test2.cys.dbo.d_UserVIP where ID=@UID
COMMIT
--go
--exec sp_droplinkedsrvlogin 'test2','sa'
go
exec sp_dropserver 'test2','droplogins'
go
服务器: 消息 7403,级别 16,状态 1,行 17
Could not locate registry entry for OLE DB provider 'SQL Server'.你那能执行成功吗?
我已经加到100分了,哪位解决了一定给分,不够再加。希望大家能先试成功后再告诉我,最好能再帮我分析一下原因,共同提高嘛:)
远程事务必须启动DTC,而且网络设置与两台服务器的SQL版本好象都协调好,反正很麻烦,后来我也把远程事务给取消了不用。
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction
即:OLE DB不能启动一个分布式事务。
而在你的代码里:BEGIN DISTRIBUTED TRANSACTION
所以,你还应往如何设置能运行分布式事务方面想,
ItransactionLocal
和
ITransactionJoin(用于分布式事务)
才可以用写操作
SQL Server 企业管理器中的“链接服务器属性”中的选项是否正确。
EXEC sp_addlinkedserver
'test2',
'',
N'SQLOLEDB', //MODIFY
NULL,
NULL,
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=cys;PWD=aaa;',
'cys'go
EXEC sp_addlinkedsrvlogin 'test2', 'false', null, 'cys', 'aaa'
goset ANSI_NULLS on
set ANSI_WARNINGS on
set xact_abort offdeclare @UID intselect @UID=230
BEGIN DISTRIBUTED TRANSACTION
delete from d_used_product where user_id=@UID
delete from RentMain where UserID=@UID
if exists(select * from VIPSeniorUser Where VIPUserID=@UID)
begin
update VIPSeniorUser set PayedFlag='0' where VIPUserID=@UID
end
--删除链接服务器中信息--
delete from test2.cys.dbo.d_UserVIP where ID=@UID
COMMIT
--go
--exec sp_droplinkedsrvlogin 'test2','sa'
go
exec sp_dropserver 'test2','droplogins'
go
错误:
服务器: 消息 7403,级别 16,状态 1,行 17
Could not locate registry entry for OLE DB provider 'SQL Server'.
THIS MEAN:
This error message indicates one of the following: The OLE DB provider is not registered properly.
The name of the provider used in the provider_name parameter of sp_addlinkedserver (or specified in the OPENROWSET function) is incorrect.