February 26, 2009 at 9:48 am
Hi,
I want to write a stored procedure in SQL Server that gets two parameters and
makes a query based on the parameters, then exports the result to an Excel file.
Is this task possible in SQL Server 2000 ? How ?
Thanks in advance...
Nelson Pombinho
Portugal
February 26, 2009 at 9:56 am
You'll be better off building this as a DTS package, then executing that from the proc (or a scheduled job).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 10:04 am
I agree with Gsquared's suggestion but if you do not desire to use DTS Read about OPENROWSET in Books On Line
The following example can not be used to write to Excel 2007. If you need help doing that please repost.
/*This is an example of how to output to Excel.
Criteria: Spread sheet must exist on the SERVER,
in row 1 acting as column headers must be the name
of the table field being exported */
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\MSSQL\Test.xls',
'SELECT * FROM [Sheet1$]') SELECT * FROM Keywords
-------------------------------------------------------
SELECT * FROM Keywords - would be your parameterized query.
February 26, 2009 at 10:11 am
Hi again,
one of my questions is: can i use openrowset inside the stored procedure code?
Nelson Pombinho
Portugal
February 26, 2009 at 10:30 am
--As an example:
--first, i create the stored procedure...
CREATE PROCEDURE [dbo.].[sp_DAILYSALES]
@Cod_Forn int,
@cod_Familia int
AS
BEGIN
insert into openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls;',
'SELECT cod_artigo,qtd,tpvp FROM [Sheet1$]')
select distinct cod_artigo,
sum(qtd) as qtd,
sum(tpvp) as tpvp
from Daily_Sales
where (Cod_Forn=@Cod_Forn)
and (Cod_Familia=@Cod_Familia)
group by cod_artigo
END
GO
--after this i run this:
exec sp_DAILYSALES 459, 40105
--but it takes so much time running that after 30 minutes i stopped the query
February 26, 2009 at 12:55 pm
If you run just the SELECT statment
select distinct cod_artigo,
sum(qtd) as qtd,
sum(tpvp) as tpvp
from Daily_Sales
where (Cod_Forn=@Cod_Forn)
and (Cod_Familia=@Cod_Familia)
group by cod_artigo
How long does this take?
Can you post your table structure, indexes, number of rows in the table, some sample data as per the article in my signature block.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply