Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,470

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

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,498

    Re: SQL Server - Is it possible to have a Job that creates Excel file multiple sheets

    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?
    "Code is like humor. When you have to explain it, itís bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,470

    Re: SQL Server - Is it possible to have a Job that creates Excel file multiple sheets

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,385

    Re: [RESOLVED] SQL Server - Is it possible to have a Job that creates Excel file mult

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,470

    Re: [RESOLVED] SQL Server - Is it possible to have a Job that creates Excel file mult

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width