dbExecute and transactions

CloverDX Platform
1

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?

2

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, …)

3

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)
4

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.

5

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

6

Thank you for your help on this. I will try what you suggest.