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).
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.
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.
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”
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.
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)
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 😉
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.
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.
Can you please the results?
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.
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?
Hi Radek, the stored procedure I have is mainly taken from this page. http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx as I am basically trying to copy a DB into another via the ANT task. I made some minor changes to that SP but you should get the idea. My error was not on BACKUP – it was infact on the RESTORE command.