May 18, 2011 at 8:27 am
Hello All,
Typically I export the results of sp_help_revlogin to a file on a daily basis for backup/DR purposes.
EXEC master..xp_cmdshell 'osql -d master -E -Q "EXEC sp_help_revlogin" -w 300 -o \\MyFolder\Users.sql'
What are my other options for outputting this to a file if xp_cmdshell is not enabled?
This is a 2008 R2 server.
Thanks.
May 18, 2011 at 8:30 am
BCP utility
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 18, 2011 at 8:36 am
Chrissy321 (5/18/2011)
EXEC master..xp_cmdshell 'osql -d master -E -Q "EXEC sp_help_revlogin" -w 300 -o \\MyFolder\Users.sql'
What are my other options for outputting this to a file if xp_cmdshell is not enabled?
Why not use PowerShell?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 8:37 am
You could use a powershell sql agent task. I've not tested this but I know the agent module has certain filesystem restrictions so if this isnt possible you could do this externally as a windows scheduled task on another server (backup server as a central point?)
invoke-sqlcmd is the powershell command and | out-file to write it as an output file.
May 18, 2011 at 8:40 am
I'll try the powershell approach and report back.
May 18, 2011 at 9:35 am
I'm running into this issue when I try to edit a job with a powershell step.
I thinks this is unrelated to the powershell script.
This works from the ps command line
invoke-sqlcmd -query "exec sp_help_revlogin" -database master -serverinstance Sever\Instance | export-csv -path C:\Users.sql
This fails from the ps command line
invoke-sqlcmd -query "exec sp_help_revlogin" -database master -serverinstance Sever\Instance | export-csv -path \\MyNetworkServer\MyFolder\Users.sql
Error:Cannot open file because the current provider (SqlServer) cannot open a file.
I'll need to come back to this tomorrow...
May 18, 2011 at 9:43 am
You may be running into a limitation in the mini-shell. I seem to recall there are some limitations there. Try rewriting it and directly invoke Powershell.exe and change the job type to Operating System (cmdexec)
May 18, 2011 at 10:16 am
sounds like it could be NTFS\share permissions on the file path. The NTFS permissions may be correct but if setting up a new share and not modifying the share permissions they will default to Everyone readonly. When share and NTFS permissions combine the most restrictive applies. Be worth checking the permissions first
I prefer BCP for my login exports
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 10:20 am
Perry Whittle (5/18/2011)
I prefer BCP for my login exports
Doesn't BCP require xp_cmdshell to call it?
May 18, 2011 at 10:30 am
Chrissy321 (5/18/2011)
Doesn't BCP require xp_cmdshell to call it?
You can use BCP with Powershell.
http://stackoverflow.com/questions/2479434/run-a-shell-command-with-arguments-from-powershell-script
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 2:12 pm
Craig Purnell (5/18/2011)
You may be running into a limitation in the mini-shell. I seem to recall there are some limitations there. Try rewriting it and directly invoke Powershell.exe and change the job type to Operating System (cmdexec)
Errm, Thats what I said.
MysteryJimbo (5/18/2011)
You could use a powershell sql agent task. I've not tested this but I know the agent module has certain filesystem restrictions so if this isnt possible you could do this externally as a windows scheduled task on another server (backup server as a central point?)invoke-sqlcmd is the powershell command and | out-file to write it as an output file.
May 18, 2011 at 4:05 pm
Chrissy321 (5/18/2011)
Perry Whittle (5/18/2011)
I prefer BCP for my login exportsDoesn't BCP require xp_cmdshell to call it?
No it's an external SQL server executable. I use a SQL server agent job to execute it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 5:15 pm
Can anyone provide specifics on using BCP through SQL Agent?
I have a 'Operating system (CmdExec)' step set up with the following command.
bcp master.dbo.sp_help_revlogin out c:\test.txt -n -SMYSERVER\MYINSTANCE -T
I am getting an error.
Message
...NativeError = 208 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'master.dbo.sp_help_revlogin'. Process Exit Code 1. The step failed.
May 19, 2011 at 2:36 am
Chrissy321 (5/18/2011)
Can anyone provide specifics on using BCP through SQL Agent?I have a 'Operating system (CmdExec)' step set up with the following command.
bcp master.dbo.sp_help_revlogin out c:\test.txt -n -SMYSERVER\MYINSTANCE -T
I am getting an error.
Message
...NativeError = 208 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'master.dbo.sp_help_revlogin'. Process Exit Code 1. The step failed.
BCP is designed to export from a table or a query. The object you have specified is a stored procedure, See the following
From a table
bcp master..syslogins out "c:\Loginsout.dat" -N -S MYSERVER\MYINSTANCE -T
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 20, 2011 at 4:24 pm
I hoping to generate scripts which I could run on a different server so I'll need sp_help_revlogin. I may look at SSIS or maybe return back to my adventures with powershell...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply