Run same query on multiple servers with sqlcmd and save the output

  • salliven

    SSCrazy

    Points: 2644

    Hello,

    I have this 3 lines:

    sqlcmd -S "Server1" -d <db> -U <user> -P <password> -i query.sql -o result.txt 
    sqlcmd -S "Server2" -d <db> -U <user> -P <password> -i query.sql >> result.txt
    sqlcmd -S "Server3" -d <db> -U <User> -P <DifferentPassword> -i query.sql >> result.txt

    If I copy and paste this code to a cmd window this is running well.

    If I save this code to a .bat file (e.g.: sqlrun.bat) and I try to call it from a new cmd window (C:\Scripts>sqlrun.bat) I'm getting this error for first two connections:

    Sqlcmd: Error: Microsoft SQL Server Native Client <xx.x> : Login failed for user '<User>'...

    Can you help me what is wrong?

    • This topic was modified 2 weeks, 2 days ago by  salliven.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720426

    Yes, the login isn't correct. That's what the error is.

  • salliven

    SSCrazy

    Points: 2644

    But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?

  • Chris Harshman

    SSC-Forever

    Points: 42145

    Salliven,

    What is the error on the server side?  Look at the SQL Server Logs in the Management folder of Object Explorer, or run a SQL command like:

    EXEC master.dbo.xp_readerrorlog 0, 1, N'Login Failed';

    This error is often more detailed than the error you receive on the client side, and will help you troubleshoot this issue easier.

  • x

    SSC-Insane

    Points: 23578

    shake up your troubleshooting. don't save one copy of that batch file, fail, then give up. create another batch file. run a hexdump on your batch and look for hidden characters (happened to me). try a batch file with a single line, with the failing line if you know which line fails. check the logs like previously recommended. type the sqlcmd line manually in a cmd window. run ssms and connect to the server with the failing login using the failing credentials.

    errors can literally come from anywhere 🙂

     

  • x

    SSC-Insane

    Points: 23578

    reminds me of a story from one of my favorite books, a truly geeky must read in my opinion, "the soul of a new machine" by tracy kidder. they're bringing a new minicomputer to life, its going to be a shiny new line of 32 bit supermini's (the book has been out for awhile heh), and they're deep into the debug on a prototype, having random nondeterministic errors that are often the case with complex systems running with a large number of internal states, etc, and they're trying to recreate this random error or at least encourage it to happen more often, and the VP of engineering comes in, gets a run down of what is happening, logic probes everywhere detailing intently internal signals etc etc

    The guy steps up to the prototype with the board on an extender, grabs it, and shakes it quite violently and it fails. the engineers are then tasked to rewire the thing as connections just got too loose heh

     

  • Jeff Moden

    SSC Guru

    Points: 996829

    x wrote:

    reminds me of a story from one of my favorite books, a truly geeky must read in my opinion, "the soul of a new machine" by tracy kidder. they're bringing a new minicomputer to life, its going to be a shiny new line of 32 bit supermini's (the book has been out for awhile heh), and they're deep into the debug on a prototype, having random nondeterministic errors that are often the case with complex systems running with a large number of internal states, etc, and they're trying to recreate this random error or at least encourage it to happen more often, and the VP of engineering comes in, gets a run down of what is happening, logic probes everywhere detailing intently internal signals etc etc

    The guy steps up to the prototype with the board on an extender, grabs it, and shakes it quite violently and it fails. the engineers are then tasked to rewire the thing as connections just got too loose heh

    Too funny... we had something similar happen on board the submarine I was on.  A relatively new piece of equipment started to suffer from sporadic errors after just a couple of months being underway.  They'd trace the problem to a board and replace it only to have a different problem appear after even just a couple of minutes later.

    They ran out of replacement boards and, since we were underway, there was no where to go to get new ones.

    I took a look at some of the old "failed" boards under a bright light and noticed the edge connectors appeared to have a bit of a coating on them.  The current failure required a board that they ran out of replacements for, so I took a good old fashioned pink eraser and polished up the edge connectors, removing the apparent coating that had built up in the process.  I installed the board and fired the system up and it worked at least for a couple of minutes.

    I powered down the machine, took out all the boards, did the pink eraser thing to all the boards, put all the boards back in, powered it up, and no more problems.

    It was the tiny bit of amine (a chemical used to remove CO2 from the air) that gets into the air and seemed to love plating out on gold edge connectors in machines.  The pink eraser was a bit too abrasive for continued use but we figured out that we could wipe the connectors with a bit of white "gillie" (our pet name for pure grain alcohol, which was used to clean compensatory plates on our sonar systems) to remove the coating and added that process to our monthly maintenance on the machine.  We even built a special tool (basically, something that looked a bit like a gutter with two fine grain sponges held close together that also acted as wicks for the alcohol) to run the edge connectors through that did the cleaning "auto-magically" and quickly.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996829

    Steve Jones - SSC Editor wrote:

    Yes, the login isn't correct. That's what the error is.

    salliven wrote:

    But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?

    It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file.  I'm with you that it seems that they should run just fine in a batch file.

    My real concern is you putting logins and passwords in a clear text batch file.  At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection".  Then, to use the batch file, login with that particular login and run the batch file.

    You simply must NEVER include clear text logins in any batch file or similar things.  Period.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • x

    SSC-Insane

    Points: 23578

    Jeff Moden wrote:

    Steve Jones - SSC Editor wrote:

    Yes, the login isn't correct. That's what the error is.

    salliven wrote:

    But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?

    It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file.  I'm with you that it seems that they should run just fine in a batch file.

    My real concern is you putting logins and passwords in a clear text batch file.  At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection".  Then, to use the batch file, login with that particular login and run the batch file.

    You simply must NEVER include clear text logins in any batch file or similar things.  Period.

    So what should I do about my open ssh private keys?

     

     

  • Jeff Moden

    SSC Guru

    Points: 996829

    x wrote:

    Jeff Moden wrote:

    Steve Jones - SSC Editor wrote:

    Yes, the login isn't correct. That's what the error is.

    salliven wrote:

    But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?

    It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file.  I'm with you that it seems that they should run just fine in a batch file.

    My real concern is you putting logins and passwords in a clear text batch file.  At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection".  Then, to use the batch file, login with that particular login and run the batch file.

    You simply must NEVER include clear text logins in any batch file or similar things.  Period.

    So what should I do about my open ssh private keys?

     

    Dunno... good luck with those.  😀

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • x

    SSC-Insane

    Points: 23578

    Jeff Moden wrote:

    x wrote:

    Jeff Moden wrote:

    Steve Jones - SSC Editor wrote:

    Yes, the login isn't correct. That's what the error is.

    salliven wrote:

    But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?

    It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file.  I'm with you that it seems that they should run just fine in a batch file.

    My real concern is you putting logins and passwords in a clear text batch file.  At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection".  Then, to use the batch file, login with that particular login and run the batch file.

    You simply must NEVER include clear text logins in any batch file or similar things.  Period.

    So what should I do about my open ssh private keys?

    Dunno... good luck with those.  😀

    Apologies for being a bit off topic, but even though open ssh keys are plain text credentials saved to disk, they have a unique characteristic which doesn't share the problems that OP would have with plain text credentials in batch files. Took me a while to get a handle on it, I'm not the sharpest stick in the woods LOL

     

Viewing 11 posts - 1 through 11 (of 11 total)

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