这个错误不是头一天遇到过,但今天特别恼火!散分…………
在这大家提个醒,若有谁能圆满解决,请说一声,在此谢过首先,说一下系统环境:
.Net Framework v1.1
Oracle 8.17i
VS.Net 2003
页面拖入以下控件:
三个 Button 与 一个 TextBox
oracleConnection1,oracleCommand1
oleDbConnection1,oleDbCommand1
oleDbConnection2,oleDbCommand2一、一个简单的存储过程
create or replace procedure A
is
begin
insert into AAA
values('asd','gfd');
rollback;
--commit; --若为提交则不报错
end A;二、 .cs 代码1、使用 OracleConnect 连接
private void Button1_Click(object sender, System.EventArgs e)
{
this.oracleConnection1.ConnectionString = "user id=Test;data source=oraTest1;password=12346";
this.oracleCommand1.CommandText = "A";
this.oracleCommand1.CommandType = System.Data.CommandType.StoredProcedure;
this.oracleCommand1.Connection = this.oracleConnection1;
this.oracleConnection1.Open();
this.TextBox1.Text = this.oracleCommand1.ExecuteNonQuery().ToString();
this.oracleConnection1.Close();
}2、使用 OleDbConnect 连接,Oracle Provider for OLE DB 方式
private void Button2_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = "Provider=\"OraOLEDB.Oracle\";User ID=Test;Data Source=oraTest1;Extended Properties=;Persist Security Info=True;Password=123456";
this.oracleCommand1.CommandText = "A";
this.oracleCommand1.CommandType = System.Data.CommandType.StoredProcedure;
this.oracleCommand1.Connection = this.oracleConnection1;
this.oleDbConnection1.Open();
this.TextBox1.Text = this.oleDbCommand1.ExecuteNonQuery().ToString();
this.oleDbConnection1.Close();
}3、使用 OleDbConnect 连接,Microsoft OLE DB Provider for Oracle 方式
private void Button3_Click(object sender, System.EventArgs e)
{
this.oleDbConnection2.ConnectionString = "Provider=\"MSDAORA.1\";User ID=Test;Data Source=oraTest1;Password=123456";
this.oleDbCommand2.CommandText = "A";
this.oleDbCommand2.CommandType = System.Data.CommandType.StoredProcedure;
this.oleDbCommand2.Connection = this.oleDbConnection2;
this.oleDbConnection2.Open();
this.TextBox1.Text = this.oleDbCommand2.ExecuteNonQuery().ToString();
this.oleDbConnection2.Close();
}以上只有第 3 种方法测试通过,第 1、2 种方法都报错误,分别如下:System.Data.OracleClient.OracleException: ORA-02074: cannot ROLLBACK in a distributed transaction ORA-06512: at "TEST.A", line 6 ORA-06512: at line 1System.Data.OleDb.OleDbException: ORA-02074: cannot ROLLBACK in a distributed transaction ORA-06512: at "TEST.A", line 6 ORA-06512: at line 1PS:1、连接肯定没问题
2、把存储过程中的“rollback”去掉或是改成“commit”,那么以上三种方法都行执行通过
3、听说是 Oracle 的 OLEDB 数据驱动有问题,哪位高手知道详情?
在这大家提个醒,若有谁能圆满解决,请说一声,在此谢过首先,说一下系统环境:
.Net Framework v1.1
Oracle 8.17i
VS.Net 2003
页面拖入以下控件:
三个 Button 与 一个 TextBox
oracleConnection1,oracleCommand1
oleDbConnection1,oleDbCommand1
oleDbConnection2,oleDbCommand2一、一个简单的存储过程
create or replace procedure A
is
begin
insert into AAA
values('asd','gfd');
rollback;
--commit; --若为提交则不报错
end A;二、 .cs 代码1、使用 OracleConnect 连接
private void Button1_Click(object sender, System.EventArgs e)
{
this.oracleConnection1.ConnectionString = "user id=Test;data source=oraTest1;password=12346";
this.oracleCommand1.CommandText = "A";
this.oracleCommand1.CommandType = System.Data.CommandType.StoredProcedure;
this.oracleCommand1.Connection = this.oracleConnection1;
this.oracleConnection1.Open();
this.TextBox1.Text = this.oracleCommand1.ExecuteNonQuery().ToString();
this.oracleConnection1.Close();
}2、使用 OleDbConnect 连接,Oracle Provider for OLE DB 方式
private void Button2_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = "Provider=\"OraOLEDB.Oracle\";User ID=Test;Data Source=oraTest1;Extended Properties=;Persist Security Info=True;Password=123456";
this.oracleCommand1.CommandText = "A";
this.oracleCommand1.CommandType = System.Data.CommandType.StoredProcedure;
this.oracleCommand1.Connection = this.oracleConnection1;
this.oleDbConnection1.Open();
this.TextBox1.Text = this.oleDbCommand1.ExecuteNonQuery().ToString();
this.oleDbConnection1.Close();
}3、使用 OleDbConnect 连接,Microsoft OLE DB Provider for Oracle 方式
private void Button3_Click(object sender, System.EventArgs e)
{
this.oleDbConnection2.ConnectionString = "Provider=\"MSDAORA.1\";User ID=Test;Data Source=oraTest1;Password=123456";
this.oleDbCommand2.CommandText = "A";
this.oleDbCommand2.CommandType = System.Data.CommandType.StoredProcedure;
this.oleDbCommand2.Connection = this.oleDbConnection2;
this.oleDbConnection2.Open();
this.TextBox1.Text = this.oleDbCommand2.ExecuteNonQuery().ToString();
this.oleDbConnection2.Close();
}以上只有第 3 种方法测试通过,第 1、2 种方法都报错误,分别如下:System.Data.OracleClient.OracleException: ORA-02074: cannot ROLLBACK in a distributed transaction ORA-06512: at "TEST.A", line 6 ORA-06512: at line 1System.Data.OleDb.OleDbException: ORA-02074: cannot ROLLBACK in a distributed transaction ORA-06512: at "TEST.A", line 6 ORA-06512: at line 1PS:1、连接肯定没问题
2、把存储过程中的“rollback”去掉或是改成“commit”,那么以上三种方法都行执行通过
3、听说是 Oracle 的 OLEDB 数据驱动有问题,哪位高手知道详情?
insert into AAA
values('asd','gfd');
rollback;
放到另一个存储过程中试一下呢/
create or repalce procedure b as
PRAGMA AUTONOMOUS_TRANSACTION
begin
insert into AAA
values('asd','gfd');
rollback;end;
/
存储过程已做尝试,但还是会不行1、PRAGMA AUTONOMOUS_TRANSACTION 只出现在 A
create or repalce procedure A as
PRAGMA AUTONOMOUS_TRANSACTION; --此处分号不可少
begin
insert into AAA
values('asd','gfd');
rollback;
end;
_____________________________________________2、PRAGMA AUTONOMOUS_TRANSACTION 只出现在 B
create or repalce procedure A as
begin
B; --调用存储过程 B
end;
create or repalce procedure B as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into AAA
values('asd','gfd');
rollback;
end;
_____________________________________________3、PRAGMA AUTONOMOUS_TRANSACTION 只出现在 A,调用 B
create or repalce procedure A as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
B;
end;
create or repalce procedure B as
begin
insert into AAA
values('asd','gfd');
rollback;
end;
_____________________________________________4、PRAGMA AUTONOMOUS_TRANSACTION 出现在 A 与 B
create or repalce procedure A as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
B;
end;
create or repalce procedure B as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into AAA
values('asd','gfd');
rollback;
end;
在oracle9i里OracleConnect + Microsoft OLE DB Provider for Oracle 方式 有时候会成功
不知道oracle10g有没有这问题。
在连接字符串中加Enlist=false;
试试
在连接字符串中加Enlist=false;
__________________________________________試過,不行。
在连接字符串加
OLE DB Services=-7;
OLE DB Services=-7;
________________________________
已试,无用
非常感谢!!