title: Exec SP on Linked server and put that in temp table
body: <p>Need some help on the below issue:</p>
  
  <p><strong>Case 1</strong> : stored procedure is on server 1 - call is from server1</p>
  
  <pre><code>declare @tempCountry table (countryname char(50))
  insert into @tempCountry
      exec [database1_server1].[dbo].[getcountrylist]
  Select * from @tempCountry
  </code></pre>
  
  <p>Result: successful execution</p>
  
  <p><strong>Case2</strong> : iIf this same stored procedure is being called from a different server using linked server like this :</p>
  
  <pre><code>declare @tempCountry table (countryname char(50))
  insert into @tempCountry
      exec [database2_server2].[database1_server1].[dbo].[getcountrylist]
  Select * from @tempCountry
  </code></pre>
  
  <p>Result</p>
  
  <blockquote>
    <p>Msg 7391, level 16, state 2, line 2<br>
    The operation could not be performed  because OLEDB provider "SQLNCLI" for linkedserver "Server2_Database2" was unable to begin a distributed transaction.</p>
  </blockquote>
  
  <p><strong>Case 3</strong></p>
  
  <p>But when tried to execute the stored procedure separately [without temp table insertion] like below </p>
  
  <pre><code>exec [database2_server2].[database1_server1].[dbo].[getcountrylist]
  </code></pre>
  
  <p>Result: that is executing the stored procedure without any error and returning data.</p>
  
  <hr>
  
  <p>I forgot to mention that am using SQL Server 2005. As per the server administrator, the feature you've suggested that I use is not available in 2005.</p>