dbExecute and alter database statements

CloverDX Platform
1

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?

2

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.

3

Hello dnrickner,

could you let me know what your stored procedure was?

Best regards,

Tomas Waller

4

Here is my procedure

5

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?

6

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

7

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.