dbExecute and alter database statements

dnrickner

• November 17, 2011 00:00

• Answered

Follow

I have a dbExecute that is trying to call a stored procedure that alters a database and I get this error:

ERROR caused by: ALTER DATABASE statement not allowed within multi-statement transaction.

I am saying {call myproc} and have the option set to call as a stored procedure. Also I have tried setting it to never commit, but it always gives me that error. How can I get this to work?

• 

  dnrickner

  • November 18, 2011 15:33

  0

  Comment actions Permalink

  I managed to get it working by instead of calling as a procedure, I have exec myproc for the sql statement, transaction set = one statement, and call as stored procedure = false. This works, though I am confused as to why it doesn't work when executed as a stored procedure when that is what it really is.
• 

  twaller

  • November 21, 2011 15:18

  0

  Comment actions Permalink

  Hello dnrickner,
  
  could you let me know what your stored procedure was?
  
  Best regards,
  
  Tomas Waller
• 

  dnrickner

  • November 21, 2011 15:22

  0

  Comment actions Permalink

  Here is my procedure

  • sp_rename_extracts.sql
• 

  mkrivanek

  • November 24, 2011 11:07

  0

  Comment actions Permalink

  From the description of the error,
  

  ALTER DATABASE statement not allowed within multi-statement transaction

  
  it seems to me that the real cause of the problem was illegal value of the "Transaction set" attribute of the component.
  
  Could you try using the original SQL query, Call as stored procedure = true and Transaction set = One statement?
• 

  dnrickner

  • November 28, 2011 14:34

  0

  Comment actions Permalink

  Getting closer. It worked until I added 2 more lines to the procedure:
  
  use fred
  dbcc shrinkfile (fred_log, truncateonly)
  
  After adding these lines, I get this error:
  
  ERROR caused by: The executeUpdate method must not return a result set

  • sp_rename_extracts.sql
• 

  jraszyk

  • November 30, 2011 13:23

  0

  Comment actions Permalink

  The DBCC SHRINKFILE statement produces a Result Set, however our code does not expect any Result Set to be returned. I can see two ways out of this.
  
  1) Either adjust your procedure so that it does not produce any result sets, or
  2) set the Result set output fields property on the DBExecute component - this way the component will expect there will be a result set returned and will not report it as an error. You will need to attach appropriate output edge to the component in this case.

Please sign in to leave a comment.