#ProjectServer #Excel report with SQL Temporary Tables #PS2010 #PS2013 #SQL

July 19, 2013 at 11:45 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , , , , ,

I came across an issue a while back and meant to blog about it but forgot until a colleague of mine today mentioned the same issue. This jogged my memory of the fix so I thought it was a good time to write the post. The issue isn’t Project Server related but the reports and queries we were creating were for Project Server.

If your SQL query in an Excel file uses temporary tables Excel will throw an error like the one seen below:

image

For the search engines the error is below:

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

Check the database server or contact your database administrator. Make sure the external database is available and hasn’t been moved or reorganized, then try the operation again.

The error will occur if you have the select statement in the connection file definition command text or even calling a SQL stored procedure from the command text. The same fix applied to both, at the start of the select statement add “SET NOCOUNT ON” as shown below:

image

Now Excel will execute the query and return the data as expected. Smile

Advertisements

1 Comment

  1. […] This article has been cross posted from pwmather.wordpress.com (original article) […]


Sorry, the comment form is closed at this time.

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: