SQL 批量更新错误 - 不允许启动新请求，因为它应该带有有效的事务描述符

Question

我正在运行 microsoft sql 2008 和 jdbc 驱动程序 3.0，并且在批量 SQL 更新时收到此错误

“不允许启动新请求，因为它应该带有有效的事务描述符”

为了调试，我已将批处理大小减少到只有一个语句，但仍然错误。这是

IF EXISTS (SELECT * FROM StaffDetail WHERE PsnID = 'GC91') 
    UPDATE staffdetail 
    SET    psnid = 'GC91', 
           servicegroup = '41B001', 
           discipline = 'IT', 
           dob = '1967-09-28', 
           ghdstartdate = '2008-12-15', 
           yearsexperience = '11 to 20 years', 
           classification = 'Admin Officer 1' 
    WHERE  psnid = 'GC91' 
ELSE 
    INSERT INTO staffdetail 
                (psnid, 
                 servicegroup, 
                 discipline, 
                 dob, 
                 ghdstartdate, 
                 yearsexperience, 
                 classification) 
    VALUES      ('GC91', 
                 '41B001', 
                 'IT', 
                 '1967-09-28', 
                 '2008-12-15', 
                 '11 to 20 years', 
                 'Admin Officer 1') 

为什么会出错？我用 google 搜索了这个，只找到了 SQL 2005 中的错误的引用，而不是 2008 中的错误。

有什么方法可以更改用于连接的连接字符串或更改我在批处理中使用的 SQL 语句来尝试避免此错误？

Answer 1

尝试将 SQL 更改为：

UPDATE StaffDetail SET PsnID = 'GC91',ServiceGroup = '41B001',Discipline = 'IT',DOB = '1967-09-28',GHDStartDate = '2008-12-15',YearsExperience = '11 to 20 years',Classification = 'Admin Officer 1' WHERE PsnID = 'GC91';

INSERT INTO StaffDetail (PsnID,ServiceGroup,Discipline,DOB,GHDStartDate,YearsExperience,Classification) 
SELECT 'GC91','41B001','IT','1967-09-28','2008-12-15','11 to 20 years','Admin Officer 1'
WHERE NOT EXISTS (SELECT * FROM StaffDetail WHERE PsnID = 'GC91') 

Answer 2

您可以将上述代码作为存储过程定义并使用 con.prepareStatement 传递吗？

Answer 3

更新较晚，以防其他人发现我的发现有用......

我最近经常遇到这个问题。重新构建查询以使用 CTE 或表变量通常可以解决该问题，但相信最终，如 http://computer-aaaargh.blogspot.co.za/2012/12/weird-cause-of-msg-3989-new-request-is.html ，它必须做与基础数据。查看您的联接并尝试更改您从基础数据中获取的内容 - 在这种情况下，只有一个语句（尝试 SELECT 1 FROM StaffDetail WHERE PsnID = 'GC91'）或使用存在中的另一个脚本作为一种形式消除。

Answer 4

您可以MERGE 来删除 IF和 ELSE 这使得它实际上是一个语句

MERGE INTO staffdetail AS TARGET 
USING (VALUES ('GC91', 
      '41B001', 
      'IT', 
      '1967-09-28', 
      '2008-12-15', 
      '11 to 20 years', 
      'Admin Officer 1')) AS SOURCE (psnid, servicegroup, discipline, dob, 
      ghdstartdate, yearsexperience, classification) 
ON TARGET.psnid = SOURCE.psnid 
WHEN MATCHED THEN 
  UPDATE SET servicegroup = SOURCE.servicegroup, 
             discipline = SOURCE.discipline, 
             dob = SOURCE.dob, 
             ghdstartdate = SOURCE.ghdstartdate, 
             yearsexperience = SOURCE.yearsexperience, 
             classification = SOURCE.classification 
WHEN NOT MATCHED BY TARGET THEN 
  INSERT (psnid, 
          servicegroup, 
          discipline, 
          dob, 
          ghdstartdate, 
          yearsexperience, 
          classification) 
  VALUES ('GC91', 
          '41B001', 
          'IT', 
          '1967-09-28', 
          '2008-12-15', 
          '11 to 20 years', 
          'Admin Officer 1')