0

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.

I'm looking for a way without changing server settings/firewall to allow the insert from a linked server.

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.

If there are a few syntax issues I apologize, I stripped out specific information.

Error I'm getting with this code.

(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.

So far I've got:

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

Thank you in advance.

user2414546
  • 1
  • 1
  • This question is not clear. You mention returning one result, which implies select queries. Then you talk about inserts. – Dan Bracuk May 23 '13 at 19:15
  • Using the cursor to loop through the available servers, in the code I am attempting to insert into a temp table "INSERT INTO #EMPNOLOCATIONTEMP", with the results from each server, then selecting the temp table for one result set. – user2414546 May 23 '13 at 19:32

0 Answers0