Creating SQL Report Query to Excel

[TekRob81]

Hi,

I am looking for some advice. I have a query where I want the results to be displayed in an excel file that I have a template for.

I would like a job to be run once a week, which gets this query, writes to the template file and saves with a name such as the current date etc and then email me the file.

Can anyone help me regarding this? I am eager to learn.

I am using SQL Server 2000 Enterprise.

Any help would be greatly appreciated.

Thanks

Rob

 

[kjv1611]

There are at least a few different directions you could go with this, and they will depend upon what you have permissions to, what tools are available at your business, and what you're comfortable with.

My first thought right off the bat is to use Reporting Services altogether if possible. I think that'll make it the easiest setup.

Another thought is to have a Stored Procedure which runs building your result set each day or however often needed, and then a separate script for sending out the email message.

Another thought would be an Access database that runs a passthrough query to build the table, and then emails the file to you in Excel format.. This could be run on Windows Task Scheduler.

But I do also have to wonder - why do you need it emailed?

Oh, another thought - if this is just where you need the latest information all the time, I suppose you could set up a View on SQL Server that would grab the data you need into one table-like object which you could then query from, import into Excel, whatever... then you wouldn't need any email, but rather you could have your Excel template's data source set to the view, and then filter out what results you don't need... of course, depending upon your data and other details, you may need to filter that data... which can also be done using MS Query in Excel.

Look here for some info on using MS Query for Excel - I almost think that could end up being what you would want to go with:faq68-5829

A Microsoft Reference:

http://office.microsoft.com/en-us/e...ry-to-retrieve-external-data-HA010099664.aspx

Also, I'm not sure which version of Excel you'll be using, nor am I sure how far back MS Query goes, so you may need to check into that as well.

 

[TekRob81]

Hi,

Many thanks for getting back to me. At the moment I've just used a DTS package. I have created the excel file template. I can come up with a VB Script to send an email with it attached. The only reason I need it emailed is that this is a report for some of our internal users that they get a list of updated data. They should not have access to SQL etc.

We do not have Reporting Services installed at this time, although I think we are looking at this for future use. So right now that is not an option.

I assume there is no difference using a query and a stored proc? to be honest it is not a resource intensive query.

Thanks

Rob

 

[philhege]

A lot of my clients require that reports be emailed to them. I have an SSIS package that creates a multi-tabbed Excel file and emails it to a list of recipients.

You mentioned DTS; are you still on SQL2K?

-------++NO CARRIER++-------

 

[philhege]

Oops, just caught the end of your original post.

I'd say upgrade, but I know not everyone's made of money.

-------++NO CARRIER++-------