如何修改函数实现动态获取OPENDATASOURCE的服务器名称？

Absolutely, you can make this work while keeping your table-valued function! The core issue here is that OPENDATASOURCE requires a constant connection string—you can't dynamically inject variables like @SERVERNAME into it directly. The most reliable workaround is to use a CLR User-Defined Function (UDF), which lets you run dynamic logic, read your parameters table, and connect to remote servers all within the function.

Step 1: Enable CLR Integration in SQL Server #

First, turn on CLR integration for your server (if it’s not already enabled):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Step 2: Create the CLR Function Code #

Write a C# (or VB.NET) class library to handle the combined data logic:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillAllDataRow", TableDefinition = "Id int, Name varchar(100)")]
    public static IEnumerable fn_AllData()
    {
        DataTable combinedData = new DataTable();
        combinedData.Columns.Add("Id", typeof(int));
        combinedData.Columns.Add("Name", typeof(string));

        // 1. Fetch data from local Table1
        using (SqlConnection localConn = new SqlConnection("context connection=true"))
        {
            localConn.Open();
            using (SqlCommand localCmd = new SqlCommand("SELECT Id, Name FROM Table1", localConn))
            using (SqlDataReader reader = localCmd.ExecuteReader())
            {
                combinedData.Load(reader);
            }
        }

        // 2. Get remote server name from Parameters table
        string remoteServer = string.Empty;
        using (SqlConnection paramConn = new SqlConnection("context connection=true"))
        {
            paramConn.Open();
            using (SqlCommand paramCmd = new SqlCommand("SELECT Value FROM Parameters WHERE Name = 'SERVERNAME'", paramConn))
            {
                object serverValue = paramCmd.ExecuteScalar();
                if (serverValue != DBNull.Value)
                {
                    remoteServer = serverValue.ToString();
                }
            }
        }

        // 3. Fetch remote Table1 data if server name exists
        if (!string.IsNullOrEmpty(remoteServer))
        {
            string remoteConnString = $"Data Source={remoteServer};User ID=xxx;Password=yyy;Initial Catalog=Database1";
            using (SqlConnection remoteConn = new SqlConnection(remoteConnString))
            {
                remoteConn.Open();
                using (SqlCommand remoteCmd = new SqlCommand("SELECT Id, Name FROM dbo.Table1", remoteConn))
                using (SqlDataReader reader = remoteCmd.ExecuteReader())
                {
                    combinedData.Load(reader);
                }
            }
        }

        return combinedData.Rows;
    }

    // Helper method to map DataRow to SQL table columns
    public static void FillAllDataRow(object rowObj, out SqlInt32 id, out SqlString name)
    {
        DataRow row = (DataRow)rowObj;
        id = new SqlInt32((int)row["Id"]);
        name = new SqlString((string)row["Name"]);
    }
}

Step 3: Deploy the CLR Function to SQL Server #

1. Compile the C# code into a DLL.
2. Deploy the DLL and create the SQL function:

-- Set database to trustworthy (required for external access)
ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON;

-- Create the assembly from your DLL
CREATE ASSEMBLY AllDataFunction 
FROM 'C:\Path\To\Your\Compiled\DLL\File.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS; -- Needed to connect to remote servers

-- Create the SQL function that maps to the CLR logic
CREATE FUNCTION dbo.fn_AllData()
RETURNS TABLE (Id int, Name varchar(100))
AS EXTERNAL NAME AllDataFunction.UserDefinedFunctions.fn_AllData;

Step 4: Use the Function as Before #

You can call the function exactly like your original implementation:

SELECT * FROM dbo.fn_AllData();

Alternative Workarounds (With Limitations) #

If you can’t use CLR, here are other options, but they have tradeoffs:

• Dynamic Linked Servers: Create a linked server dynamically via a stored procedure, then use OPENQUERY in your function. However, this causes concurrency issues (if multiple users run the function simultaneously) and requires elevated permissions to modify linked servers.
• Stored Procedure + Temporary Table: Replace the function with a stored procedure that uses dynamic SQL to fetch remote data. But this breaks your requirement of keeping a function, as you can’t call stored procedures directly from SELECT statements like you can with functions.

内容的提问来源于stack exchange，提问作者Jortx