Power BI调用带参SQL存储过程时出现Named Pipes连接错误

问题描述 #

在Power BI中调用带参数的SQL Server存储过程时，触发如下错误：

DataSource.Error: Microsoft SQL: Named Pipes Provider: Could not open a connection to SQL Server [2].

Details:

DataSourceKind=SQL

DataSourcePath=xxx-xxxxxx\xxxxxx;xxxx

Message=Named Pipes Provider: Could not open a connection to SQL Server [2].

ErrorCode=-2146232060

Number=2

Class=16

State=1

Power BI高级编辑器中的调用代码：

Source  = Sql.Database("xxx-xxxxxx\xxxxxx", "xxxx", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 'exec xxxx..sp_storedprocedure @StartDate=" & StartDate & ", @EndDate=" & EndDate & "')", CreateNavigationProperties=false])

已排除的排查项：

• SQL Server配置管理器中Named Pipes功能已启用，且Power BI其他查询可正常运行
• 多次重启SQL Server服务，问题未解决

对应的存储过程代码：

CREATE PROCEDURE [dbo].[sp_Demo_kpi] @StartDate datetime, @EndDate datetime

AS

--Set @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0);
--Set @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1);

SELECT [Snapshot_ID]
      ,[Cost to Complete]
      ,[Cost to Complete (SS)]
      ,[CtC PM Notes]
      ,[CtC Variance]
      ,[PCOs]
      ,[PM Manual CtC Override]
      ,[Previous Month (CtC)]
      ,[Project]
      ,[Project Number]
      ,[Projected Contract]
      ,[Projected Over/Under]
      ,[Remaining Exposure]
      ,[Revised Contract]
      ,[Total Cost Incurred]
      ,[UnitCode]
      ,[Approved COs]
      ,[Cost Code]
      ,[CPCOs]
      ,[Current Commitment]
      ,[Invoices]
      ,[Original Budget]
      ,[Original Commitment]
      ,[ImportDate]
      ,[CtC Modifications Notes (Previous Month)]
    ,@StartDate AS StartDate
    ,@EndDate AS EndDate
 FROM [Buckingham].[dbo].[tbl_UnitTracker_NonReno_Snapshot]
    WHERE ImportDate BETWEEN CONVERT(DATETIME, @StartDate, 101)
            AND CONVERT(DATETIME, @EndDate, 101);

解决方案 #

1. 移除OPENROWSET，直接调用存储过程

当前嵌套OPENROWSET的方式可能引发连接异常，改用直接调用的方式：

Source = Sql.Database("xxx-xxxxxx\xxxxxx", "xxxx", 
    [Query="exec dbo.sp_Demo_kpi @StartDate = '" & DateTime.ToText(StartDate, "yyyy-MM-dd HH:mm:ss") & "', @EndDate = '" & DateTime.ToText(EndDate, "yyyy-MM-dd HH:mm:ss") & "'", 
     CreateNavigationProperties=false])

注意：通过DateTime.ToText统一日期格式，避免因格式不匹配导致的隐式转换错误。

2. 使用Power BI原生参数化查询（推荐）

避免拼接SQL字符串，改用参数绑定的方式，更安全且支持查询折叠：

• 先在Power BI中创建两个datetime类型的参数StartDate和EndDate
• 替换为以下代码：

Source = Sql.Database("xxx-xxxxxx\xxxxxx", "xxxx"),
ExecProc = Value.NativeQuery(Source, 
    "exec dbo.sp_Demo_kpi @StartDate = @StartDateParam, @EndDate = @EndDateParam",
    [StartDateParam = StartDate, EndDateParam = EndDate],
    [EnableFolding = true])

该方式会自动处理参数类型映射，同时规避SQL注入风险。

3. 若坚持使用OPENROWSET，检查权限配置

• 确保执行Power BI的账号拥有ADMINISTER BULK OPERATIONS权限
• 开启SQL Server的Ad Hoc Distributed Queries选项：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

4. 验证存储过程本身的可用性

先在SSMS中执行存储过程，确认参数传递和结果返回正常：

exec dbo.sp_Demo_kpi @StartDate = '2024-01-01', @EndDate = '2024-01-31';

如果SSMS中执行正常，说明问题出在Power BI的调用逻辑上，而非存储过程本身。

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