MS Query SQL Syntax

[RRinTetons]

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

 

[Andrzejek]

Don't you think you should be asking this question in forum183


---- Andy

There is a great need for a sarcasm font.

 

[RRinTetons]

I don’t know, I figured that more people working with Excel would be likely to have used MS Query than would folks primarily working with SQL query syntax. As I indicated, the *SQL* part is fine, it’s calling it in MS Query to get the days into Excel that causes problems.

Where would *you* post such a question?

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[SkipVought]

The SQL syntax for a parameter in MS Query is...

WHERE...SomeField=?

...and then you have three options for assigning the parameter value at run time...
[tt]
1) manually
2) a cell reference
3) a constant
[/tt]

That's just related to a parameter query, not necessarily a stored procedure.

In my practice, I never had occasion to make stored procedures, since the query seems to default to a state that Excel always stores the SQL. The feature that seemed the most conducive to my needs was to pre-assign cells for parameters, and have the user fill those cells. Excel also has a feature for parameter queries that automatically runs the query when one of the parameter cells is filled--great for interactive applications.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If you have excel 365 or 2016+ it is much easier to manage data with power query. Ken Puls (Excelguru) explains it here.

combo

 

[RRinTetons]

Re PowerQuery —- Agree, 100%. Unfortunately, this client has an aversion to Microsoft PowerBI tools, so,I’m stuck with MS Query...

I’ve made it work in the past, and recall the syntax being a bit arcane, so I’ll persevere.

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[combo]

In recent excels power query is built in excel, as Get&Transform part of Data tab.

combo