Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Quefy syntax for calling SP with parameters.

Status
Not open for further replies.

RRinTetons

IS-IT--Management
Jul 4, 2001
333
US
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
 
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:

Code:
{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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top