运行 SQL Server 存储过程的 Power Query 数据类型转换问题 [英] Power Query data type conversion issue running SQL Server stored procedure

问题描述

最新版本的 Excel M365 和 SQL Server 2017.

我有一个存储过程，它以 YYYY-MM-DD 格式将日期作为输入 - 当前变量类型设置为 nvarchar(20)，但我也尝试解决通过将其输入 Date 来解决这个问题.

我有一个在 T-SQL 中运行时的存储过程，如下所示:

EXECUTE [Database1].[dbo].[SP1]@Date = '2020-10-01'

我正在使用 Power Query 将结果提取到 Excel 中.这是在 Power Query 中生成的 M 查询的样子:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date= '2020-10-01'"])

这会按预期运行并导入数据.

但是现在我正在尝试从 Excel 中的单元格中导出 @Date 值.我遵循了一个很好的教程，使 Power Query 可以使用 Excel 单元格中的日期，我相信我的设置是正确的.我已将参数命名为 SDate，并将其设置为文本类型，因为这是存储过程所期望的.

所以现在 M 查询看起来像这样:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date= SDate"])

运行此程序时，我在 Power Query 中收到此错误:

<块引用>

Microsoft SQL:从以下位置转换日期和/或时间时转换失败字符串.

我尝试过的另外两件事:

• 为 SDate 创建一个文字字符串，即用单引号括起来的日期，即 '2020-10-01' - 这给出了同样的错误.>

• 更改了存储过程，使@Date 变量的类型为Date.通过在 SSMS 中运行与上述完全相同的 T-SQL 语句进行测试，结果返回与 nvarchar 时相同.因此，在 Power Query 中，我确保 SDate 参数的类型为日期.然后我运行 M 查询，这次我收到错误错误将类型 nvarchar 转换为日期".

我发现有趣的一件事是，在创建上述 M 查询之后，如果我进入运行查询步骤的 Source 设置，SQL 语句框会显示:

EXECUTE [Database1].[dbo].[SP1]@Date = SDate

对我来说，这看起来像是将文字 SDate 作为日期传递给 SQL Server，而不是该参数的值.我可能错了.

对让它正确运行有什么想法吗?

解决方案

如果您将文字字符串放在正确的位置，它就可以工作.您需要退出引号内的文字文本并附加或插入您想要的字符串.

取而代之的是:

[Query=EXECUTE [Database1].[dbo].[SP1] @Date = SDate"]

试试这个:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = ;&日期]

其中 Sdate 已定义为文字字符串 '2020-10-01'.

如果 Sdate 是一个日期，那么使用这个:

[Query=EXECUTE [Database1].[dbo].[SP1]@Date = '";&Date.ToText(Sdate, "yyyy-MM-dd") &'"]

Latest version of Excel M365 and SQL Server 2017.

I have a stored procedure which takes a date as its input in format YYYY-MM-DD - currently the variable type is set to nvarchar(20), but I've also tried to resolve this issue by making it type Date.

I have a stored procedure that when run in T-SQL, looks like this:

EXECUTE [Database1].[dbo].[SP1]
@Date = '2020-10-01'

I'm using Power Query to pull the results of this into Excel. Here's what the generated M query looks like in Power Query:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = '2020-10-01'"])

This runs and imports the data as expected.

However now I'm trying to derive the @Date value from a cell in Excel. I have followed a good tutorial for making the date from the Excel cell available to Power Query and I believe I have that setup correctly. I have named the parameter SDate, and set it as type text since this is what the stored procedure is expecting.

So now the M query looks like this:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = SDate"])

When I run this, I get this error in Power Query:

Microsoft SQL: Conversion failed when converting date and/or time from character string.

Two other things I tried:

• create a literal string for SDate that's the date surrounded by single quotes i.e. '2020-10-01' - this gave the same error.

• changed the stored procedure so the @Date variable is of type Date. Tested that by running the exact same T-SQL statement as above in SSMS, and the results return just the same as when it was nvarchar. So then in Power Query I ensured that the SDate parameter is of type date. Then I run the M query and this time I get the error "error converting type nvarchar to date."

One thing I found interesting is that after creating the aforementioned M query, if I go into settings of Source for the step that runs the query, the SQL Statement box shows this:

EXECUTE [Database1].[dbo].[SP1]
@Date = SDate

To me this looks like it's passing a literal SDate as the date to SQL Server, instead of the value of that parameter. I could be wrong there.

Any thoughts on getting this to run correctly?

解决方案

The literal string works if you drop it in right. You need to exit the literal text inside the quotes and append or insert the string you want.

Instead of this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = SDate"]

Try this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = " & Sdate]

Where Sdate has been defined as the literal string '2020-10-01'.

If Sdate is a date, then use this:

[Query="EXECUTE [Database1].[dbo].[SP1]
@Date = '" & Date.ToText(Sdate, "yyyy-MM-dd") & "'"]

这篇关于运行 SQL Server 存储过程的 Power Query 数据类型转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！