carriage return disappears after string concatenation

I am using this script to script out windows server logins:

$timestamp = Get-Date -Format yyyy-MM-dd

$Srv = 'MyServer\MyInstance'

SL SQLSERVER:\SQL\"$Srv"\Databases

$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Srv;

$SQLInstance.name

$BatchSeperator = "`r`nGO" #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.

$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

$so.IncludeDatabaseContext = $false;

$so.AllowSystemObjects = $false;

$so.IncludeDatabaseRoleMemberships = $true;

$so.IncludeIfNotExists = 1

$script = "USE [master]$BatchSeperator" 

$WindowsLogins = $SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($so)} |% {$_.ToString()+$BatchSeperator};

$script = $script + "`r`n" + $WindowsLogins 

 

$WindowsLogins text has the correct structure:

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'builtin\administrators')

CREATE LOGIN [builtin\administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

 

However after I concatenate it to $script variable: 

$script = $script + "`r`n" + $WindowsLogins 

I get this:

USE [master]

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'builtin\administrators')

CREATE LOGIN [builtin\administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\SYSTEM')

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO EXEC ...

 

As you can see the carriage return dissapeared from the string before "IF", this gives me a syntax error when I try to use this script.

 

Any ideas?

 

Thanks.

Parents
No Data
Reply
  • Tried what you suggested, but still the same problem:

     

    $timestamp = Get-Date -Format yyyy-MM-dd

    $Srv = 'MyServer\MyInstance'

    SL SQLSERVER:\SQL\"$Srv"\Databases

    $SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Srv;

    $SQLInstance.name

    $BatchSeperator = "`r`nGO" #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.

    $so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

    $so.IncludeDatabaseContext = $false;

    $so.AllowSystemObjects = $false;

    $so.IncludeDatabaseRoleMemberships = $true;

    $so.IncludeIfNotExists = 1

    $script = "USE [master]$BatchSeperator" 

    $WindowsLogins = $SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($so)} |% {$_.ToString()+$BatchSeperator};

    $script = $script"`r`n" 

    $script = $script + $WindowsLogins 

Children
No Data