Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-09-30 : 17:26:03
|
| This is not exactly a question, but a solution in its vague shape I have been thinking for awhile. I want to hear some of your critics when I start to implement it. This is kind of a side project without a firm deadline, because I have been running the select statement in QA and copy paste the result in excel and email it everyday. It is just one of the things you do every day that you think there got to be a better way to meet the need.The requirement in a nutshell is simple: get sql data into excel. It will be run on a daily basis. Preferably, every day with a new file name, in the format of myReportMM_DD_YYYY.xlsI have researched MSDN, www.sqldts.com, and of cause all the goodies from this site. My first option is DTS. There are two limitations. The xls file has to be pre-existing, and I cannot easily send in header names. To overcome, I probably need to use File System Object to manipulate the file. FSO is from Microsoft scripting runtime library. It is available for VBA. Does ActiveX script in SQL Server 2000 have a way to reference it?Second option:Borrowing from Madhivanan’s post with slight modification, I got this.insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= D:\testing.xls;', 'SELECT * FROM [SheetName$A2:M10]') select * from SQLServerTableThe modification is to dynamically specify a range for preserving the header row.10 should be replace with record.count + 1 to leave the first row for header.My question is where should I run this code? In an activeX script step in the DTS? If this will work, then I don’t need data transformation task. All in all, these requirements are not fancy at all, but to implement it right is another story. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-30 : 18:23:11
|
| Just use the DTS export wizard to create a DTS package to do this. It will create the file, create the headers, and everything elee you require. If you need to give the file a new name ever day, just add a script task to rename it with the FSO.CODO ERGO SUM |
 |
|
|
|
|
|
|
|
Export to Excel