title: SQL Server : Query Multiple Databases within Multiple Linked Servers, Return One result set
body: <p>I have multiple linked servers, containing many databases. I'm trying to search through all the server/databases for specific information and return back one result set within SQL. </p>
  
  <p>I'm looking for a way without changing server settings/firewall to allow the insert from a linked server.</p>
  
  <p>I thought about creating a table on the linked servers, then pull them in, but I don't want to do that. Mainly because many people will be running this at once, and it feels sloppy.</p>
  
  <p>If there are a few syntax issues I apologize, I stripped out specific information.</p>
  
  <p>Error I'm getting with this code.</p>
  
  <p>(2 row(s) affected)
  OLE DB provider "SQLNCLI10" for linked server "server1" returned message "The transaction manager has disabled its support for remote/network transactions.".
  Msg 7391, Level 16, State 2, Line 3
  The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server1" was unable to begin a distributed transaction. </p>
  
  <p>So far I've got:</p>
  
  <pre><code>DECLARE @SERVER VARCHAR(20),@EmpNo VARCHAR(10)
  SET @EmpNo = '0000000001'
  
  CREATE TABLE #EMPNOLOCATIONTEMP ( ServerName nvarchar(50),CompanyCode nvarchar(20), EmpNo nvarchar(20), Name nvarchar(500) )
  
  DECLARE @TEMP TABLE (servername nvarchar(50)) -- remove and find table with information
  insert into @TEMP
  values('server1'),('server2'),('server3')
  
  
  DECLARE db_cursor CURSOR FOR  
  
      SELECT ServerName
      FROM @TEMP
  
  OPEN db_cursor   
  FETCH NEXT FROM db_cursor INTO @SERVER   
  
  WHILE @@FETCH_STATUS = 0   
  BEGIN   
  
      INSERT INTO #EMPNOLOCATIONTEMP
      --EXECUTE [dbo].usp_Ulti_EmpNo_Search @EmpNo,@SERVER
              EXECUTE('
  
              EXECUTE(''
  
              CREATE table #Results (CompanyCode nvarchar(20), EmpNo nvarchar(20), Name nvarchar(500))
  
              EXECUTE sp_MSforeachdb ''''USE ?; 
  
              IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[TableWithInfo]'''''''') )
              BEGIN
                  INSERT INTO #Results
                  select CompanyCode,EmpNo,Name
                  from TableWithInfo
                  where EmpNo = '''''''''+@EmpNo+'''''''''
              END
              ''''
  
              SELECT * FROM #Results
  
              '') AT '+@SERVER+'
  
              ')
  
      UPDATE #EMPNOLOCATIONTEMP SET ServerName = @SERVER WHERE ServerName is null
  
  FETCH NEXT FROM db_cursor INTO @SERVER 
  END   
  
  CLOSE db_cursor   
  DEALLOCATE db_cursor
  
  
  SELECT * FROM  #EMPNOLOCATIONTEMP 
  
  DROP TABLE #EMPNOLOCATIONTEMP
  </code></pre>
  
  <p>Thank you in advance.</p>