Is there a way to turn off transactions for queries running in a dbExecute? I have a call to a system procedure and MSSQL 2008 R2 gives me this error message:
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.
How can I turn off transactions on this node?
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.
How can I turn off transactions on this node?
-
Hi dnrickner,
because there is a lot of exact hits on google (http://www.google.cz/search?rlz=1C1GGGE ... 00&bih=785) for your message, I would expect it is general problem.
If not, please provide us more information (executed statement, graph, db structure, ...) -
I am not doing anything fancy like triggers. Also, my script runs just fine inside management studio. I only have an issue when I execute the script from within Clover. Here is my script:
declare @source_system varchar(30)
declare @extract_type varchar(20)
declare @db_instance varchar(20)
declare @username varchar(20)
declare @password varchar(20)
declare @link_name varchar(20)
declare @db_engine varchar(20)
declare @provider_name varchar(20)
declare @sql varchar(4000)
set @source_system = 'banner'
set @extract_type = (select value from global_variables
where variable = 'extract_type'
)
set @db_instance = (select db_instance from extract_params
where source_system = @source_system and type = @extract_type
)
set @username = (select username from extract_params
where source_system = @source_system and type = @extract_type
)
set @password = (select dbo.base64decode(password) from extract_params
where source_system = @source_system and type = @extract_type
)
set @db_engine = (select db_engine from extract_params
where source_system = @source_system and type = @extract_type
)
set @link_name = 'oracle'
if exists (select 1 from sys.servers where name = @link_name and is_linked = 1)
exec sp_dropserver @link_name, 'droplogins'
if(@db_engine = 'oracle')
set @provider_name = 'OraOLEDB.Oracle'
else set @provider_name = ''
set @sql = 'exec master.dbo.sp_addlinkedserver
@server=''' + @link_name + ''',
@srvproduct=''' + @db_engine + ''',
@provider=''' + @provider_name + ''',
@datasrc=''' + @db_instance + ''''
print '/****************************************************/'
print '/****************************************************/'
print @sql
exec (@sql)
set @sql = 'exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=''' + @link_name + ''',
@useself=''False'',
@locallogin=NULL,
@rmtuser=''' + @username + ''',
@rmtpassword=''' + @password + ''''
print '/***************************************************/'
print '/***************************************************/'
print @sql
exec (@sql) -
Hi dnricker,
just for sure: did you tried to play with combinations of "Call as stored procedure" and "Transaction set"?
There is similar http://forum.cloveretl.com/viewtopic.ph ... f0b9#p7784 thread, created by you. So I expect you tried it.
We will try to reproduce your problem. -
Hi dnrickner,
I was able to reproduce your problem. I simplified your stored procedure to less dependent on your infrastructure ('customer' is table in my database - may be arbitrary):
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.TestKubosJ
AS
BEGIN
declare @db_instance varchar(20)
declare @link_name varchar(20)
declare @db_engine varchar(20)
declare @provider_name varchar(20)
declare @sql varchar(4000)
-- any select somehow opens transaction - just when called from CloverETL through JDBC
-- comment it out to demonstrate it
set @db_instance = (select name from customers where customer_id=15)
set @link_name = 'Test'
set @db_engine = 'oracleXXX'
set @provider_name = 'OraOLEDB.OracleXXX'
set @db_instance = 'server.in.brno'
-- this helps when called by cloveretl (with default settings of DbExec and enabled select above)
-- but fails when called from sql server console
-- commit
if exists (select 1 from sys.servers where name = @link_name and is_linked = 1)
exec sp_dropserver @link_name, 'droplogins'
set @sql = 'exec master.dbo.sp_addlinkedserver
@server=''' + @link_name + ''',
@srvproduct=''' + @db_engine + ''',
@provider=''' + @provider_name + ''',
@datasrc=''' + @db_instance + ''''
print '/****************************************************/'
print @sql
print '/****************************************************/'
exec (@sql)
END
So there are 2 things you can do:
* set DbExecute property "Transaction set" to "One statement"
* add commit statement after last "select" and before first "exec" in your procedure
Please sign in to leave a comment.
Comments 5