stored procedure results to excel file.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » stored procedure results to excel file.

stored procedure results to excel file.

Rate Topic

Display Mode

Topic Options

Author

Message

sqlserver12345

Posted Sunday, October 25, 2009 11:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 139, Visits: 448

I have a stored procedure with a simple select statement. How do I export the select results to an excel file ? say abc_123_mmddyy.xls.
So every week the the last part of the file name should change .
Eg:abc_123_010109.xls
Next week it should be abc_123_010809.xls
then abc_123_011509.xls
how do I achieve this.
Thank you

Post #808382

TRACEY-320982

Posted Sunday, October 25, 2009 5:26 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 03, 2013 9:01 AM
Points: 722, Visits: 996

$nameoffile = 'myname' + getdate() this way the file be unique.

Post #808415

Steve Jones - SSC Editor

Posted Sunday, October 25, 2009 9:21 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

You can do this pretty easy in SSIS. Calculate the file name, change the destination dynamically based on the calculation.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #808459

sqlserver12345

Posted Monday, October 26, 2009 11:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 139, Visits: 448

Suppose this is my stored procedure

CREATE PROCEDURE [dbo].[usp_test]
AS
select * from emp
GO

How do I export the results to an excel file with the name of the excel file changing every week
Where do I plug in the code($nameoffile = 'my name' + getdate())which you mentioned.
Thanks

Post #808876

Vinay Thakur-585143

Posted Monday, October 26, 2009 1:49 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 4:05 PM
Points: 1,514, Visits: 720

http://vinay-thakur.spaces.live.com/blog/cns!645E3FC14D5130F2!591.entry

and if we want to export into xls we can use the below query but we have to give heading Name onexcelTargetfile.xls file before running this query:

INSERT INTO
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=e:\excelTargetfile.xls;Extended Properties=Excel 8.0;')...[Sheet1$]
SELECT name FROM master.dbo.sysdatabases
GO

we can use the same with OPENROWSET

http://www.mssqltips.com/tip.asp?tip=1202

HTH
Vinay

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

Post #808948

TRACEY-320982

Posted Monday, October 26, 2009 1:53 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 03, 2013 9:01 AM
Points: 722, Visits: 996

declare @filename nvarchar(100)
set @filename = 'c:\test.' + convert(varchar(10),getdate(),102) + '.xls'
print @filename

Then examples of using bcp or rowset
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Post #808953

Harold Buckner

Posted Wednesday, December 02, 2009 5:55 AM

Old Hand

Group: General Forum Members
Last Login: Friday, January 25, 2013 11:27 AM
Points: 307, Visits: 383

Check this out, it may help.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Post #827366

pasbanrule

Posted Friday, December 10, 2010 11:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 21, 2010 12:41 AM
Points: 1, Visits: 4

each time a description of the change, there is always plenty to talk about around the world. they are not exempt. a statement usually are: "i have to change almost immediately versions, so do not expect me to date continue to attack ..

-----------
smith

1Y0-A06
E20-322
E20-001
E20-340

Post #1033282

« Prev Topic | Next Topic »

Permissions