聽說幸福…在山的那邊 (浪機子)

今天在執行SQL Procedure  時遇到的問題…

由於 OLE DB Provider 'SQLOLEDB' 無法開始分散式交易，所以無法執行此操作。
1                             =000103=2008/01=0                            
[OLE/DB provider returned message: 在指定異動協調員中無法編列新異動。]

找出問題…
因為程式很短，在經過不斷的測試後，發現是和遠端連線的那台SERVER有關，
但是單獨執行那段語法(在寫入另一個Procedure中)又是ok的…
所以 達人森 建議我，一段一段的執行，來找出問題…

所以呢…整個程式就被我點的一片綠綠的囉…

最後才發現原來是錯在 
Transaction  這呢…經過 達人森 的解釋是…因為是在本機上做的Transaction Log
所以無法遠端去RollBack…

建議的作法如下
1．改寫程式為一次即可執行完畢，如此就不須要做Transaction 了
(依據我的程式上來看呢，就是要用PIVOT來解
2．將Procedure放在遠端的的那台主機上，然後利用本機來exec 囉
3．手動做Rollback... 
4．不要做 Rollback

尤於我的程式不是挺重要的…所以選擇了第四點…

程式內容
------------------------------------------------------------------------------------------------------------
alter      PROCEDURE   Tmp_20081101
AS
Declare
 @ErrorSave   int,
 @ErrMsg           char(50),               /*記錄錯誤訊息*/
 @Content   varchar(1000),
 @Subject   varchar(100),
 @SystemDate   char(10),  /*系統日*/
 @ExecuteTime   datetime,  /*Batch執行時間*/
 @EmpID    CHAR(6),
 @InsertCnt   int,
 @m1    int,
 @m2    int,
 @m3    int,
 @m4    int,
 @m5     int,
 @m6    int,
 @m7    int,
 @m8    int,
 @m9    int,
 @m10    int,
 @m11    int,
 @m12    int,
 @DateY    char(4),
 @DateYM    char(7),
 @int    int,
 @Hour    int,
 @RecCnt    int

Set nocount on

set @EmpID=''
set @InsertCnt = 0

Select @SystemDate = (Select Convert(char(10),getdate(),111) as SystemDate)
Select @ExecuteTime = (Select Convert(char(19),getdate(),20) as ExecuteTime)

select @DateY  = convert(char(4),getdate(),111)

print '***************************************'
print '*** Tmp_20081101  OverTime-T start ***'

Create Table  ##Table_OVT ( EmpID char(6), m1 int,m2 int,m3 int,m4 int,m5 int,
    m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int
   )

--If @ErrorSave != 0
--  goto err_Control

BULK INSERT ##Table_OVT  FROM 'C:\overtimeT.txt'  
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
)

Declare cur_OV Cursor for
select EmpID,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12 from  ##Table_OVT
order by EmpID
Open cur_OV
Fetch next from cur_OV
Into @EmpID,@m1,@m2,@m3,@m4,@m5,@m6,@m7,@m8,@m9,@m10,@m11,@m12
--Begin Transaction
while (@@fetch_status = 0)
Begin

 set @int = 1
 while  @int < 13
  begin
   set @DateYM = '00' + CAST(@int AS CHAR)    
   set @DateYM = @DateY + '/' + substring(@DateYM,len(@DateYM)-1,2)
   set @Hour=0

   select @Hour = case  when @int =1 then @m1
           when @int= 2 then @m2 
    when @int= 3 then @m3
    when @int= 4 then @m4
    when @int= 5 then @m5
    when @int= 6 then @m6
    when @int= 7 then @m7
    when @int= 8 then @m8
    when @int= 9 then @m9
    when @int= 10 then @m10
    when @int= 11 then @m11
    when @int= 12 then @m12
            end   

print  cast(@int as char) + '=' +  @EmpID + '='+ @DateYM +  '=' + cast(@Hour as char) 
        
    INSERT  [10.11.12.185\BPM].[BPMFlowRule].[dbo].[TTHR_OverTimeN]
    SELECT  @EmpID,@DateYM,@Hour,''

    set @ErrorSave = @@Error
    set @ErrMsg = 'insert TTHR_OverTimeN err  '
    If @ErrorSave != 0
     goto err_Control

  set @InsertCnt = @InsertCnt + 1
 
  set @int = @int + 1   
   end

 Fetch next from cur_OV
 Into @EmpID,@m1,@m2,@m3,@m4,@m5,@m6,@m7,@m8,@m9,@m10,@m11,@m12 
end

--Commit Transaction
close cur_OV
deallocate cur_OV

DROP TABLE ##Table_OVT

print '*** Tmp_20081101  OverTime-T  end    ***'  + cast(@InsertCnt as int) 
print '***************************************'

Set nocount off
Return 0

err_Control:   
 --Rollback Transaction
 DROP TABLE ##Table_OVW
 close cur_OV
 deallocate cur_OV
 set nocount off
 Set @Subject = '*** Tmp_20081101  Error ***' + @EmpID + '=' + cast(@InsertCnt as char) + '='  + @ErrMsg
 Set @Content = 'SystemDate = ' + cast(@SystemDate as char) + ',' + 'ExecuteTime = ' +  cast(@ExecuteTime as char)  + ',' + 'EmpID = ' + @EmpID + ',' + 'ErrorSave = ' + cast(@ErrorSave as char)
 print @Subject + ','
 print @Content

Return 1
skip:
 Return 1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO