While invoking the command to import the master..sp_help_revlogin output to the server erroring out

Question

While invoking the command to import the master..sp_help_revlogin output to the server erroring out

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 19, 2017 at 11:34 am

#401430

Hello
I executed the below command to extract the login to a .sql file.
sqlcmd -E -S server -d master -Q "set nocount on; exec master..sp_help_revlogin; set nocount off;" -o "c:\Logins.sql"
But while importing and executing the same .sql file to another sql instance - getting below error -
PS SQLSERVER:\> invoke-sqlcmd -inputfile "c:\Logins.sql" -serverinstance server -database "master" | Out-File -filePath "c:\file_logins.txt"
invoke-sqlcmd : Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
The identifier that starts with 'ma
                                          ' is too long. Maximum length is 128.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous
statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
The identifier that starts with 'mast
                                         ' is too long. Maximum length is 128.
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous
statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near ','.
At line:1 char:1
+ invoke-sqlcmd -inputfile "c:\Logins.sql" - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo    : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Thanks.

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

SQL-DBA-01 - Thursday, January 19, 2017 11:34 AM
Hello
I executed the below command to extract the login to a .sql file.
sqlcmd -E -S server -d master -Q "set nocount on; exec master..sp_help_revlogin; set nocount off;" -o "c:\Logins.sql"
But while importing and executing the same .sql file to another sql instance - getting below error -
PS SQLSERVER:\> invoke-sqlcmd -inputfile "c:\Logins.sql" -serverinstance server -database "master" | Out-File -filePath "c:\file_logins.txt"
invoke-sqlcmd : Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
The identifier that starts with 'ma
                                          ' is too long. Maximum length is 128.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous
statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
The identifier that starts with 'mast
                                         ' is too long. Maximum length is 128.
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous
statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near ','.
At line:1 char:1
+ invoke-sqlcmd -inputfile "c:\Logins.sql" - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo    : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Have you reviewed the c:\Logins.sql file to search for any possible errors on it?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

If I copy and paste the entire sql to ssms and run it, it just runs without having any error

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Hi Luis

I thought to copy the .sql o/p and running it manually. When I do that I found that the extract through sqlcmd did not work properly. Many places, it just extracted few portion.

sqlcmd -E -S server-d master -Q "set nocount on; exec master..sp_help_revlogin; set nocount off;" -o "c:\Logins.sql"

Do I have to modify the script so that It will extract the entire output while the proc sp_help_revlogin is being called via sqlcmd.?

Please suggest.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Can I use something like this? But this is giving me error -

Message
Executed as user: . Msg 105, Level 15, State 1, Server , Line 1 Unclosed quotation mark after the character string 'declare @t table(names varchar(8000)) '. Process Exit Code 0. The step succeeded. File is not being generated though.

sqlcmd -E -S servername -d master -Q "declare @t table(names varchar(8000))
insert into @t
exec master..sp_help_revlogin
set nocount on
select * from @t
set nocount off;" -o "D:\Logins\Logins.sql"

Thanks.