0

I've done this numerous times in the past when connecting Access queries to Excel, but now when I try and do it with a SQL Server query, I get an error message of

[Microsoft][ODBC SQL Sever Driver]Invalid parameter number

before I can even assign the parameter a value.

Once I got this error message I started to look around online for some answers, and I came across this article here. I followed these steps, which is essentially what I was doing anyway, and I still get the same error. The article is a few years old, so I'm not sure if things have changed since the release of Excel 2016 (my current version).

Is this something that cannot be done anymore without using VBA?

Just to summarize my steps, this is what I have done:

  • From Other Sources > From Microsoft Query
  • Select Database from MS Query Prompt > Click SQL button
  • Paste SQL > Click Return Data Button
  • Click Definition in Connection Properties window> Swap criteria code with ? in Command Text
  • Click OK
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jcmoney1010
  • 912
  • 7
  • 18
  • 41
  • Here are just some of the many solutions provided on the web: http://superuser.com/questions/197453/run-an-sql-query-with-a-parameter-from-excel-2007 http://superuser.com/questions/632202/query-parameters-in-an-external-connection-to-sql-server https://support.office.com/en-us/article/Customize-a-parameter-query-addf7cb7-ddf0-442f-a60c-cd7280e201bd http://stackoverflow.com/questions/5434768/how-to-pass-parameters-to-query-in-sql-excel – Ralph Feb 10 '16 at 21:18
  • @Ralph While I appreciate your response, none of those links are very helpful. For the most part they all either depict the same or similar solution to the article I posted, or they depict a way to make this work with Access, which I already stated works just fine. I read almost everyone of those before posting. – Jcmoney1010 Feb 10 '16 at 21:27
  • How about this one: http://superuser.com/questions/197453/run-an-sql-query-with-a-parameter-from-excel-2007 I just tried it myself on my computer and it works fine. Note, that this one is using `[]` instead of `?` to generate the initial query. If this doesn't work, please also add a tag for your Excel Version as `Other Sources` starts to behave differently in Excel 2016. – Ralph Feb 10 '16 at 21:44

2 Answers2

3

It looks as though I may have found the issue. When doing the initial connection through MS Query, you are asked to select the data source. Apparently there are two SQL Server options, one named SQL Server and another named SQL Server Native CLient 11.0. Originally I chose SQL Server, after trying numerous ways to fix this issue, I deleted the data source completely from MS Query, and started over, this time I chose SQL Server Native Client 11.0 and it worked exactly as it should. I'm not entirely sure what the difference between the two are, but it seems to have made the difference.

Jcmoney1010
  • 912
  • 7
  • 18
  • 41
2

I had this issue as well and it turns out that because I had included square braces around the procedure I wanted to execute it seemed to think it was a parameter. e.g. exec dbo.[myproc] @foo=? just changing to... exec dbo.myproc @foo=? worked fine. I think behind the scenes the params are being put in an array and square brackets in the sql is confusing it. Hope this helps. It may well be that sql native client 11 doesn't handle the params in the same way so you bypass this problem.

Andy Robertson
  • 171
  • 2
  • 6
  • This is the real solution. We can keep using the default "SQL Server" driver which is quite straightforward. – Nelson Jul 05 '21 at 10:11