0

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.

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."

OR

"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."

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.

Here is how my code is set up

        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

Then I select from the temporary table #tmpLastWeek

    select * from #tmpLastWeek

Then finally I drop this temporary table so that it can be ran again

    drop table #tmpLastWeek

Any advice or suggestions on getting this query to be ran thru and SQL connection in Excel would be much appreciated! Thanks!!!!

Omar123456789
  • 59
  • 1
  • 2
  • 9
  • Could you not look at wrapping it all up into a stored proc and calling the stored proc instead? Also, reference your temp table just as #tmpLastWeek. – Keith Oct 17 '17 at 15:39
  • I'm not one of the DBAs, so I cannot do that without their permission. That is a last resort option – Omar123456789 Oct 17 '17 at 15:41
  • 1
    Why not do away with your temporary table altogether and just run your query from excel. As I'm not sure what advantages the temporary table is giving you. – Steve Ford Oct 17 '17 at 15:45
  • @SteveFord makes a good point, what is the temp table used for? – Keith Oct 17 '17 at 15:52
  • This is just a small piece of the total query. The total query is much larger and the alternative to creating temporary tables is utilizing nested/subqueries. But that option really slows down the process to about 4 minutes which is entirely too long. Using the temporary tables reduces my run time to less than 1 minute – Omar123456789 Oct 17 '17 at 15:52
  • I also removed the database prefix before creating the temp table so now the line reads "INTO #tmpLastWeek" but that also did not solve the problem – Omar123456789 Oct 17 '17 at 15:53
  • Can you update your question with the exact errors you receive and how you are making the connection from Excel to SQL Server? – Keith Oct 17 '17 at 15:57
  • Done. Updated with the latest error after removing the database prefix before creating the temp table – Omar123456789 Oct 17 '17 at 16:07
  • Can you not use a table variable with Insert ... Select? – Steve Ford Oct 17 '17 at 16:10
  • A table variable is a new concept for me. I am looking into that now? Do you have any suggestions for how to implement this in my query above? – Omar123456789 Oct 17 '17 at 16:15
  • I tried to use a table variable in the following way: Declare @tmpLastWeek TABLE( [PRODUCT_ID] [char](18) NOT NULL, [SETID] [char](5) NOT NULL, [LW] [INT] NOT NULL ); Insert into tmpLastWeek select ... select * from tmpLastWeek – Omar123456789 Oct 17 '17 at 17:01
  • I tried to use a table variable in the following way: Declare @tmpLastWeek TABLE( [PRODUCT_ID] [char](18) NOT NULL, [SETID] [char](5) NOT NULL, [LW] [INT] NOT NULL ); Then inserting into it and selecting * from it, but I got the same error in excel. It again worked SQL server, but excel can't handle it for some reason – Omar123456789 Oct 17 '17 at 17:07

0 Answers0