Wednesday, February 15, 2012

Excel Output

I want to run a stored procedure which will run a specific query and put out
a monthly file each month. The name of the file with be "jan.xls" for Jan
data and then the next month it will be "feb.xls" for Feb. In addition, I
want to place the output in a folder which cooresponds month it is.
If I run a query, is there a way for me to put out an xls file? I see that
I can do this via DTS, but is this the only way?
I am guessing that I can invoke a DTS package from within a stored
procedure, but I am not sure how I would vary the output name. Maybe I woul
d
put out a standard name and then within the stored procedure generate the do
s
commands to rename and move the output as desired. Seems fairly complex for
something which seems to be fairly simple and common.
So I am kind of new to SQL, so I am just trying to get up to speed.
What approach would be recommended?
Thanks in advance for your assistance!!!I reckon the easiest way to do all this is with a ActiveX task within a DTS
package. You could use a SQL Task or ADO to extract the information to a
recordset, the Excel object library to manipulate and save the data and the
FileSystemObject object library to move/create files and directories. Within
VB Script you could build your filename dynamically too.
You would probably want to do some performance testing though.
"Jim Heavey" wrote:

> I want to run a stored procedure which will run a specific query and put o
ut
> a monthly file each month. The name of the file with be "jan.xls" for Jan
> data and then the next month it will be "feb.xls" for Feb. In addition, I
> want to place the output in a folder which cooresponds month it is.
> If I run a query, is there a way for me to put out an xls file? I see tha
t
> I can do this via DTS, but is this the only way?
> I am guessing that I can invoke a DTS package from within a stored
> procedure, but I am not sure how I would vary the output name. Maybe I wo
uld
> put out a standard name and then within the stored procedure generate the
dos
> commands to rename and move the output as desired. Seems fairly complex f
or
> something which seems to be fairly simple and common.
> So I am kind of new to SQL, so I am just trying to get up to speed.
> What approach would be recommended?
> Thanks in advance for your assistance!!!

No comments:

Post a Comment