Script - SQL Server RECONFIGURE Statement

dataservices job design
1

Hello All,

I recognize this may be more of a SQL Server issue, however I was wondering if anyone has dealt with the following:

I have a need to change some SQL Server configuration settings in our batch load via a sp_configure statement. Calling the stored proc in a script object during the load is my best bet.

Unfortunately when I try to issue a RECONFIGURE command to set the changes I get the following error:

“CONFIG statement cannot be used inside a user transaction”

sql(‘DS’, 'EXEC sp_configure ‘show advanced options’, 1);
sql(‘DS’, ‘RECONFIGURE’);

I have also tried to embed the call to this sp_configure and RECONFIGURE in another SP I created however that will not run either.

If anyone has a workaround I would appreciate it! Stumped.

LucaBrasi23 (BOB member since 2008-09-15)

2

sql() run the statement that you pass to it in a transaction, you can try calling a commit before executing the stored proc to close the transaction and see if that works

something as below
sql(‘DS’, ‘COMMIT GO RECONFIGURE’);

manoj_d (BOB member since 2009-01-02)