Query results to EXCEL via Magic

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query results to EXCEL via Magic

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-24 : 09:10:52

I need to implement a periodic report sent via email as an EXCEL attachment or perhaps as a URL to an EXCEL file on disk.

The stored proc that creates the recordset is the easy part. Scheduling the stored proc as an SQL job as easy to manage.

Will I have to resort to .NET or ASP to get the resultset into EXCEL format or is there a shortcut that will make my life easy?

Argh!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-24 : 09:16:19

Reporting Services ?
Or is that overkill ?

Damian
Ita erat quando hic adveni.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-24 : 09:20:59

quote:
Originally posted by Merkin

Reporting Services ?
Or is that overkill ?

I wish I could do a 49 step death scene to music in this forum.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-24 : 09:22:33

I know some good Flash guys that could help

Yes then, you'll need to write some code to do this.

Damian
Ita erat quando hic adveni.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-24 : 09:50:12

Why not just dts out a view?

Or does it hasve to be "pretty"?

How about com objects?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-24 : 09:52:31

Or, why not use access?

Set one that when launched will run a macro and do everything you need, and then close....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-24 : 09:59:01

quote:
Originally posted by X002548

Why not just dts out a view?

... DTS out a view? To EXCEL, on a scheduled basis, and email it? If this can be done, give me some details?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-24 : 10:10:53

I'm not to keen on the mailing thing...

But why can't you create a view that does whatever you want.

Create a DTS package that exports the view

and schedule it?

Just put it out to a share the user has access to.

I'm missing something....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-24 : 10:13:56

quote:
Originally posted by X002548

I'm not to keen on the mailing thing...

But why can't you create a view that does whatever you want.

I think even DTS has it's limits...

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-24 : 10:17:24

Actually Brett,

Your idea is a great shortcut. It has a single fixed filename so the EXCEL reports will need to be downloaded (or overwritten).

I can schedule another job to email notification of the report availability.

Thanks,

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-01 : 20:12:07

I've used EM's "export" to save a DTS package that'll exec a stored proc, and store the results in an EXCEL "xls" format on my hard drive.

Works great... first time.

When scheduled, subsequent executions generate the following failure message:

quote:
Executed as user: xxxxxx\sqlservice. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Create Table Results Step DTSRun OnError: Create Table Results Step, Error = -2147217900 (80040E14) Error string: Table 'Results' already exists. Error source: Microsoft JET Database Engine Help file: Help context: 5003010 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: -534840599 (E01EFAE9) Error string: Table 'Results' already exists. Error source: Microsoft JET Database Engine Help file: Help context: 5003010 DTSRun OnFinish: Create Table Results Step DTSRun OnStart: Copy Data from Results to Results Step DTSRun OnProgress: Copy Data from Results to Results Step; 19 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 19 DTSRun OnFinish: Copy Data from Results to Results Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 20:16:55

Here's the important part in that error message:

quote:

Table 'Results' already exists

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 20:22:50

Are you "creating" a staging table 1st (results) before exporting. If so DROP/TRUNCATE it before inserting/appending data

Have a look at Madhivanans Excel script in the Script library. Also deals with exporting

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 20:26:40

Note to self:

Refresh prior to reply!!!

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 20:32:31

quote:
Originally posted by X002548

Or, why not use access?

Set one that when launched will run a macro and do everything you need, and then close....

Brett

8-)

On another note you can schedule an Access macro to run via a bat file rather than Start up or AutoExec


"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "D:\MyAccessDBs\DBName.mdb /x MacroName"

Dont know whether thats of use to anyone

Beauty is in the eyes of the beerholder

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-01 : 22:08:58

Found the cause.

The table "Reports" is something DTS is creating. Seems the conflict exists only if the target spreadsheet already exists on the hard drive.

Deleting the existing spreadsheets in the folder solved the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources