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!!!

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 would
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 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 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 would
> 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 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!!!