title: Creating SQL Connection in Excel that creates and drops temporary tables
body: <p>I've created query that runs perfectly in Microsoft SQL Server and it utilizes temporary tables it creates and drops. I now want to create a data connection in Microsoft Excel that will run my query and display the results in Excel, so that business partners who do not know SQL can utilize the report. </p>
  
  <p>However, when I try to run the query in a data connection in excel I get errors like the following:
  "Database name 'database' ignored, referencing object in tempdb."</p>
  
  <p>OR</p>
  
  <p>"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."</p>
  
  <p>I then tried to use the phrase "SET NOCOUNT on" before creating my temporary tables but it did not resolve the issue and I still have no report. </p>
  
  <p>Here is how my code is set up</p>
  
  <pre><code>        select
          x.PRODUCT_ID,
          y.SETID,
          LW = ISNULL (SUM(y.SALES_QTY) ,0)  
          INTO database.#tmpLastWeek
          --#tempLastWeek is a temporary table I am creating here and inserting values into
          from 
          database.sales_table y
          inner join
          database.product_table x
          on y.PRODUCT_ID = x.PRODUCT_ID
          and x.SETID = y.SETID
          where 
          y.stores IN (
          'storeslist'
          )
          and y.sales_week = @this_week - 1
          and y.sales_year = @this_year
          group by 
          x.PRODUCT_ID,
          y.SETID,
          x.DESCR
  </code></pre>
  
  <p>Then I select from the temporary table #tmpLastWeek</p>
  
  <pre><code>    select * from #tmpLastWeek
  </code></pre>
  
  <p>Then finally I drop this temporary table so that it can be ran again</p>
  
  <pre><code>    drop table #tmpLastWeek
  </code></pre>
  
  <p>Any advice or suggestions on getting this query to be ran thru and SQL connection in Excel would be much appreciated! Thanks!!!!</p>