export stored procedure output to excel using access 2003

[masoume1]

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?

[NeoPa]

From an Access database you can link a table to your SQL Server Stored Procedure. From there just export it.

[masoume1]

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>u sername</var>\Local Settings\Tempor ary Internet Files\Content.M SO\<var>filenam e</var>.log.

And here is output of my procedure:

CREATE TABLE #Temp2

(

Goodsint,

Scopeint,

Univalentmoney,

[Avg]money

)



EXECUTE ('  INSERT INTO #Temp2  '+@SQLStr)  



INSERT INTO #Temp2 SELECT DISTINCT #Temp2.Goods , ScopeRows.Scope, 0 AS [Univalent] , 0 AS [Avg]

FROM  dbo.ScopeRows( @BDate, @EDate, @Scope) ScopeRows CROSS JOIN #Temp2



CREATE TABLE #Temp3

(

Goodsint,

[Avg]money

)



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



SELECT MyQ.Goods , TVAllObjects.Name AS GoodsName , MyQ.Scope, MyQ.Univalent, MyQ.[Avg], TVDepotGoods.Inventory

FROM (SELECT #Temp2.Goods, #Temp2.Scope, SUM(#Temp2.Univalent) AS Univalent, #Temp3.[Avg] FROM #Temp2 

INNER JOIN  #Temp3 ON #Temp2.Goods = #Temp3.Goods

                       GROUP BY #Temp2.Goods, #Temp2.Scope, #Temp3.[Avg]) MyQ 

INNER JOIN TVAllObjects ON MyQ.Goods = TVAllObjects.Code 

INNER JOIN TVDepotGoods ON MyQ.Goods = TVDepotGoods.Goods

WHERE     (TVDepotGoods.Depot = @Depot)

thank!

[Rabbit]

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?

[masoume1]

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?

[Rabbit]

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.