Post reply

SSMS: How to save multiple resultsets from same query into 1 result or 1 file?

  • June 16, 2020 at 2:52 pm

    #3763479

    I am submitting an official request for DBA of certain servers to run a script on those servers and give me back the resultset.

    However the script (the actual script is 3 times larger than the attached and returns 20 more results) returns multiple results,

    so a DBA will not be copy/pasting each resultset into an Excel spreadsheet for me one by one.

    What/how can I do to (spending the least time/effort on it) to get the output as ONE RESULTSET or ONE FILE?

     

    thanks.

    Attachments:
    You must be logged in to view attached files.

    Likes to play Chess

  • June 16, 2020 at 3:18 pm

    #3763502

    other than UNION all results and patch with fake columns ('' and zeros and other defaults and converting datatypes)

    i do not see other / easier / quicker way .

    But perhaps there are, so I am double-checking.

    Likes to play Chess

  • June 16, 2020 at 4:28 pm

    #3763535

    one thought (without downloading your code, so excuse any suggestions you already did) - put "SET NOCOUNT ON" at the start of your script and have the DBA run the script to text instead of to grid.  Save the results as a CSV file and pass it back to you.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • June 16, 2020 at 4:38 pm

    #3763536

    Maybe use SSIS and a For Each file loop, and dump them all into a single table?

    Or use Excel... put all the files in a single folder, use a folder source, append them to get one large dataset?

  • June 16, 2020 at 6:20 pm

    #3763567

    You can rewrite the script to run in SQLCMD mode:

    :connect YourServerName
    :out \\somesharename\somefilename.txt

        Set Nocount On;

     Select ...
       From first_table
      Where somecriteria;

     Select ...
       From second_table
      Where somecriteria;

     Select ...
       From third_table
      Where somecriteria;
         Go

    :out stdout

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • June 16, 2020 at 7:00 pm

    #3763596

    or better yet - setup a powershell that executes all required queries and writes the output of each onto a csv file (one per query) - and set it so it can be supplied with a list of servers and dbnames to process.

  • June 18, 2020 at 4:06 pm

    #3764203

    VoldemarG wrote:

    I am submitting an official request for DBA of certain servers to run a script on those servers and give me back the resultset.

    However the script (the actual script is 3 times larger than the attached and returns 20 more results) returns multiple results,

    so a DBA will not be copy/pasting each resultset into an Excel spreadsheet for me one by one.

    What/how can I do to (spending the least time/effort on it) to get the output as ONE RESULTSET or ONE FILE?

    thanks.

    To be sure, I'm NOT trying to start an argument here.

    With that in mind, I have to ask... why are you doing all of this?  And, if it's necessary that you be the one one to do all of this, why aren't you classified as a DBA so that you don't have to go through the DBA team?  Another question is, why isn't the DBA team actually doing all of this?

    It seems like you're trying to provide and enterprise-wide solution and the DBAs, who are responsible for the servers the enterprise-level, seem like the ones that should be working on this.

    Like I said... not trying to start an argument.  I'm just curious.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2020 at 4:31 pm

    #3764207

    You could create an SSRS report, put each query into a separate table, add page breaks for each, and then set up a subscription to email the Excel file to you as desired.

  • June 18, 2020 at 4:33 pm

    #3764208

    You could create a temporary table, insert all the rows you need into that. Then just select * from the temporary table.

  • June 19, 2020 at 1:26 pm

    #3764364

    Use your existing SQL jobs and drop each into an Excel Power Query task.  Consider using Excel Power Query to perform the task(s) to run each SQL job to fetch the results.  Then define a Power Query script to have Power Query Append each result set into a final worksheet.  /Frank

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply