Export a Stored procedure result to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Export a Stored procedure result to Excel

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-22 : 07:36:40

Senthil writes "Hai I have a Stored procedure which gives me the result of rows depending upon the user's input, I want to Export the Stored Prcedure result to Excel Sheet. Plz give me the Technique how i have to proceed."

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-22 : 11:35:12

If you want it in an excel worksheet then dts.
If you just want something that will open in excel when double clicked then bcp to a tab delimitted file with .csv extension.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NoNulls
Starting Member

14 Posts

Posted - 2005-02-22 : 12:03:21

Or if you exec the sp in query analyzer simply select the columns and copy paste right into excel

senpro15
Starting Member

3 Posts

Posted - 2005-02-22 : 23:27:40

Is tht possible to Use or call an Stored procedure with its parameters in DTS and run and to transfer the result rows to excel.
plz help me.......

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-23 : 08:05:08

It certainly is. Simply paste the stored procedure call into the query window of the Source tab:

EXEC myStoredProcedure @p1='whatever', @p2=1

Once that's done, you set up the destination as an Excel file, check the transformations, and run it.

senpro15
Starting Member

3 Posts

Posted - 2005-02-25 : 04:51:06

Thanks for the reply from robvolk but after doing like above (executing the SP in query window ) it give me an error 'Invalid Pointer' and in the preview also nothing is coming and in the destination i have only my column names not the values - plz help me.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-25 : 07:46:26

You'd have to post the code of the procedure. I've never heard of an "invalid pointer" error before.

senpro15
Starting Member

3 Posts

Posted - 2005-02-28 : 01:30:18

ALTER procedure Koki_cuswise_salesavr
@company_no glcompany,
@locn_no GLLOCN,
@lang_id cmn_langid,
@from_date datetime,
@todate datetime

as
/*
example: Exec Koki_cuswise_salesavr 'Hitkokil','bnglbr',1,'08/01/2004','08/31/2004'
*/
set nocount on

Begin

Declare @PCust_no cmn_cust_ven_code, @PInv_no cmn_tranno,@PItem_qty cmn_pqty,
@PItem_desc char(100), @Plocation_desc rcg_locdesc,
@Pitem_amt cmn_pamt,@PInv_basic cmn_pamt, @PInvdate Datetime,
@Pfetch_flag int, @pbasic_amt cmn_pamt, @Pfetch_flag2 int,
@Ptemp1per numeric(14,4),@PItemrateper numeric(14,4),
@Pori_mnth_val int ,@PDisc_amt cmn_pamt,
@PItem_qty_amt NUMERIC(14,4), @PTot_Qty cmn_pqty,
@PAvg_itemval NUMERIC(14,4),@Ptot_amt cmn_pamt

select @Pori_mnth_val = datediff(mm,@from_date,@todate ) + 1

Drop Table Temp1

CREATE TABLE temp1 (
company_no GLCOMPANY,cus_name som_name,
Cus_code cmn_cust_ven_code,location_desc rcg_locdesc,
Tot_amt cmn_pamt, Item_qty cmn_pqty,mnthdiff int )

insert temp1(Cus_code,company_no,location_desc,
cus_name,mnthdiff )
select distinct c.bill_to_cust,c.company_no,
d.location_desc,f.cust_name,@Pori_mnth_val as mnth
from shp..dms_t_invoice_item b ,
shp..dms_t_invoice_hdr c ,
common..sec_location_Details d ,
som..m_customer f
where c.company_no = @company_no
and c.order_locn = @locn_no
and b.company_no = @company_no
and b.invoice_locn = @locn_no
and d.company_code = @company_no
and d.location_code = @locn_no
and f.locn_no = @locn_no
and b.invoice_no = c.invoice_no
and c.bill_to_cust = f.cust_no
--and c.invoice_no = '575'
and c.invoice_dt between @from_date and @todate
and c.status = 'A'

--select * from #temp
select @Ptot_amt = 0,@PTot_Qty = 0

DECLARE CUSCUR CURSOR FOR
select Cus_Code
from temp1
order by Cus_Code

OPEN CUSCUR

SELECT @pfetch_flag2 = 1

WHILE (@pfetch_flag2 = 1)
BEGIN

FETCH CUSCUR INTO @Pcust_no

if (@@FETCH_STATUS < 0)
BEGIN
select @pfetch_flag2 = 0
BREAK
END

DECLARE INVCUR CURSOR FOR
select invoice_no
from dms_t_invoice_hdr
where invoice_dt between @from_date and @todate
and invoice_locn = @locn_no
and bill_to_cust = @PCust_no
and status = 'A'
order by invoice_no

OPEN INVCUR

SELECT @Pfetch_flag =1

WHILE (@PFetch_flag = 1)
BEGIN

FETCH INVCUR
INTO @Pinv_no

print @PCust_no
print @Pinv_no

IF (@@FETCH_STATUS != 0)
BEGIN
SELECT @Pfetch_flag = 0
BREAK
END

if exists (select tcd_amt
from dms_t_dms_doc_tcd
where invoice_locn = @locn_no
and tcd_type = 'D'
and invoice_no = @Pinv_no)
begin
select @PDisc_amt = sum(tcd_amt)
from dms_t_dms_doc_tcd
where invoice_locn = @locn_no
and tcd_type = 'D'
and invoice_no = @Pinv_no
end
Else
begin
select @PDisc_amt = 0

end

select @pbasic_amt = SUM(item_value),
@PItem_qty = SUM(invoice_qty)
from dms_t_invoice_item
where invoice_locn = @locn_no
and invoice_no = @Pinv_no

select @PTot_Qty = @PTot_Qty + @PItem_qty

select @Ptot_amt = @Ptot_amt + (@pbasic_amt - @PDisc_amt)

select @PItem_qty = 0 , @pbasic_amt = 0, @PDisc_amt = 0

END

CLOSE INVCUR
DEALLOCATE INVCUR

UPDATE TEMP1
SET Tot_amt = @Ptot_amt,
Item_qty = @PTot_Qty
where Cus_code = @Pcust_no

select @Ptot_amt = 0,@PTot_Qty = 0

END
CLOSE CUSCUR
DEALLOCATE CUSCUR

select *
from temp1
order by tot_amt desc

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Mr. Robvolk......
This is the procedure I am using (sorry for bad performance tuning) and previously i have used temp1 table as Temporary table i thnk DTS is not working with Temporary table so i created tht as physical table with drop statement and the result of final select qry is i needed to be exported to excel using DTS I got the error 'Invalid POinter' on running this query in DTS i get only the column headings in the output excel file no result is comong on the preview mode also.
Plz help me.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources