1

I have been following the tutorial HERE but anytime I try to refresh the workbook, it gives me an error of:

DataSource.Error: Microsoft SQL: This database function doesn't support the query option 'Query' with value 'null'.

This is the SQL behind the query, and nothing is returning null, and the stored proc is set-up to accept 3 input parameters.

CSR=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"CSR"],
d1=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d1"],
d2=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d2"],
Source = Sql.Database("ServerInstanceName", "Credentials", 
[Query="exec [dbo].[StoredProcToRun] '" & Number.ToText(d1)
& "''," & Number.ToText(d2) & "','" & CSR & "'"])

And this is the set-up of my stored procedure showing it accepts 3 params:

Alter PROCEDURE dbo.StoredProcToRun 
@d1 varchar(50), @d2 varchar(50), @CSR varchar(500)

AS
BEGIN

What do I need to modify in order for this power query to pass the Excel Parameters to my SQL stored procedure and return the data back to the Excel workbook?

I altered my power query sql to the below and now I get an error of

Token comma expected

[Query="exec [dbo].[StoredProcToRun] 
& Number.ToText(d1) & "','"
& Number.ToText(d2) & "','" 
& CSR & "'"])
Improve this question
4
  • one or more Parameter has a null value. Shouldn't be the part "& CSR" not be like the other two Parameters "& Number.ToText(CSR)".
    – visu-l
    May 25, 2016 at 9:45
  • @visu-l - the CSR value is a text value not a number value.
    – user2676140
    May 25, 2016 at 11:41
  • since you build a string "exec...." the conversion of this numeric value to a string should be necessary in order to concatenate your substrings. On SQL Server you get an error as well if you try "select 'exec [dbo].[StoredProcToRun] ' + 3" instead of "select 'exec [dbo].[StoredProcToRun] ' + CONVERT(varchar, 3 )"
    – visu-l
    May 25, 2016 at 12:03
  • @visu-l - how would I alter my power query string to convert(varchar,csr)?
    – user2676140
    May 25, 2016 at 12:47

1 Answer 1

Reset to default
0

make sure to have a string when concatenating your substrings. Values, which are numeric (e.g. CSR) have to be converted to a string for the concatenation.

CSR=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"CSR"],
d1=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d1"],
d2=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d2"],
Source = Sql.Database("ServerInstanceName", "Credentials", 
    [Query="exec [dbo].[StoredProcToRun] '" &
    Number.ToText(d1)  & "','" & 
    Number.ToText(d2)  & "','" & 
    Number.ToText(CSR) & "'"])

see also here an another example as the tutorial you found.

Just tried with a part of the script to generate the string for the EXEC Statement. Eventually try to generate the string and copy it on Management Studio to check if it works there.

enter image description here

Improve this answer
8
  • If I add Number.ToText(CSR) I then get an error of unable to convert value "Name Name" to type number.
    – user2676140
    May 25, 2016 at 13:09
  • which data type recognizes Power Query when loading just the CSR value? This could be the issue. Try eventually Text.From(CSR) instead of Number.ToText.
    – visu-l
    May 25, 2016 at 13:17
  • Altering to text.from(CSR) gives me an error of DataSource.Error: Microsoft SQL: Procedure or function 'StoredProcToRun' expects parameter '@CSR', which was not supplied. However, if you look at syntax, it is supplied?!?!?!
    – user2676140
    May 25, 2016 at 13:28
  • Try on the parameter table to define each column in the table as text before importing in Power Query. As your tutorial notice: "...To make out life easier I changed the format cell of the “Check Date” column to Text, other wise we’ll need to convert it in Power Query.... "
    – visu-l
    May 25, 2016 at 13:45
  • I already have them formatted as text, but still get the error of the parameter is not supplied.
    – user2676140
    May 25, 2016 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.