Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure results to excel file. Expand / Collapse
Author
Message
Posted Sunday, October 25, 2009 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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
Posted Sunday, October 25, 2009 5:26 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
$nameoffile = 'myname' + getdate() this way the file be unique.
Post #808415
Posted Sunday, October 25, 2009 9:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Posted Monday, October 26, 2009 11:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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
Posted Monday, October 26, 2009 1:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:44 PM
Points: 1,537, Visits: 739
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
Posted Monday, October 26, 2009 1:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
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
Posted Wednesday, December 2, 2009 5:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:37 AM
Points: 311, Visits: 397
Check this out, it may help.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Post #827366
Posted Friday, December 10, 2010 11:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse