Data Connection Error with Functioning SQL

[Renee Beacham]

Hello,

I receive the following error when attempting to create a pivot table via a SQL connection:

"The query did not run or the database could not be opened."

However the query I'm attempting to run functions in SSMS and returns rows.

Has anyone experienced this or has any idea as to why a query that functions in SSMS would not function in an Excel workbook connected to the same database? I've not had this issue before when creating pivot tables from this database in Excel.

-Renee

 

[Chihiro]

My guess is one of following.

1. Check your ODBC connection setting & registry key
2. Is query/procedure set up with Temporary Tables or Table Variables? If former change it to latter.
3. Add "Set nocount on" at beginning of procedure (database side)
4. Credential used does not have appropriate access to table/query

You may find below link helpful.
https://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/