carriage return disappears after string concatenation

This topic contains 1 reply, has 2 voices, and was last updated by  Dave Wyatt 2 years ago.

  • Author
    Posts
  • #28217

    Roustam Akhmetov
    Participant

    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.

  • #28256

    Dave Wyatt
    Moderator

    Your $WindowsLogins variable will wind up containing an array of strings, instead of a single string, as written. When you expand an array in a string, it winds up separated by the default field separator (which is a space, unless you've changed the global $OFS variable.)

    Here's how I'd tweak your code to accomplish the desired effect:

    $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
    
    $commands = @(
        'USE [master]'
        $SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($so)} |% {$_.ToString()}
    )
    
    $script = $commands -join $BatchSeperator
    

You must be logged in to reply to this topic.