output sp_help_revlogin to text file

[groverzucker]

First and foremost, KB #246133 provides a wonderful piece of code in sp_help_revlogin -- it will help me no end in maintaining an effective disaster recovery environment. However . . . there's always a however, isn't there . . . trying to port the output of sp_help_revlogin to a text file that will accompany my backups to the disaster recovery servers is giving me kittens.

I have tried Transact-SQL, DTS, bcp and the Import/Export wizard. My guess is my problem lies in sp_help_revlogin piecemeal printing to the screen. Needless to say, I am not a programmer.

What I would like is to create a text (sql) file just before the nightly production backup, with a file name beginning with the SQL servername, that contains the output script from executing sp_help_revlogin in the production master database.

Can someone steer me in the right direction?

[Hari Prasad]

Hi,

See OSQL from books online

Thanks
Hari
MCDBA

Post by groverzucker
First and foremost, KB #246133 provides a wonderful piece of code in

sp_help_revlogin -- it will help me no end in maintaining an effective
disaster recovery environment. However . . . there's always a however,
isn't there . . . trying to port the output of sp_help_revlogin to a text
file that will accompany my backups to the disaster recovery servers is
giving me kittens.

Post by groverzucker
I have tried Transact-SQL, DTS, bcp and the Import/Export wizard. My guess

is my problem lies in sp_help_revlogin piecemeal printing to the screen.
Needless to say, I am not a programmer.

Post by groverzucker
What I would like is to create a text (sql) file just before the nightly

production backup, with a file name beginning with the SQL servername, that
contains the output script from executing sp_help_revlogin in the production
master database.

Post by groverzucker
Can someone steer me in the right direction?

[groverzucker]

Thank you, Hari. I made some progress. Now my problem seems to be with setting the OSQLPASSWORD

I have set the OSQLUSER and the OSQLPASSWORD environmental variables

Here is the command I am issuing

osql -S SYSDATPR193 -d master -Q "EXECUTE SP_HELP_REVLOGIN" -U -P > C:\TEMP\LOGINS.sq

This creates a file that contains "Login failed for user 'GETLOGIN'." which is the OSQLUSER environmental variable

When I test the command by typing in the username and password, it works. When I use %OSQLPASSWORD% after the -P, I get the above message in the output file

Any ideas

[Tibor Karaszi]

You're not supposed to have the -U and -P switch specified if OSQL is to
pick them up from the environment. Below work fine on my machine:

set osqluser=Kalle
set osqlpassword=pass
osql /STIBDELL\FRESH /Q"SELECT 1"
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

Post by groverzucker
Thank you, Hari. I made some progress. Now my problem seems to be with

setting the OSQLPASSWORD.

Post by groverzucker
I have set the OSQLUSER and the OSQLPASSWORD environmental variables.
osql -S SYSDATPR193 -d master -Q "EXECUTE SP_HELP_REVLOGIN" -U -P >

C:\TEMP\LOGINS.sql

Post by groverzucker
This creates a file that contains "Login failed for user 'GETLOGIN'."

which is the OSQLUSER environmental variable.

Post by groverzucker
When I test the command by typing in the username and password, it works.

When I use %OSQLPASSWORD% after the -P, I get the above message in the
output file.

Post by groverzucker
Any ideas?

[groverzucker]

Well, damn! Thanks, Tibor! Still didn't work when I dropped the -U -P, but when I changed to the slash instead of the dash, it worked! It's an NT4 box running version 7. Go figure! Again, thank you!