By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

export stored procedure output to excel using access 2003

P: 3
I need to export data, from within a Sql SERVER stored procedure to excel. Right now we use access 2003.The procedure has selected from 2 temporary table which joined with other views in our database.This cause problems while i'm trying export data to excel.I had defined another temporary table and insert whole result into it and then select just from that.This solve my problem but my boss refuse to do this(for any reason that i dont know)so I have to find another way.

any ideas?
Feb 11 '13 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,769
From an Access database you can link a table to your SQL Server Stored Procedure. From there just export it.
Feb 11 '13 #2

P: 3
thank NeoPa for your guide
but actually we use SQL SERVER 2008 R2 and I have to change just the form in Access(not the procedure in DATABASE).
Here I put the message that I recieved :


Problems During Load

Problems came up in the following areas during load:

pivotTable



A log file has been created listing the load warnings. This file has been saved to the following location: C:\Documents and Settings\<var>username</var>\Local Settings\Temporary Internet Files\Content.MSO\<var>filename</var>.log.

And here is output of my procedure:



Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #Temp2
  2.  
  3. (
  4.  
  5. Goodsint,
  6.  
  7. Scopeint,
  8.  
  9. Univalentmoney,
  10.  
  11. [Avg]money
  12.  
  13. )
  14.  
  15.  
  16.  
  17. EXECUTE ('  INSERT INTO #Temp2  '+@SQLStr)  
  18.  
  19.  
  20.  
  21. INSERT INTO #Temp2 SELECT DISTINCT #Temp2.Goods , ScopeRows.Scope, 0 AS [Univalent] , 0 AS [Avg]
  22.  
  23. FROM  dbo.ScopeRows( @BDate, @EDate, @Scope) ScopeRows CROSS JOIN #Temp2
  24.  
  25.  
  26.  
  27. CREATE TABLE #Temp3
  28.  
  29. (
  30.  
  31. Goodsint,
  32.  
  33. [Avg]money
  34.  
  35. )
  36.  
  37.  
  38.  
  39. INSERT INTO #Temp3 SELECT Goods, Univalent / MyScope AS [AVG] FROM (SELECT Goods, SUM(Univalent) AS Univalent, MAX(Scope) AS MyScope FROM #Temp2 GROUP BY Goods) MyQ
  40.  
  41.  
  42.  
  43. SELECT MyQ.Goods , TVAllObjects.Name AS GoodsName , MyQ.Scope, MyQ.Univalent, MyQ.[Avg], TVDepotGoods.Inventory
  44.  
  45. FROM (SELECT #Temp2.Goods, #Temp2.Scope, SUM(#Temp2.Univalent) AS Univalent, #Temp3.[Avg] FROM #Temp2 
  46.  
  47. INNER JOIN  #Temp3 ON #Temp2.Goods = #Temp3.Goods
  48.  
  49.                        GROUP BY #Temp2.Goods, #Temp2.Scope, #Temp3.[Avg]) MyQ 
  50.  
  51. INNER JOIN TVAllObjects ON MyQ.Goods = TVAllObjects.Code 
  52.  
  53. INNER JOIN TVDepotGoods ON MyQ.Goods = TVDepotGoods.Goods
  54.  
  55. WHERE     (TVDepotGoods.Depot = @Depot)


thank!
Feb 12 '13 #3

Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code.

Masoume, he wasn't telling you to change your stored procedure. He was telling you to change Access. Please reread his post.

P.S.
That is some convoluted code you have there. I can barely make out what you're trying to accomplish but you do realize you can boil most of it down to one SQL statement right?
Feb 12 '13 #4

P: 3
sorry I'm new at work...
Actually we use Access as front end and we have not any table or data in access.from access just connect to SQL SERVER and use views and tables and stored procedure...
Now I have a pivottable that use stored procedure.and i put the output of my procedure (not the total code).you see there was 2 temporary table that joined with other views.this make the trouble and whenever i define another temp table which insert result into that and at last select just from that,i dont have any problem.
i cant understand what do you mean Rabbit,would you please explain more?
Feb 14 '13 #5

Rabbit
Expert Mod 10K+
P: 12,430
It doesn't matter if none of your data is in Access. You can still make the changes that NeoPa suggested in Access to resolve your problem.

What I was saying was that your code is confusing and inefficient. The code can be written a lot better.
Feb 14 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.