Executing Stored Procedures via ANT

On December 7, 2010, in SQL, ANT, by Anuj Gakhar

We are using ANT heavily in our current Project to automate builds for different environments. One of the requirements was to execute a Stored procedure from one of the ANT tasks. That sounds straightforward. Use the ANT Sql task and that should do it. But apprently, it turns out that using “EXEC” commands within a SQL Task does not work out very well.

Here is what I had :-

[xml]
<sql
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://DBServer"
userid="username"
password="password" >
exec myStoredProc @param1=’test’, @param2=’test2′
</sql>

[/xml]

That always comes up with an error like this :-

[xml]

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot perform a backup or restore operation within a transaction.

[/xml]

Now, the stored procedure I was trying to execute was doing some BACKUP and RESTORE operations but the reason it was failing was because ANT was trying to run it in a Transaction.

After spending a couple of hours troubleshooting this, the same command works if written with 2 extra parameters.

[xml]
<sql
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://DBServer"
userid="username"
password="password" keepformat="true" autocommit="true" >
exec myStoredProc @param1=’test’, @param2=’test2′
</sql>

[/xml]

My understanding is that, by telling ANT to use “autocommit” it commits the transaction and does not wait for a transaction response from the DB. Maybe someone who has more knowledge into this can shed some light but for now, all I can say, this works.

P.S. The database I was working with was SQL Server 2008 (for those who want to know).

Tagged with:  

12 Responses to Executing Stored Procedures via ANT

  1. radekg says:

    Hi Anuj,
    I think sql task for ant simply wraps your sql scripts with BEGIN TRANSACTION and COMMIT as well as ROLLBACK in case of any exception from the database. IF THIS ISN’T the case it might be due to not connecting to master database.

    Autocommit option name is probably little bit unfortunate for MSSQL as there is no such option for MSSQL, there is SET IMPLICIT_TRANSACTIONS {ON|OFF}. It might be worth trying executing this command while using master database (yes, connecting directly to master but you have to be sysadmin anyway).

    You can replicate this error in management studio with the following steps:

    – first go to Options / Query Execution / SQL Server / ANSI, tick SET IMPLICIT_TRANSACTIONS
    – reopen management studio
    – login with a username whose default database is master
    – execute BACKUP – should work fine
    – now change the database to anything else
    – execute BACKUP – should fail with a message looking like:

    Msg 3021, Level 16, State 0, Line 7
    Cannot perform a backup or restore operation within a transaction.
    Msg 3013, Level 16, State 1, Line 7
    BACKUP DATABASE is terminating abnormally.

    From that moment it will always fail, even if change the database back to master however it will work fine if you reopen management studio and continue working until changing the database again.

  2. Anuj Gakhar says:

    Hi Radek,

    That’s interesting. I did not try it in the Management Studio. But I will.

    But surely, there is something that “autocommit” is doing that is making the command work and bear in mind, I did not use the “master” database whehn I used the command.

  3. radekg says:

    I just asked that question on stacoverflow:
    http://stackoverflow.com/questions/4386296/mssql-2008-backup-with-implicit-transactions-on
    Is your stored proc executing any selects/updates/inserts before executing backup itself?
    If that’s the case AND sql task for ant set implicit_transactions to on by default then it would be the correct answer.

    Check the documentation for IMPLICIT_TRANSACTIONS:
    http://msdn.microsoft.com/en-us/library/aa259220(SQL.80).aspx

    “When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

    ALTER TABLE FETCH REVOKE
    CREATE GRANT SELECT
    DELETE INSERT TRUNCATE TABLE
    DROP OPEN UPDATE”

    • radekg says:

      Replying to this part: ‘ But surely, there is something that “autocommit” is doing that is making the command work and bear in mind, I did not use the “master” database whehn I used the command. ‘

      If sql task or the driver is by default using implicit transactions then I imagine setting autocommit would turn it off, by default every single statement is commited right after execution so rolling back has no effect and returns an error that there is no corresponding begin transaction unless you start your very own transaction.

      Now, if connection with that option set to on and issuing:
      print cast(@@trancount as nvarcbar(1))
      will print 1

      In that case “autocommit” would refer to default query commit in non transactional execution.

    • Anuj Gakhar says:

      The only SELECT I have before the BACKUP command is this, not sure if this counts as a proper SELECT or not as it does not select from any table.

      DECLARE @currentdate varchar(10)
      SELECT @currentdate = convert(varchar(8),getdate(),112)

  4. radekg says:

    Could you just try changing that select to

    set @currentdate = convert(varchar(8),getdate(),112)

    and try running that proc without autocommit? IMO select is select 😉

  5. Anuj Gakhar says:

    I will try that, but unfortunately, this will have to wait as the process that initiates this takes 30-40 mins and I will do it when I get some free time today/tomorrow.

  6. Anuj Gakhar says:

    But I do remember almost 100% that I put that SELECT statement at a very later stage in the SP and the error was there before that as well. But in any case, I will report the results of changing SELECT to SET.

  7. radekg says:

    Can you please the results?

  8. radekg says:

    I just tried that, seems like SELECT != SELECT, SELECT @var doesn’t affect it.
    My guess is that the task calls begin transaction which is suppressed with autocommit or maybe implicit transactions are on, it executes some of its internal selects before EXEC?
    Must be one of the two tho.

  9. radekg says:

    Hi Anuj,
    Just created this build file:

    exec myStoredProc @param1=’test’, @param2=’test2′

    and following stored proc:

    ALTER PROCEDURE [dbo].[myStoredProc]
    — Add the parameters for the stored procedure here
    @Param1 nvarchar(100)
    , @Param2 nvarchar(100)
    AS
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @d int
    select @d = 5
    backup database [test] to disk = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\test.bak’

    END

    When executed it returns:

    radosx:ant-test radek$ ant -lib libs/sqljdbc4.jar
    Buildfile: /Users/radek/Development/ant-test/build.xml

    main:
    [echo] Hello world…
    [sql][/sql] Executing commands
    [sql][/sql] 1 of 1 SQL statements executed successfully

    Do you have any specific settings? Any chance you can post contents of your stored proc?

Leave a Reply to Anuj Gakhar Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: