linked server exec stored procdure results in Transaction context in use by another session.

linked server exec stored procdure results in Transaction context in use by another session.

• Maizatul Akmal

  Are you using looped back server If yes, then this is by design. What happens here is that the INSERT EXEC involves both local operation and remote operation. So SQL Server will promote the local auto-commit transaction to a distributed transaction when running the remote EXEC. But since the distributed transaction is still used by local server, the remote EXEC cannot start to run under the same transaction.
   
  You can workaround this by changing your code to not doing loopback INSERT EXEC.
  
  Please let me know if you need more help on this.
  
  

• kit thambiratnam

  This works just fine under 2000. There are actually some good reasons to use loopback for remote query execution. If the set on the remote server is much larger, it is cheaper to perform the query on the remote server and insert the results back at the host server.

  Here is an example that works just fine in 2000 but 2005 throws the error

  DECLARE @sql nvarchar(4000)

  CREATE TABLE #name(name sysname)

  SET @sql = 'Select name from '+@@serverName + '.master.dbo.sysobjects'

  INSERT INTO #name

  EXEC Controller.master.dbo.sp_ExecuteSQL @stmt = @sql

  select * from #name

  DROP TABLE #name

  OUTPUT

  Msg 3910, Level 16, State 2, Line 1

  Transaction context in use by another session.

  Has anyone found a correction for this problem that does not include changing the way the query works

  Thanks

  
  

• Mike Gabriele

  Hello! Thanks for posting your question.
  
  I would need some more details about your configuration.
  What is your linked server Is it SQL 2000 or SQL2005 Beta Does your remote stored procedure define any save points, start new transactions
  
  
  
  

• Bill C.

  Bear,

  Did you get a resolution to this

  
  

• Black Code Master

  Hello!
  
  As a side note, build  9.00.1116.00 is pretty old. Please try upgrading to later versions of SQL server 2005 (CTP16,RTM) and check if problem persists.
  
   
  
  

• PaulGauthier

  This is basically what I ended up doing and have not had a problem with it yet.
  
       DECLARE @ServerName varchar(255),
               @SQL nvarchar(MAX)
  
       DECLARE @tmp_results_db table ( DATABASE_NAME sysname,
                                       DATABASE_SIZE bigint,
                                       REMARKS nvarchar(3000)
                                     )
  
       SELECT @ServerName = ms.ServerName
       FROM   MyServers ms
       WHERE  ms.ServerID = 5
  
       IF @ServerName = @@SERVERNAME OR @ServerName = '127.0.0.1'
         BEGIN
           SELECT @ServerName = ''
         END
       ELSE
         BEGIN
           SELECT @ServerName = '[' + @ServerName + '].'
         END
  
       SELECT @SQL = @ServerName + 'master.dbo.sp_databases'
  
       INSERT @tmp_results_db
       EXECUTE @SQL
  
  Also note, that you need to make sure the MSDTC is opened up enough to allow the distributed transaction to work. Some simple instructions I found somewhere for doing this are:
  

  1. To allow the network transaction, you must enable MSDTC. To do this, follow these steps:

  a. Click Start, and then click Run.

  b. In the Run dialog box, type dcomcnfg.exe, and then click OK.

  c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer.

  d. Right-click My Computer, and then click Properties.

  e. In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.

  f. In the Security Configuration dialog box, click to select the Network DTC Access check box.

  g. To allow the distributed transaction to run on this computer from a remote computer, click to select the Allow Inbound check box.

  h. To allow the distributed transaction to run on a remote computer from this computer, click to select the Allow Outbound check box.

  i. Under the Transaction Manager Communication group, click to select the No Authentication Required option.

  j. In the Security Configuration dialog box, click OK.

  k. In the My Computer Properties dialog box, click OK.

  2. Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:

  a. Click Start, and then click Run.

  b. In the Run dialog box, type Firewall.cpl, and then click OK.

  c. In Control Panel, double-click Windows Firewall.

  d. In the Windows Firewall dialog box, click Add Program on the Exceptions tab.

  e. In the Add a Program dialog box, click Browse, and then locate the Msdtc.exe file. By default, the file is stored in the Installation drive:\Windows\System32 folder.

  f. In the Add a Program dialog box, click OK.

  g. In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list.

  h. Click Add Port on the Exceptions tab.

  i. In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

  j. In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK.

  k. In the Windows Firewall dialog box, select the name that you used for the exception in step j in the Programs and Services list, and then click OK.

  
  
  

• Chris_Bird

  This is my recomendation:

  Instead use:

  INSERT #TTB_TEMP

  EXEC LINKED_SERVER.DATABASE.dbo.StoreProcedure

  Instead use:

  INSERT #TTB_TEMP

  SELECT * FROM OPENQUERY(LINKED_SERVER, 'LINKED_SERVER.DATABASE.dbo.StoreProcedure')

  This Works for me.

  
  
  

• Becky N

  Ok, I changed my code to remove the IP Address if it is the local server and it works fine now for local calls. Thanks.

  CREATE TABLE #tmp_Results_db ( DATABASE_NAME sysname,
  DATABASE_SIZE bigint,
  REMARKS nvarchar(3000)
  )

  DECLARE @SQL nvarchar(4000)
  SELECT @SQL = 'master.dbo.sp_databases'
  INSERT #tmp_Results_db
  EXECUTE @SQL

  Only now for remote servers (leaving the IP Address in) I get the following error:

  OLE DB provider "SQLNCLI" for linked server "192.168.0.13" returned message "No transaction is active.".
  Msg 7391, Level 16, State 2, Line 9
  The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "192.168.0.13" was unable to begin a distributed transaction.

  I have the following settings for the linked server (192.168.0.13):

  Security - using the login's current security context (both servers have the same password for the SQL Server username I am using)

  Collation Compatible - False
  Data Access - True
  Rpc - True
  Rpc Out - True
  Use Remote Collation - True
  Collation Name -
  Connection Timeout - 0
  Query Timeout - 0

  When I execute the following statement, it works fine:

  exec [192.168.0.13].master.dbo.sp_databases

  Also, the following script works fine too

  declare @SQL nvarchar(4000)
  select @SQL = '[192.168.0.13].master.dbo.sp_databases'
  exec @SQL

  It is only when I try to combine it with an INSERT that I get an error.

  CREATE TABLE #tmp_Results_db ( DATABASE_NAME sysname,
  DATABASE_SIZE bigint,
  REMARKS nvarchar(3000)
  )

  DECLARE @SQL nvarchar(4000)
  SELECT @SQL = '[192.168.0.13].master.dbo.sp_databases'
  INSERT #tmp_Results_db
  EXECUTE @SQL

  Can you help me

   

  
  

• DEV01

  Hey even i am facing similar issue below query works for me on one set of servers and fails on another set of servers

  INSERT INTO rowcounts EXEC Pod1.channel.dbo.up_getRowCounts

  Pod1 in the query is link server name.

  Linked server property on both set of servers is same.

  I am getting following error:

  OLE DB provider "SQLNCLI" for linked server "Pod1" returned message "No transaction is active.".

  Msg 7391, Level 16, State 2, Line 2

  The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "Pod1" was unable to begin a distributed transaction.

  Is their any server level setting which i am missing. As this is working fine in development enviornment.

   

  TIA

  
  
  

• Cephid

  Bear, the problem with your example is that it does not fix the issue but avoids it. The case I am stuck with is that a cross-server query is actually necessary, and a loopback query is optimal. Let me describe the situation and why loopback is performing optimal

  LocalServer with a VerySmallRecordset
  RemoteServer witha VeryLargeRecordset

  the loopback basically does this:

  --On localserver
  Insert into localtable
  exec RemoteServer...sp_executesql '
  Select * from VLRecordset
  inner join LocalServer...VSRecordset
  where somerandomfilter'

  This throws loopback error in 2005 (works in 2000). It is optimal because it operates on the remoteserver where the VLRecorset is local and pull over the necessary data from the VSRecordset on the LocalServer

  You could do the same without the loopback

  Insert into localtable
  Select * from RemoteServer..VLRecordset
  inner join VSRecordset
  where somerandomfilter

  but is about 10 times slower due to the majority of the data you need is on the remote server.

  Thanks,
  Michael Mockus

  
  

• waifoong

  I have a similar issue that is giving me grief in SQL 2005. I have this code in SQL 2000 and has been working fine for years that checks to see if a stored procedure is on another database (maybe or maybe not in another server) and executes it. Now I have upgraded to SQL 2005 and I cannot get around this error (Transaction context in use by another session). Any suggestions on how I can accomplish this in SQL 2005

  Here is my code that works fine in SQL 2000 (@DBServer should be able to be any IP Address I have configured as a linked server):

  DECLARE @DBServer varchar(50)
  SELECT @DBServer = '127.0.0.1'

      CREATE TABLE #tmp_Results_Version ( Version     varchar(50),
                                          ReleaseDate datetime
                                        )
      CREATE TABLE #tmp_Results_db      ( DATABASE_NAME sysname,
                                          DATABASE_SIZE bigint,
                                          REMARKS nvarchar(3000)
                                        )
      CREATE TABLE #tmp_Results_procs   ( PROCEDURE_QUALIFIER sysname,
                                          PROCEDURE_OWNER     sysname,
                                          PROCEDURE_NAME      nvarchar(200),
                                          NUM_INPUT_PARAMS    int,
                                          NUM_OUTPUT_PARAMS   int,
                                          NUM_RESULT_SETS     int,
                                          REMARKS             varchar(254),
                                          PROCEDURE_TYPE      smallint
                                        )
  

                  SELECT @SQL = '[' + @DBServer + '].master.dbo.sp_databases'

                  INSERT  #tmp_Results_db
                  EXECUTE @SQL

                  IF (SELECT COUNT(*) FROM #tmp_Results_db WHERE DATABASE_NAME = @DBName) > 0
                    BEGIN
                      SELECT @SQL = '[' + @DBServer + '].' + @DBName + '.dbo.sp_stored_procedures'

                      INSERT  #tmp_Results_procs
                      EXECUTE @SQL

                      IF (SELECT COUNT(*) FROM #tmp_Results_procs WHERE PROCEDURE_NAME LIKE 'GetDBVersion%') > 0
                        BEGIN
                          SELECT @SQL = '[' + @DBServer + '].' + @DBName + '.dbo.GetDBVersion'

                          INSERT  #tmp_Results_Version
                          EXECUTE @SQL

                          SELECT @Version     = Version,
                                 @ReleaseDate = ReleaseDate
                          FROM   #tmp_Results_Version
                      END

   

  
  

• Fahad349

  The term loopback server was not clear to me. Looks like there is a default SQL server and you can add named SQL server running indepently.

  I created a named sql server, created the linked server between the default sql server and the named sql server and was able to use INSERT (local table) EXEC (remote SP). I used the install process from the setup.exe of the SQL Server 2005 DVD to create the named SQL server.

  How can I create a named sql server on command line

  sp_addserver @server='MySqlServer', @local='LOCAL'

  didn't work, do I miss something here

  Thanks

  
  

• rupart

  Is it documented somewhere in BOL that SQL Server does not officially support loopback queries I has working in SQL Server 2000 and I have been unable to find anywhere in BOL that this functionality would be removed.
  

• Pradosh

  This is the same problem as you posted before. The key here is that

  INSERT (local table) EXEC (remote SP)

  doesn't work when the remote SP is executed against loopback server.

  You may workaround this by running the SP directly against local server, or do not user loopback server in your query.

  SQL Server doesn't officially support loopback server scenario.

  Thanks!

   

   

  
  
  
• linked server exec stored procdure results in Transaction context in use by another session.