Display commands and results to the screen or file using SSMS

Question

Post reply

Display commands and results to the screen or file using SSMS

caz100

SSCommitted

Points: 1827
More actions
September 21, 2017 at 2:10 am

#349208

Hi
I want to run a long list of disable accounts & update password commands in sqlserver using SSMS.
I want to see the command and the result for example displayed to the screen or to a text file like below for example:
ALTER LOGIN test1 WITH PASSWORD='sdf(*9893sfgsDD!';
command successful
Alter login test2 disable ;
command unsuccessful

Can anyone tell me how to do this in ssms?
A similar way to do this in oracle would be spool results to a file.
thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

In SSMS, press CTRL+SHIFT+F to have the results written to a file.
😎

caz100 SSCommitted Points: 1827 More actions · Answer 2

@Eirikur Eiriksson yes but this wont show the update statement executed and the result for each and every statement?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

caz100 - Thursday, September 21, 2017 2:33 AM
@Eirikur Eiriksson yes but this wont show the update statement executed and the result for each and every statement?

Use the print command to echo the statements before executing and an if conditional that tests if success to echo the results to the file.
😎

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Or use Sqlcmd with [-e echo input] and [-o outputfile] options
p.s. Don't forget to batch the commands with GO

Far away is close at hand in the images of elsewhere.
Anon.

caz100 SSCommitted Points: 1827 More actions · Answer 5

@Eirikur Eiriksson print and if command sounds interesting....any idea of how that would look code wise for the following example code?

ALTER LOGIN test1 WITH PASSWORD='sdf(*9893sfgsDD!';
ALTER LOGIN test22 disable;
ALTER LOGIN test99 WITH PASSWORD='778(*9893sfgsDD!';

@David Burrows - thanks but I am trying to avoid sqlcmd for the moment as I want to visibly see the code on screen first to double check....execute it and just keep a record of what worked and didnt....ie save the results off to a text file showing what commands were executed successfully and what commands were not executed correctly.