Dynamically determine Linked Servers

In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done

CREATE FUNCTION dbo.stfGetSupervisorOrgUnitAssignments(@SupervisorID INT)

RETURNS @ReturnTable TABLE(Org_Unit INT PRIMARY KEY)

AS

BEGIN

DECLARE @Server VARCHAR(100)

SELECT @Server = dbo.stfGetLinkedServerName()

INSERT INTO @ReturnTable(Org_Unit)

SELECT ReplicationID FROM OPENQUERY(@Server, '....')

That helped. Thanks. One more question on this. Is there away to use the result set from the query

SET @SQLString = N'SELECT ReplicationID FROM OPENQUERY(' + @Server + ',

AND ReplicationID IS NOT NULL';

SET @ParmDefinition = N'@SupervisorIDParam int';

INSERT INTO @ReturnTable

EXECUTE sp_executesql @SQLString,

@ParmDefinition,

@SupervisorIDParam = @SupervisorID

This statement is being called in a function that returns a table

I run into this problem all the time.

You need to create a table (temp is fine) before you run your dynamic SQL. Then alter/use your dynamic SQL to populate the table. Then return the table in your function.

HTH

Dan