MS Quefy syntax for calling SP with parameters.

[RRinTetons]

I tried this over the Office list figuring that MS Query was more an Eccel thing, but was directed over here, so...

RRinTetons (IS/IT--Management)(OP)20 Dec 19 01:59

This query works fine in SSMS:

EXECUTE [dbo].[myProc] @BeginTransactionDate = '2019-12-01 22:52:00',@EndTransactionDate = '2019-12-5 22:52:00'

When I change it to work as a stored procedure call in MS Query to move data into an Excel workbook I *thought it should look like:

{CALL [dbo].[myProc] @BeginTransactionDate = '2019-12-01', @EndTransactionDate = '2019-12-05'}

but that returns a syntax error without much information. I tried the MS Query version with the parameters in parentheses, but that doesn't fix anything.

I used to do this a lot, but it's been a few years, my recollections of the very picky syntax MS Query expects is a little fuzzy. Does anybody see what I'm missing.
-

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[RRinTetons]

To answer my own question:

MS Query as present in Excel 2010 and 2013 doesn't recognize named parameters. In my case, the client's proc accepts up to 12 arguments, only two of which are of interest in this application. As I couldn't use named parameters to pick out the ones I needed in Excel, I wrapped the client's SQL sp in an sp of my own that only expects two arguments and then called the client's procedure from that therefore I was able to succeed with the syntax:

{CALL [dbo].[myWrapperProc] ('2019-12-01', '2019-12-05')}

Note that also seems to require that the parameters be in parens, although none of the documentation I can find online suggests that.

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY