[RESOLVED] SQL Server - Is it possible to have a Job that creates Excel file multiple sheets

[MMock]

I want to make sure i give my boss the right answer regarding an enhancement she wants. We currently have a sql job that runs weekly which sends an email which is the results of a query both in the email body and an attached csv file. The data is "rental units". Currently there is one type of rental unit. We are adding more types so we would like the output file (currently the csv file that gets attached to the email) to be an excel file with a sheet for Long Term rentals, a sheet for Service rentals, a sheet for Sales Demo rentals etc. The current sql job runs bcp like so:

bcp
SELECT * FROM qryRentalsActive ORDER BY CONVERT(datetime, DateEntered) DESC
queryout @theFileName
-c
-t ,
-S <our server name>
-T

I didn't write the code that is this sql job but I just read through the bcp documentation and I understand all the parameters. It is also my understanding that I can't use bcp to create an Excel file with multiple sheets. You can use SSIS. But can that still be a weekly scheduled job that we run in SQL Server? My boss is saying we can have a command button in our C# application to do this, which I would completely know how to do, but I want to make sure that is the best way because I feel we'd be taking an automatic process and putting the burden on the user to manually run a "report", but maybe it'd also be good to have this function available on-demand instead of once a week. I am waiting to have a verbal discussion with my boss, so far it's been just sporadic discussion in our PM tool. I don't know if she's offering me an "easy out" but would prefer it to be automated weekly. So I thought I'd ask this forum what you guys think is the better way. Thanks.

[dday9]

To be honest, I would do this as a C# routine.

You could do this using SSIS but comparatively speaking, which do you think would be easier to setup and maintain?

[MMock]

Yes, we were having a live Teams meeting when your reply came in so I showed it to her and it's pretty much what we were deciding anyway. What she likes about sending automated emails is people get hit over the head with it rather than remembering to go hit a button, but whatever. If it's their job to hit the button they will be told they have to do it. Thanks.

[techgnome]

You could handle that one of two ways ... do the code in C# ... and then 1) it's possible to load an assembly into SQL Server and run it like a sproc. Fun to do, crap to maintain... but then at least you could still schedule it and run it. or 2) (and this is probably how I'd do it) write it as a C# app, and then deploy it to the server and then used Windows Scheduler to run it.


-tg

[MMock]

TY. We are going to nix the sql server job. I guess management decided they don't need it to run on any kind of a schedule. It generates a report of data. Really, people can do it as they need it.