June 28, 2011 at 10:34 am
I need to create a Stored Procedure that will take a query and export it to Excel. I also need the Excel file to have a timestamp making each file unique upon creation.
I have attempted to use the bcp utility, however you cannot export to Excel using this method.
I have attempted an OPENROWSET, however this method requires having a file existing.
I would sincerely appreciate any suggestions, links, or example on how to be able to export to Excel with DTS/SISS in an SPROC that would allow creating the Excel on the fly.
Thank you in advance.
June 28, 2011 at 10:35 am
You could use CLR integration to instantiate Excel, create a file, save it, and write into it.
- 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
June 28, 2011 at 10:42 am
Thank you for the extemely quick response and suggestion.
I just realized I have just posted this to a SQL Server 2005 (I normally use SSQL Server 2005) however for this particular case it will be running on 2000. I do believe CLR is an option on 2000.
I apologize for placing this request on the wrong board.
June 28, 2011 at 10:54 am
In SQL 2000, you'll have to use sp_OA_CreateObject, and use OLE automation into Excel, instead of CLR.
- 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
June 28, 2011 at 2:06 pm
One other option...from this forum post:
4 If you dont want to create an EXCEL file in advance and want to export data to it, use
EXEC sp_makewebtask@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)
I tested it on SQL 2005 and it worked fine. Sorry, but I don't have a 2000 instance to try it on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 2:25 pm
If you will continue with older versions of SQL Server, sp_makewebtask is fine. However, this is from MSDN:
sp_makewebtask is retained for backward compatibility. New Web pages are more easily created using Microsoft SQL Server 2005 Reporting Services (SSRS).
To execute sp_makewebtask you must be a member of the sysadmin fixed server role.
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
That note is from SQL 2005.
So far as I can tell, it's not in SQL 2008.
So, use it if you're positive that server will not be upgraded. Otherwise, avoid it.
- 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
June 28, 2011 at 2:27 pm
Very true, I knew and should have mentioned that the proc was dropped from the product in 2008.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 6:50 pm
Thank you very much, I will give that a shot and let you know how it turns out!:-)
June 29, 2011 at 7:39 am
Hi,
Thank you, the export is working.
Two more questions about the formatting.
1. Is there a way to have the column names appear as the first row? Right now it is showing "Testing Details" per the example, however if I set this to ' ', by default, it says "Query Results". I need my first row to be my header row.
2. IS there a way to format the data as it appears in the database? For example my ID field is showing as "1.03111E+15" instead of the expected 16 numbers. Also, instead of showing $25.00 it is showing as just "25".
Thanks again for your help!
June 29, 2011 at 8:58 am
rayh 98086 (6/29/2011)
Hi,Thank you, the export is working.
Two more questions about the formatting.
1. Is there a way to have the column names appear as the first row? Right now it is showing "Testing Details" per the example, however if I set this to ' ', by default, it says "Query Results". I need my first row to be my header row.
You can get rid of the "Query Results" like this:
DECLARE @resultstitle VARCHAR(100) ;
SET @resultstitle = CHAR(13) ;
EXEC sp_makewebtask
@outputfile = 'E:\testing.xls',
@query = 'Select * from master.sys.objects',
@colheaders = 1,
@FixedFont = 0,
@lastupdated = 0,
@resultstitle = @resultstitle ;
GO
2. IS there a way to format the data as it appears in the database? For example my ID field is showing as "1.03111E+15" instead of the expected 16 numbers. Also, instead of showing $25.00 it is showing as just "25".
Those are Excel display issues. Your data should be intact. If you click on the cell do you see the proper value? If you want to manipulate the display in Excel you can right-click and select "Format Cells...".
Thanks again for your help!
You're very welcome 🙂
-- enable sp_makewebtask
EXEC sys.sp_configure
@configname = 'Web Assistant Procedures',
@configvalue = 1
GO
RECONFIGURE
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply