windows SQLServer(MSSQL) 的自主事务（独立事务）

前几个月做了个项目要求使用postgresql，但是最近客户突然提出要使用MSSQL（SQL Server），本来只是迁移一下数据库的事，但是MSSQL居然是不支持独立事务的，后来查阅资料发现MSSQL虽然不支持，但是微软却给了一个扩展去支持类似ORACLE的自主事务；

前置准备

 1.安装SQLServer 和SSMS（我使用的2019）

  下载安装方法

 2.官网下载SSMA FOR ORALCE插件下载

  Microsoft SQL Server Migration Assistant for Oracle这个扩展是微软的官方扩展可以放心的使用 在项目中

  a.先点击下载

   b.再选择 对应的插件

  c.安装

    官方安装教程

扩展说明

        官方文档说明： 

            SQL Server 2014 does not support autonomous transactions. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection. To convert a procedure, function, or trigger with an AUTONOMOUS_TRANSACTION flag, you split it into two objects. The first object is a stored procedure containing the body of the converted object. It looks like it was converted without a PRAGMA AUTONOMOUS_TRANSACTION flag and is implemented as a stored procedure. The second object is a wrapper that opens a new connection where it invokes the first object. It is implemented via an original object type (procedure, function, or trigger).

          Use the xp_ora2ms_exec2 extended procedure and its extended version xp_ora2ms_exec2_ex, bundled with the SSMA 6.0 Extension Pack, to open new transactions. The procedure's purpose is to invoke any stored procedure in a new connection and help invoke a stored procedure within a function body. The xp_ora2ms_exec2 procedure has the following syntax:

  

xp_ora2ms_exec2
<active_spid> int,
<login_time> datetime,
<ms_db_name> varchar,
<ms_schema_name> varchar,
<ms_procedure_name> varchar,
<bind_to_transaction_flag> varchar,
[optional_parameters_for_procedure]

-- Where:
-- <active_spid> [input parameter] is the session ID of the current user process.
-- <login_time> [input parameter] is the login time of the current user process.
--  <ms_db_name> [input parameter] is the database name owner of the stored procedure.
--  <ms_schema_name> [input parameter] is the schema name owner of the stored  procedure.
--  <ms_procedure_name> [input parameter] is the name of the stored procedure.
--  optional_parameters_for_procedure [input/output parameter] are the procedure 
-- parameters.
-- In general, you can retrive the active_spid parameter from the @@spid system function. 
-- You can query the login_time parameter with the statement:
--  declare @login_time as datetime
--  select @login_time=start_time from sys.dm_exec_requests where 
-- session_id=@@spid

实际验证

 1.准备

--创建数据库
create database testdb;
go
use testdb;
go
--创建表
create table test_db(
t_no varchar(9) primary key,
t_desc varchar(200)
);

go
--创建一个insert的存储过程
create or alter procedure add_test_tb
@no varchar(max),
@name varchar(max)
as
begin
insert into test_db(t_no,t_desc) values(@no,@name);
end;
go

 

--测试代码
create or alter procedure myTestProcedure
@no varchar(max),
@name varchar(max)
as 
begin 
   -- insert  一条数据   1-test
   insert into test_db(t_no,t_desc) values('1','test1');
   -- 测试xp_ora2ms_exec2_ex
   declare @login_time datetime = getdate();
   declare @database_name sysname = db_name();
   exec master.dbo.xp_ora2ms_exec2_ex
		 @@spid,
		 @login_time,
		 @database_name,
		 'dbo',
		 'add_test_tb',
		 'N',
		 @no,
		 @name;
end;
go
--先删除所有数据
delete from test_db;
go
--在一个事务执行myTestProcedure 然后事务回滚
begin transaction
exec myTestProcedure '2','test2';
rollback transaction;
go
验证结果
select * from test_db;
go

执行结果，验证成功，数据库只有用xp_ora2ms_exec2_ex独立事务创建的2-test2数据

 补充说明

   目前这个扩展只能安装只能默认到到master用户的dbo下，作为系统扩展使用

   所以调用只能使用master.dbo.xp_ora2ms_exec2_ex使用