0

I have a SQL query via a MS Query connection into Excel that includes a temporary table.

Code:

CREATE TABLE #PV (ProductID varchar(255), Price int);

INSERT INTO #PV(ProductID, Price)
   SELECT DISTINCT 
      PLR.ProductID, MAX(PLR.normal_price)
   FROM 
      dbo.t_price_lists_r AS PLR 
   WHERE 
      PLR.price_list = 1115
   GROUP BY 
      PLR.ProductID

SELECT P.ProductID, P.Price 
FROM #PV AS P

BEGIN DROP TABLE #PV END

The code works fine in SSMS and returns the correct results, but I get three distinct errors when running it in Excel/MS Query, as follows:

  1. Invalid column name 'ProductId'

  2. Deferred prepare could not be completed

  3. Statement(s) could not be prepared

I run SQL queries via Excel on a daily basis without incident, usually.

Many thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
J-BAC
  • 13
  • 6
  • Okay I can't replicate your example easily on my machine, but first I would try adding brackets to all your identifiers(tables,columns,etc...) just in case. Then if that doesn't work, break it down. First run the select statement with one column. Then add the where clause, then group clause and other column. Then the insert, then selecting from temp. Break it down and keeping adding things until it breaks and that should help pinpoint your problem – Stephan Mar 12 '15 at 16:33
  • Thanks Stephan, but I have tried that and the issue persists. – J-BAC Mar 12 '15 at 16:52
  • Have you tried getting rid of "DISTINCT"? – Stephan Mar 12 '15 at 18:58

2 Answers2

0

Whenever troubleshooting, it normally helps to start with what works and keep adding stuff until it breaks. So try running these and it'll show you what's not working.

1

CREATE TABLE #PV (ProductID varchar(255), Price int);
GO
SELECT P.ProductID, P.Price 
FROM #PV AS P;
GO
BEGIN DROP TABLE #PV END

2

CREATE TABLE #PV (ProductID varchar(255), Price int);
GO
INSERT INTO #PV(ProductID, Price)
    SELECT 'Test',1;
GO
SELECT P.ProductID, P.Price 
FROM #PV AS P;
GO
BEGIN DROP TABLE #PV END

3

CREATE TABLE #PV (ProductID varchar(255), Price int);
GO
INSERT INTO #PV(ProductID, Price)
  SELECT DISTINCT PLR.ProductID, MAX(PLR.normal_price)
FROM 
      dbo.t_price_lists_r AS PLR 
WHERE 
      PLR.price_list = 1115
   GROUP BY 
      PLR.ProductID
GO
SELECT P.ProductID, P.Price 
FROM #PV AS P;
GO
BEGIN DROP TABLE #PV END
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • Thanks for the feedback guys, perhaps I didn't word it particularly well, but I know that the SQL is sound. My question is why does it work in SSMS but not work in MS Query? If there is a reason, how do I get round it? Thanks! – J-BAC Mar 13 '15 at 14:52
0

I found that by adding 'SET NOCOUNT ON' at the start of the SQL, Excel's MS Query can accommodate temporary tables.

Thanks to all that commented.

J-BAC
  • 13
  • 6