October 25, 2009 at 11:22 am
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
October 25, 2009 at 5:26 pm
$nameoffile = 'myname' + getdate() this way the file be unique.
October 25, 2009 at 9:21 pm
You can do this pretty easy in SSIS. Calculate the file name, change the destination dynamically based on the calculation.
October 26, 2009 at 11:37 am
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
October 26, 2009 at 1:49 pm
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
October 26, 2009 at 1:53 pm
declare @filename nvarchar(100)
set @filename = 'c:\test.' + convert(varchar(10),getdate(),102) + '.xls'
print @filename
Then examples of using bcp or rowset
December 2, 2009 at 5:55 am
Check this out, it may help.
December 10, 2010 at 11:09 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply