sql2005:Cannot use SAVE TRANSACTION within a distributed transaction

sql2005:Cannot use SAVE TRANSACTION within a distributed transaction

• Tim Brassey

  javamick, i could not reproduce your problem, maybe it's something else.  This works fine when remote proc trans is enabled or disabled:

  create table t1 (col1 int)
  go
  create trigger trig1 on t1 for update
  as
   declare @x sysname
   set @x = 'test1_' + cast(getdate() as varchar(20))
   EXEC msdb.dbo.sp_add_job @job_name = @x
  go

  insert into t1 values (1)
  go
  update t1 set col1 = 2
  go

   

  Is replication involved in this problem

  
  

• Sonic_Molson

  Replication is not involved...sorry for posting here (I was just posting since the person had the same error).  I guess I should have mentioned, that in addition to sp_add_job, the following are used:
  
  sp_add_jobstep
  sp_add_jobserver
  sp_start_job
  
  I'll try to work up some demo code in just a sec, but if this helps answer it, let me know.
  
  Thanks,
  Micky
  
  

• ChandraSiva

  Do you have remote proc trans enabled on your server  
  exec sp_configure 'show advanced' ,1
  go
  reconfigure with override
  go
  exec sp_configure
  go
  
  If so do you know why it is enabled and is it possible to turn this off
  
  To turn it off
  
  exec sp_configure 'remote proc trans',0
  go
  reconfigure with override
  
  

• jorrit5477

  OK, here is the code, but let me say this.  This does work in SQL.  The problem is when the update is called from a COM object and the transaction was started using DTC:
  
  CREATE TABLE t1 (col1 int)
  go
  CREATE TRIGGER trig1 ON t1 FOR UPDATE
  AS
  DECLARE @x AS sysname
  DECLARE @jobid varchar(38)
  DECLARE @jobname sysname
  DECLARE @GUID varchar(38)
  DECLARE @stepname sysname
  DECLARE @sCommand nvarchar(200)
  
  IF UPDATE(col1)
      BEGIN
              SET @jobid = NULL
  
              SET @GUID = NEWID()
              SET @stepname = 'test step 1'
              SET @jobname = CAST('test' + @GUID AS sysname)
              SET @sCommand = 'SELECT TOP 1 * FROM t1'
  
              EXEC msdb.dbo.sp_add_job @job_name = @jobname
                          ,  @enabled =  1 
                          ,  @description =  '' 
                           ,  @owner_login_name =  NULL 
                           ,  @notify_level_eventlog =  2
                           ,  @delete_level = 1
                           ,  @job_id =  @jobid OUTPUT 
     
              EXEC msdb.dbo.sp_add_jobstep @job_id = @jobid
                  , @step_name = @stepname
                  , @subsystem = 'TSQL'
                  , @command = @sCommand
                  , @on_success_action = 1
                  , @on_fail_action = 2
                  , @database_name = 'Playground'
                  , @database_user_name = NULL
                  , @retry_attempts = 3
                  , @retry_interval = 1
  
              EXEC msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = NULL
              EXEC msdb.dbo.sp_start_job @job_id = @jobid           
  
      END
  GO
  
  insert into t1 values (1)
  go
  update t1 set col1 = 2
  go
  
  
  Any ideas
  
  Thanks,
  Micky
  
  

• PitBull.ja

  I posted some of this same information to the microsoft.public.sqlserver.server group.  We narrowed it down to the sp_add_jobstep_internal in SQL 2005.  Making a change in that sp makes it so the error goes away.  What I need to find out is this:  is this an error that should be corrected in SQL Server or should it not even be possible to do this
  
  If it is something that should be corrected in SQL Server, where can I submit it
  
  Thanks,
  Micky
  
  BTW, here is the link to the newsgroup post:
  http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/b4882e6ffd004b25/
  
  I hope I did the link right.  The subject is "SQL 2005: Error with Trigger"
  
  

• JodyByrd

  Hi Micky, thanks for your post, just wanted to let you know that your specific issue is actually a known issue in SQL 2005, and is slated to be fixed in the next release of SQL Server.  I'll raise awareness internally about this and point them to your post, but I can't guarantee you anything will be done for SQL 2005.

  
  

• DaFeiFan

  Thought I would post one additional link.  Someone else had posted this to MS as a bug.  If this is affecting you, please vote for the bug here:
  http://lab.msdn.microsoft.com/ProductFeedback/ViewWorkaround.aspx FeedbackID=FDBK42832#1
  
  Thanks,
  Micky
  

• MARK E6581

  I have the same error.  Mine was not caused by altering the table though.  Mine was caused by calling sp_add_job from a trigger.  I have still not solved it.  I've tried running it with "remote proc trans" set both to a 0 and a 1.
  
  Any ideas
  
  Thanks,
  Micky
  
  

• sjoshi

  Tried that and it didn't work.
  The problem is now solved.
  
  The stored procedure that was entering data was altering the table as part of the transaction. (Dropping the primary key before insert and returning it after...)
  This isn't possible if the table is meant for replication
  
  
• sql2005:Cannot use SAVE TRANSACTION within a distributed transaction