carriage return disappears

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Roustam Akhmetov Roustam Akhmetov 1 year, 7 months ago.

  • Author
    Posts
  • #28257
    Profile photo of Roustam Akhmetov
    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.

  • #28263
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    You could try using a here string to build your final script

    $BatchSeperator = "`r`nGO"

    $script1 = "USE [master]$BatchSeperator"

    $script = @"
    $script1

    $windowslogins
    "@

    $script

    That will preserve the line breaks.

    I've found using here strings a better way to create multi-line strings. Much easier to get the line breaks as you want

  • #28316
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    Thanks Richard, tried this, but it did not work. $WindowsLogins has line breaks, once I concatenate it to $script1 variable, the line breaks dissapear.

You must be logged in to reply to this topic.