Home > Paul Mather, Work > #ProjectServer #Excel report with SQL Temporary Tables #PS2010 #PS2013 #SQL

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

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

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
Categories: Paul Mather, Work Tags:
  1. July 20, 2013 at 00:23

    This is the definition of SET NOCOUNT ON: http://msdn.microsoft.com/en-us/library/aa259204(v=sql.80).aspx

    I suspect this will be useful when I forget why this works!

  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: