none
[錯誤訊息]已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應 RRS feed

  • 問題

  • 作業行為:

    有兩個資料庫:來源資料庫和目的資料庫

    目地資料庫transaction

    若有值,以下這三個會不斷重覆

    {

    sel來源資料庫之資料表之值

    insert目地庫裡的資料表

    update來源的資料表 <=就在updat來源資料表之值時,會出現:已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應。

    可有時又不會有逾時的問題。把字串拿到sqlserver run很快就跑出來了。

    煩請各位大大提供個方向思考可能是什麼導致,謝謝各位大大了~~

    }

    沒值了,全都insert完畢了

    目地資料庫commit

    2007年6月21日 上午 08:09

解答

  • DataReader 是用產生伺服器游標(Server-side cursor)的方法來讀資料,在游標的有效期間內,資料表都會被鎖定。

    而 DataAdapter 是把資料寫入 DataTable/DataSet,沒有產生游標,所以不會鎖定資料表。

    2007年6月22日 上午 02:16
    版主
  • 你可以在執行程式的同時,使用活動管理員 (SQL Server 2005) 去看目前運作的執行緒,就會知道有沒有被鎖住 .

    不過通常若是只有目地伺服器連線有交易的話,來源應不會被鎖住才是 .

    可能有其他原因導致來源物件被鎖住了 .

     

    我都是以 DataTable/DataSet 來做,盡可能避免開二個以上的連線 .

    2007年6月24日 上午 09:16
    版主

所有回覆

  • 你的來源資料表可能被鎖定了,讓 UPDATE 無法被執行。

    如果你的來源資料表是用 DataReader 去讀的話,就有可能發生這樣的狀況。

    你可以:

    • 改用 DataTable/DataSet 搭配 DataAdapter 去讀取資料。
    • 若是 SQL Server 2005,可利用 MARS (Multiple Active Result Set) 來讓表格不被鎖定。
    2007年6月21日 上午 08:12
    版主
  • 小弟不解

    為何DataReader 會發生lock而DataAdapter 不會呢?

    (改寫成DataAdapter 測試中)

    謝謝大大的幫忙哦

     

    2007年6月22日 上午 01:15
  • DataReader 是用產生伺服器游標(Server-side cursor)的方法來讀資料,在游標的有效期間內,資料表都會被鎖定。

    而 DataAdapter 是把資料寫入 DataTable/DataSet,沒有產生游標,所以不會鎖定資料表。

    2007年6月22日 上午 02:16
    版主
  • 可是DataReader 有reader.Close();

    為什麼游標還在呢?

    2007年6月22日 上午 03:01
  • 你的原始問題並沒有程式碼,誰曉得你有沒有 close...

    2007年6月22日 上午 03:09
    版主
  • 大大sorry

    因為對cursor不了解,不了解reader.close, cursor就會跟著關掉,所以才會那樣問,

     

    再次請教大大

    來源與目地之伺服器相同, 資料庫相異

    tranction = 目地伺服器connection

    會不會影響來源被鎖住呢

     

    謝謝大大的辛苦回答

     

     


     

    2007年6月23日 上午 06:58
  • 你可以在執行程式的同時,使用活動管理員 (SQL Server 2005) 去看目前運作的執行緒,就會知道有沒有被鎖住 .

    不過通常若是只有目地伺服器連線有交易的話,來源應不會被鎖住才是 .

    可能有其他原因導致來源物件被鎖住了 .

     

    我都是以 DataTable/DataSet 來做,盡可能避免開二個以上的連線 .

    2007年6月24日 上午 09:16
    版主
  • 謝謝大大,再次請教大大

    大大避開二個以上的連線有什麼考量呢?(@@不懂事的我每次reader就開一個connection,怕開開關關負荷大,後來改為4)

    用的是SQL Server 2000,可有如SQL Server 2005活動管理員的工具可供使用

     

    來源和目地相同伺服器,不同資料庫,使用3connection

     connection1 = 來源

    connection2 = 來源(reader中又有reader,所以又多做了一個connection,使一個reader有一個connection)

     connection 3 = 目地(tranction)

     connection 4 = 目地(判斷這筆資料是否己存在用,這個沒和 connection 3一起用,因為怕影響tranction)<=請教大大,會有影響嗎有沒有比較好的處理方式(寫法)/其它的處理方式?

    try

    {

    目地tranction

    for (int i = 0 ;I<最後一筆;i++)

    {

    select 來源

    insert目地

    upd來源

    }

    tranction commit

    }

    catch

    {

    rollback

    }

    若將upd來源收集,for都跑完再做,就不會發生錯誤@@ <=請教大大們, 有可能是什麼造成呢?

    再次謝謝大大

    2007年6月25日 上午 02:44
  • 避免使用二個以上的連線,就是為了要避掉不必要的資源鎖定。

    若 DataReader 要存取到一個被 Transaction 鎖定住的資源,那不就變成死結(Deadlock)了。

     

    SQL Server 2000 有可以看資料庫活動的工具,只是我忘了在哪裡(好久沒用 SQL Server 2000 了)

    2007年6月25日 上午 08:55
    版主
  • Code Snippet

     

    string srcConn = "Data Source=Server;Initial Catalog=src;Persist Security Info=True;User ID = sa;Password="";               

    string destConn = "Data Source=Server;Initial Catalog=dest;Persist Security Info=True;User ID = sa;Password="";
                   

    openConn(srcConn,destConn);

     test();

       

    Code Snippet

    public static class  csConn
        {//給沒有tran INS/UPD的用,給讀了就放的
            public static SqlConnection objConn_PDM = new SqlConnection();

            public static SqlConnection objConn_ERP = new SqlConnection();

            public static SqlCommand objCmd_PDM = new SqlCommand("", objConn_PDM);

            public static SqlCommand objCmd_ERP = new SqlCommand("", objConn_ERP);

     

            public static SqlConnection objConnTran_PDM = new SqlConnection();
            public static SqlConnection objConnTran_ERP = new SqlConnection();

            public static SqlTransaction TranForERP, TranForPDM;

            public static SqlCommand objCmdUpdPart_PDM = new SqlCommand("", objConnTran_PDM);
            public static SqlCommand objCmdUpdPrj_PDM = new SqlCommand("", objConnTran_PDM);
            public static SqlCommand objCmdInsPart_ERP = new SqlCommand("", objConnTran_ERP);
            public static SqlCommand objCmdInsHead_ERP = new SqlCommand("", objConnTran_ERP);
            public static SqlCommand objCmdInsBody_ERP = new SqlCommand("", objConnTran_ERP);

     

            public static Boolean SqlDataReader_HasRows(string queryString, Boolean isPDM)
            {
                Boolean isHasRows = false;

                Int32 count;
                if (isPDM)
                {
                    objCmd_PDM.CommandText = queryString;
                    if (objConn_PDM.State != ConnectionState.Open) objConn_PDM.Open();

                    count = (Int32)objCmd_PDM.ExecuteScalar();
                }
                else
                {
                    objCmd_ERP.CommandText = queryString;
                    if (objConn_ERP.State != ConnectionState.Open) objConn_ERP.Open();
                    count = (Int32)objCmd_ERP.ExecuteScalar();
                }
                if (count > 0)
                    isHasRows = true;
                return isHasRows;
            }

            public static Boolean openConn(string conn_PDM,string conn_ERP)
            {
                isConnOpen = false;
                try
                {
                    if (objConn_PDM.State == ConnectionState.Open) objConn_PDM.Close();
                    objConn_PDM.ConnectionString = conn_PDM;
                    objConn_PDM.Open();
                    if (objConn_ERP.State == ConnectionState.Open) objConn_ERP.Close();
                    objConn_ERP.ConnectionString = conn_ERP;
                    objConn_ERP.Open();
                    if (objConnTran_PDM.State == ConnectionState.Open) objConnTran_PDM.Close();
                    objConnTran_PDM.ConnectionString = conn_PDM;
                    objConnTran_PDM.Open();
                    if (objConnTran_ERP.State == ConnectionState.Open) objConnTran_ERP.Close();
                    objConnTran_ERP.ConnectionString = conn_ERP;
                    objConnTran_ERP.Open();
                    isConnOpen = true;
                }
                catch
                { return false; }
                return isConnOpen;
            }

            public static string SqlDataReader_Field(string queryString, Boolean isPDM)
            {
                string List = "";
                SqlDataReader reader;
                if (isPDM)
                {
                    objCmd_PDM.CommandText = queryString;
                    if (objConn_PDM.State != ConnectionState.Open) objConn_PDM.Open();
                    reader = objCmd_PDM.ExecuteReader();
                }
                else
                {
                    objCmd_ERP.CommandText = queryString;
                    if (objConn_ERP.State != ConnectionState.Open) objConn_ERP.Open();
                    reader = objCmd_ERP.ExecuteReader();
                }
                if (reader.Read())
                    List = reader[0].ToString();
                reader.Close();

                return List;
            }

    }

     

       

    Code Snippet

    class csDTSDB
        {

            private void TranExec_Part(string queryString)
            {          

                csConn.objCmdInsPart_ERP.Transaction = csConn.TranForERP;
                csConn.objCmdInsPart_ERP.CommandText = queryString;
                csConn.objCmdInsPart_ERP.ExecuteNonQuery();

            }
            private void TranExec_BOMVer(string queryString) //執行
            {          
                csConn.objCmdInsHead_ERP.Transaction = csConn.TranForERP;
                csConn.objCmdInsHead_ERP.CommandText = queryString;
                csConn.objCmdInsHead_ERP.ExecuteNonQuery();           

         }
            private void TranExec_BOM(string queryString) 
            {         

                csConn.objCmdInsBody_ERP.Transaction = csConn.TranForERP;
                csConn.objCmdInsBody_ERP.CommandText = queryString;
                csConn.objCmdInsBody_ERP.ExecuteNonQuery();
            }

     

            public void testReadTree(string queryString)
            {
                if (csConn.objConn_PDM.State != ConnectionState.Open) csConn.objConn_PDM.Open();
                SqlCommand command = new SqlCommand(queryString, csConn.objConn_PDM);
                command.CommandTimeout = csConn.CmdTimeout;
                using (SqlDataReader reader = command.ExecuteReader())
                { }
            }
            public void testReadPDM(string queryString)
            {
                csConn.objCmd_PDM.CommandText = queryString;
                csConn.objCmd_PDM.CommandTimeout = csConn.CmdTimeout;
                if (csConn.objConn_PDM.State != ConnectionState.Open) csConn.objConn_PDM.Open();
                using (SqlDataReader reader = csConn.objCmd_PDM.ExecuteReader())
                { }
            }
    public string test()
    {
        string cmd = "";   
            try
            {
                if (csConn.objConn_ERP.State != ConnectionState.Open) csConn.objConn_ERP.Open();            
                csConn.TranForERP = csConn.objConnTran_ERP.BeginTransaction("Test");                          
                // TRANSACTION

                cmd = " SELECT B.ParentID, B.ParentVersion, B.ParentType, B.ChildID,B.ChildVersion, B.ChildType,B.BOM_ST, B.UseQty, B.Status,   CONVERT(varchar, SUBSTRING(CONVERT(varbinary,CONVERT(varchar, B.BOM_Note)),1, 255)) AS BOM_Note,    CONVERT(varchar, SUBSTRING(CONVERT(varbinary, P.ChiUnit),1, 4)) AS ChiUnit,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Pos),1, 4)) AS Pos,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Create_User),1, 10)) AS Create_User, CONVERT(char(8), B.Create_Date, 112) AS Create_Date FROM PDM_TreeBOM B LEFT OUTER JOIN PDM_Part P ON B.ChildID = P.ID where ParentType='A' and ParentID='20070702-1' and ParentVersion=1 order by ChildID,ChildType";

                testReadTree(cmd);
                cmd = " SELECT B.ParentID, B.ParentVersion, B.ParentType, B.ChildID,B.ChildVersion, B.ChildType,B.BOM_ST, B.UseQty, B.Status,   CONVERT(varchar, SUBSTRING(CONVERT(varbinary,CONVERT(varchar, B.BOM_Note)),1, 255)) AS BOM_Note,    CONVERT(varchar, SUBSTRING(CONVERT(varbinary, P.ChiUnit),1, 4)) AS ChiUnit,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Pos),1, 4)) AS Pos,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Create_User),1, 10)) AS Create_User, CONVERT(char(8), B.Create_Date, 112) AS Create_Date FROM PDM_TreeBOM B LEFT OUTER JOIN PDM_Part P ON B.ChildID = P.ID where ParentType='A' and ParentID='20070702-11' and ParentVersion=1 order by ChildID,ChildType";
              
              testReadTree(cmd);

                cmd = "select User_11 from PDM_Part where ID='20070702-11'"; //chk
                         csConn.SqlDataReader_Field(cmd, true);
                cmd = "select User_11 from PDM_Part where ID='20070702-2'";
                     csConn.SqlDataReader_Field(cmd, true);
                cmd = "select User_11 from PDM_Part where ID='20070702-2'";
               
                csConn.SqlDataReader_Field(cmd, true);

                cmd = "select count(*) from INVMB where MB001='20070702-1'"; //chk
          
                 csConn.SqlDataReader_HasRows(cmd, false);
                cmd = " select COMPANY='VPDM',MB019='N',MB020='N',MB022='N',MB042='1',MB044='N',MB052='N',MB066='N',MB083='N',MB085='N',MB087='N',MB091='N',MB092='N',MB096='1',MB098='N',MB100='N',MB101='N',MB102='N',MB103='N',MB104='N',MB105='N',MB106='N',MB107='N',MB108='N',MB109='N',MB110='N',MB121='N',ID,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then ID  else null end as MB010,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then '****' else null end as MB011,case when  User_11=''  or  User_11 is null then 'P'  else convert(varchar,substring(convert(varbinary,User_11),1,1)) end  as User_11,case when   User_5=''  or  User_5 is null then 'L'  else  convert(varchar,substring(convert(varbinary,User_5),1,1)) end  as User_5,case when   User_7=''  or  User_7 is null then '0'  else  User_7 end  as User_7,case when   User_2=''  or  User_2 is null then '0'  else  User_2 end  as User_2,case when   User_4=''  or  User_4 is null then '0'  else convert(varchar,substring(convert(varbinary,User_4),1,1)) end  as User_4,Name=convert(varchar,substring(convert(varbinary,Name),1,30)),ChiUnit=convert(varchar,substring(convert(varbinary,ChiUnit),1,4)),Spec=convert(varchar,substring(convert(varbinary,Spec),1,30)),User_1=convert(varchar,substring(convert(varbinary,User_1),1,10)),User_3=convert(varchar,substring(convert(varbinary,User_3),1,10)),User_12=convert(varchar,substring(convert(varbinary,User_12),1,3)),User_13=convert(varchar,substring(convert(varbinary,User_13),1,4)),User_14=convert(varchar,substring(convert(varbinary,User_14),1,3)),User_15=convert(varchar,substring(convert(varbinary,User_15),1,4)),User_16=convert(varchar,substring(convert(varbinary,User_16),1,3)),Create_User=convert(varchar,substring(convert(varbinary,Create_User),1,10)),Update_User=convert(varchar,substring(convert(varbinary,Update_User),1,10)),Convert (char(8),Create_Date,112) as Create_Date, Convert (char(8),Update_Date,112) as Update_Date from PDM_Part where ID='20070702-1'";
                
                testReadPDM(cmd);
                cmd = " insert into INVMB ( MB001,MB002,MB003,MB004,MB005,MB006,MB010,MB011,MB017,MB018,MB019,MB020,MB022,MB025,MB032,MB034,MB036,MB038,MB042,MB043,MB044,MB052,MB066,MB067,MB068,MB083,MB085,MB087,MB091,MB092,MB096,MB098,MB100,MB101,MB102,MB103,MB104,MB105,MB106,MB107,MB108,MB109,MB110,MB121 ,COMPANY,CREATE_DATE,CREATOR,MODIFIER,MODI_DATE)  values('20070702-1','','','','','','','','','','N','N','N','P','','L',0,0,'1','0','N','N','N','','','N','N','N','N','N','1','N','N','N','N','N','N','N','N','N','N','N','N','N','VPDM','20070702','Sa','','')";
        
                TranExec_Part(cmd);
                cmd = "select   COUNT(*) AS count from BOMMC where MC001='20070702-1'";
             
                csConn.SqlDataReader_HasRows(cmd, false);
                cmd = "SELECT         B.PartID,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Description),1, 255)) AS Description,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary,                           P.ChiUnit), 1, 4)) AS ChiUnit,  CONVERT(varchar,                           SUBSTRING(CONVERT(varbinary, P.User_6), 1, 4)) AS User_6,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Create_User), 1, 10))  AS Create_User,  CONVERT(char(8), B.Create_Date, 112) AS Create_Date,  'VPDM' AS COMPANY, '1' AS MC004, '0001' AS MC009 FROM             PDM_TreeBOMVer B LEFT OUTER JOIN                  PDM_Part P ON B.PartID = P.ID where PartID='20070702-1'";
                
                testReadPDM(cmd);
                cmd = "insert into BOMMC ( MC001,MC002,MC004,MC005,MC009,MC010,CREATE_DATE,CREATOR,COMPANY)values ( '20070702-1','','1','','0001','','VPDM','20070702','Sa')";
               
                TranExec_BOMVer(cmd);
                cmd = "delete  BOMMD   where MD001='20070702-1'";
                
                TranExec_BOM(cmd);
                cmd = "select count(*) from INVMB where MB001='20070702-11'";
                
                csConn.SqlDataReader_HasRows(cmd, false);
                 cmd =" select COMPANY='VPDM',MB019='N',MB020='N',MB022='N',MB042='1',MB044='N',MB052='N',MB066='N',MB083='N',MB085='N',MB087='N',MB091='N',MB092='N',MB096='1',MB098='N',MB100='N',MB101='N',MB102='N',MB103='N',MB104='N',MB105='N',MB106='N',MB107='N',MB108='N',MB109='N',MB110='N',MB121='N',ID,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then ID  else null end as MB010,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then '****' else null end as MB011,case when  User_11=''  or  User_11 is null then 'P'  else convert(varchar,substring(convert(varbinary,User_11),1,1)) end  as User_11,case when   User_5=''  or  User_5 is null then 'L'  else  convert(varchar,substring(convert(varbinary,User_5),1,1)) end  as User_5,case when   User_7=''  or  User_7 is null then '0'  else  User_7 end  as User_7,case when   User_2=''  or  User_2 is null then '0'  else  User_2 end  as User_2,case when   User_4=''  or  User_4 is null then '0'  else convert(varchar,substring(convert(varbinary,User_4),1,1)) end  as User_4,Name=convert(varchar,substring(convert(varbinary,Name),1,30)),ChiUnit=convert(varchar,substring(convert(varbinary,ChiUnit),1,4)),Spec=convert(varchar,substring(convert(varbinary,Spec),1,30)),User_1=convert(varchar,substring(convert(varbinary,User_1),1,10)),User_3=convert(varchar,substring(convert(varbinary,User_3),1,10)),User_12=convert(varchar,substring(convert(varbinary,User_12),1,3)),User_13=convert(varchar,substring(convert(varbinary,User_13),1,4)),User_14=convert(varchar,substring(convert(varbinary,User_14),1,3)),User_15=convert(varchar,substring(convert(varbinary,User_15),1,4)),User_16=convert(varchar,substring(convert(varbinary,User_16),1,3)),Create_User=convert(varchar,substring(convert(varbinary,Create_User),1,10)),Update_User=convert(varchar,substring(convert(varbinary,Update_User),1,10)),Convert (char(8),Create_Date,112) as Create_Date, Convert (char(8),Update_Date,112) as Update_Date from PDM_Part where ID='20070702-11'";
                
                testReadPDM(cmd);
                 cmd =" insert into INVMB ( MB001,MB002,MB003,MB004,MB005,MB006,MB010,MB011,MB017,MB018,MB019,MB020,MB022,MB025,MB032,MB034,MB036,MB038,MB042,MB043,MB044,MB052,MB066,MB067,MB068,MB083,MB085,MB087,MB091,MB092,MB096,MB098,MB100,MB101,MB102,MB103,MB104,MB105,MB106,MB107,MB108,MB109,MB110,MB121 ,COMPANY,CREATE_DATE,CREATOR,MODIFIER,MODI_DATE)  values('20070702-11','','','','','','','','','','N','N','N','P','','L',0,0,'1','0','N','N','N','','','N','N','N','N','N','1','N','N','N','N','N','N','N','N','N','N','N','N','N','VPDM','20070702','Sa','','')";
             
                TranExec_Part(cmd);
                 cmd ="insert into BOMMD  ( COMPANY,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE, MD001,MD002,MD003,MD004, MD006,MD007,MD009,MD010,  MD013,MD014,MD016,MD017,MD018)values ('VPDM','Sa','','20070702','','','20070702-1','1','20070702-11','',1,1,'****','1','N','Y','','1',0)";
                         TranExec_BOM(cmd);
                 cmd ="select count(*) from INVMB where MB001='20070702-2'";
                             csConn.SqlDataReader_HasRows(cmd, false);
                cmd =" select COMPANY='VPDM',MB019='N',MB020='N',MB022='N',MB042='1',MB044='N',MB052='N',MB066='N',MB083='N',MB085='N',MB087='N',MB091='N',MB092='N',MB096='1',MB098='N',MB100='N',MB101='N',MB102='N',MB103='N',MB104='N',MB105='N',MB106='N',MB107='N',MB108='N',MB109='N',MB110='N',MB121='N',ID,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then ID  else null end as MB010,case  when convert(varchar,substring(convert(varbinary,User_11),1,1)) ='M' or  convert(varchar,substring(convert(varbinary,User_11),1,1))='S' then '****' else null end as MB011,case when  User_11=''  or  User_11 is null then 'P'  else convert(varchar,substring(convert(varbinary,User_11),1,1)) end  as User_11,case when   User_5=''  or  User_5 is null then 'L'  else  convert(varchar,substring(convert(varbinary,User_5),1,1)) end  as User_5,case when   User_7=''  or  User_7 is null then '0'  else  User_7 end  as User_7,case when   User_2=''  or  User_2 is null then '0'  else  User_2 end  as User_2,case when   User_4=''  or  User_4 is null then '0'  else convert(varchar,substring(convert(varbinary,User_4),1,1)) end  as User_4,Name=convert(varchar,substring(convert(varbinary,Name),1,30)),ChiUnit=convert(varchar,substring(convert(varbinary,ChiUnit),1,4)),Spec=convert(varchar,substring(convert(varbinary,Spec),1,30)),User_1=convert(varchar,substring(convert(varbinary,User_1),1,10)),User_3=convert(varchar,substring(convert(varbinary,User_3),1,10)),User_12=convert(varchar,substring(convert(varbinary,User_12),1,3)),User_13=convert(varchar,substring(convert(varbinary,User_13),1,4)),User_14=convert(varchar,substring(convert(varbinary,User_14),1,3)),User_15=convert(varchar,substring(convert(varbinary,User_15),1,4)),User_16=convert(varchar,substring(convert(varbinary,User_16),1,3)),Create_User=convert(varchar,substring(convert(varbinary,Create_User),1,10)),Update_User=convert(varchar,substring(convert(varbinary,Update_User),1,10)),Convert (char(8),Create_Date,112) as Create_Date, Convert (char(8),Update_Date,112) as Update_Date from PDM_Part where ID='20070702-2'";
                testReadPDM(cmd);
              cmd =" insert into INVMB ( MB001,MB002,MB003,MB004,MB005,MB006,MB010,MB011,MB017,MB018,MB019,MB020,MB022,MB025,MB032,MB034,MB036,MB038,MB042,MB043,MB044,MB052,MB066,MB067,MB068,MB083,MB085,MB087,MB091,MB092,MB096,MB098,MB100,MB101,MB102,MB103,MB104,MB105,MB106,MB107,MB108,MB109,MB110,MB121 ,COMPANY,CREATE_DATE,CREATOR,MODIFIER,MODI_DATE)  values('20070702-2','','','','','','','','','','N','N','N','P','','L',0,0,'1','0','N','N','N','','','N','N','N','N','N','1','N','N','N','N','N','N','N','N','N','N','N','N','N','VPDM','20070702','Sa','','')";
                           TranExec_Part(cmd);
                cmd ="insert into BOMMD  ( COMPANY,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE, MD001,MD002,MD003,MD004, MD006,MD007,MD009,MD010,  MD013,MD014,MD016,MD017,MD018)values ('VPDM','Sa','','20070702','','','20070702-1','2','20070702-2','',1,1,'****','1','N','Y','','1',0)";
                
                TranExec_BOM(cmd);
                 cmd ="select   COUNT(*) AS count from BOMMC where MC001='20070702-11'";
                 csConn.SqlDataReader_HasRows(cmd, false);//跑到這行時, 就會發生錯誤
               cmd ="SELECT         B.PartID,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Description),1, 255)) AS Description,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary,                           P.ChiUnit), 1, 4)) AS ChiUnit,  CONVERT(varchar,                           SUBSTRING(CONVERT(varbinary, P.User_6), 1, 4)) AS User_6,  CONVERT(varchar, SUBSTRING(CONVERT(varbinary, B.Create_User), 1, 10))  AS Create_User,  CONVERT(char(8), B.Create_Date, 112) AS Create_Date,  'VPDM' AS COMPANY, '1' AS MC004, '0001' AS MC009 FROM             PDM_TreeBOMVer B LEFT OUTER JOIN                  PDM_Part P ON B.PartID = P.ID where PartID='20070702-11'";

                 testReadPDM(cmd);
            cmd ="insert into BOMMC ( MC001,MC002,MC004,MC005,MC009,MC010,CREATE_DATE,CREATOR,COMPANY)values ( '20070702-11','','1','','0001','','VPDM','20070702','Sa')";
           
                  TranExec_BOMVer(cmd);
                 cmd ="delete  BOMMD   where MD001='20070702-11'";
                 TranExec_BOM(cmd);
                 cmd ="select count(*) from INVMB where MB001='20070702-2'";
                             csConn.SqlDataReader_HasRows(cmd, false);
      
                csConn.TranForERP.Commit();
                csConn.objConnTran_ERP.Close();

            return "DB_Trans_Succeed";
            }
        catch (Exception er)
        {
            csConn.TranForERP.Rollback();
            return "DB_Trans_Fail";
        }
      }

    }

     

    20070702-1 20070702-11 20070702-2
      20070702-2  

    20070702-1 子 20070702-11

    20070702-11 子 20070702-2

    20070702-1   子 20070702-2

     

     

     

     各位大大好~~

    以上是我的式碼

    select來源, select目地,insert目地,delete目地, 交錯使用

    來源table: PDM_Part,PDM_TreeBOMVer,PDM_TreeBOM

    目地table: INVMB,BOMMC,BOMMD

    當執行到最後一段cmd ="select count(*) from INVMB where MB001='20070702-2'";時,就會發生錯誤:

    Type: System.Data.SqlClient.SqlException

    Message: 已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應。

    目前有發現, 只要在trancaction裡有重覆的節點就會發生

    麻煩大大幫忙怎麼會有這種問題, 怎麼解決呢

     

    謝謝各位大大~~

     

    2007年7月2日 上午 03:57
  • 使用C#, ADO.NET,
    錯誤訊息
    Type: System.Data.SqlClient.SqlException
    Message: 已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應。

     

    去查找發現
    52: 正遭到另一個連線封鎖的處理序 (SPID)。
    54: 正在阻礙一個或多個連線的處理序 (SPID)。
    52和54是連到相同資料庫的CONNECTION, 差異在於:54有TRANCACTION,52沒有,52是專明拿來讀取資料的]

    52是public static SqlConnection objConn_ERP = new SqlConnection();

    54是public static SqlConnection objConnTran_ERP = new SqlConnection();

    請教大大~~
      這是什麼意思呢?怎麼樣可以解決這樣的問題呢?

     

    謝謝大大

     

    2007年7月2日 上午 09:30
  •  

    trancaction

    用沒有trancactionconnselect aa

    trancactionconninsert aa

    用沒有trancactionconnselect aa

    不知為何?就造成沒有trancactionconn和有trancactionconn互相lock(或是阻擋)(查詢sp_whoblk,不曉得這樣是lock或阻擋)

    然後就出現錯誤訊息

     

    在發現是相同conn blk造成conn逾時的錯誤訊息

    需將select目地和insert目地都要在同一個conn && 同一個trans

    而之前我只有insert在同一個conn && 同一個trans

    select是獨立一個conn

     

    不過這樣就使人好奇

    為何不同一個conn會造成這樣的現象呢

     

    謝謝各位大大的幫忙^^

    2007年7月4日 上午 08:24
  • 找到了

    在使用手冊打上sp_lock就可找到相關說明

    如何檢視目前的鎖定 (Enterprise Manager)

    若要檢視目前的鎖定

    1. 展開伺服器群組,然後展開伺服器。

    2. 展開 [管理],然後展開 [目前活動]。

    3. 執行下列其中之一:
      • 展開 [鎖定/處理序識別碼] 以檢視各連線的目前鎖定。
      • 展開「鎖定/物件」以檢視各物件目前的鎖定。
    4. 在主控台樹狀目錄中,按一下要檢視的連線 (SPID) 或物件。

      連線或物件的目前鎖定會顯示於詳細資料窗格中。

    2007年7月4日 上午 08:36
  • 同問會出現這種情況啊
    Code如下:
      try
                {
                    transferid = this.GetTransferIDForDelete();//這個里面會開一個鏈接獲取ID,但是鏈接絕對確保關閉了
                    con = new SqlConnection(ConfigHelper.ConnectionString);
                    con.Open();
                    tran = con.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tran;
                    sql = "delete from salarydetail where transfer_id = " + transferid.ToString();
                    sql += " delete from salarygeneral where transfer_id = " + transferid.ToString();
                    sql += " delete from salarysourcesum where transfer_id = " + transferid.ToString();
                    if (m_TransferType == TransferType.Salary)
                    {
                        sql += " delete from pensioninfo where transfer_id = " + transferid.ToString();
                    }
                    sql += " delete from datatransferlog where transfer_id = " + transferid.ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    throw new Exception();
                }
                finally
                {
                    con.Close();
                }
    拜托大大噢。
    2007年7月26日 上午 03:21