1

Every now and then, I download some scripts (always the same package with more or less the same files in it). I wrote a powershell script that downloads the master.zip, unpacks it, copy the files to a different location and cleans up afterwards.

Now I want to run the .sql files on all my servers, so the servers always have the most actual version on them.

I wrote this powershell script

Function Install-BrentFirstResponderKit
{
    param(
        $server= @("server1","server2")
    )
    $path = "$env:USERPROFILE\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql\_Monitoring\Brent"
    $onlyFiles = ("sp_Blitz.sql","sp_BlitzCache.sql","sp_BlitzFirst.sql","sp_BlitzIndex.sql","sp_BlitzLock.sql","sp_BlitzWho.sql", "sp_TEST")
    $files = Get-ChildItem $path\* -Include $onlyFiles

    # Credentials to access SQL-Server
    $credential = Get-Credential -username myadmin -Message "Credentials"
    $username  = $credential.UserName
    $password  = $credential.Password
    $password.MakeReadOnly()
    $creds = New-Object System.Data.SqlClient.SqlCredential($username, $password)

    # Create SQL Connection Object
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    foreach($srv in $server)
    {
        # Open SQL connection
        $SqlConnection.ConnectionString = "Server=$srv; Database=master; Integrated Security=false"
        $SqlConnection.Credential = $creds
        $SqlConnection.Open()

        # Execute SQL Statement 
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandTimeout = 0

        foreach($file in $files)
        {
            $SqlCmd.CommandText = Get-Content $file -Raw    
            $SqlCmd.Connection = $SqlConnection
            $sqlcmd.ExecuteScalar()
        }
        $sqlConnection.Close()
    }
}

When I run the powershell script, I get the following error:

Ausnahme beim Aufrufen von "ExecuteScalar" mit 0 Argument(en): "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch." In Zeile:1 Zeichen:1 + $sqlcmd.ExecuteScalar() + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Or this error, depending on which file i ran:

Ausnahme beim Aufrufen von "ExecuteScalar" mit 0 Argument(en): "Incorrect syntax near the keyword 'ALTER'. 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near 'GO'." In Zeile:1 Zeichen:1 + $sqlcmd.ExecuteScalar() + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

The .sql files are all build in the same way:

IF OBJECT_ID('dbo.sp_TEST') IS NULL
    EXEC ('CREATE PROCEDURE dbo.sp_TEST AS RETURN 0;')
GO

ALTER PROCEDURE dbo.sp_TEST
AS
BEGIN
    Select @@Version
END
GO

While I understand the error-message and found a work-around (remove manually the IF block and remove all the GO messages), it's not what I want.

If I have to edit the files manually (or I could script it with $sqlcmd.SelectCommand = $sqlcmd.SelectCommand.Replace(<if-block>,'')), I could also directly install them manually..

So, my question is, why can't I run a script that starts with anything else than Create/Alter? What am I missing??

Thanks for your help.

PS: I don't know if it would work with Invoke-SqlCmd, but my servers are in different domains and in the past, I had some issues with Invoke-SqlCmd

@Aaron: If I run Get-Content $file -Raw I get the follwing output

IF OBJECT_ID('dbo.sp_TEST') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_TEST AS RETURN 0;')
GO

ALTER PROCEDURE dbo.sp_TEST
AS
BEGIN
Select @@Version
END
GO

That is exactly the content of $sqlcmd.CommandText

EDIT: As of Dan Guzmans comment, I changed my code to the following and it is now working:

Function Install-BrentFirstResponderKitV2
{
    param(
        [string[]]$server= @("server1","server2")
    )

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

    #alle dateien finden, welche installiert werden sollen
    $path = "$env:USERPROFILE\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql\_Monitoring\Brent"
    $onlyFiles = ("sp_Blitz.sql","sp_BlitzCache.sql","sp_BlitzFirst.sql","sp_BlitzIndex.sql","sp_BlitzLock.sql","sp_BlitzWho.sql","sp_TEST.sql")
    $files = Get-ChildItem $path\* -Include $onlyFiles

    # Credentials für den Zugriff auf die SQL Server einlesen
    $credential = Get-Credential -username myadmin -Message "Credentials"

    foreach($srv in $server)
    {
        $x = $sqlConnection = New-Object Microsoft.SqlServer.Management.Smo.Server $srv
        $x.ConnectionContext.LoginSecure = $false
        $x.ConnectionContext.set_Login($credential.username)
        $x.ConnectionContext.set_SecurePassword($credential.password)
        $x.ConnectionContext.Connect()

        foreach($file in $files)
        {
            Invoke-Sqlcmd -Database master -Query $(Get-Content $file -Raw) -ServerInstance $x -Username $credential.UserName -Password $credential.GetNetworkCredential().Password
        }
        $x.ConnectionContext.Disconnect()
    }
}
Dan Stef
  • 753
  • 1
  • 10
  • 25
  • If you're building one single script to send to SQL Server, you can't remove all the GO messages. You can't remove the IF block because an ALTER will fail if the CREATE never happened. Certainly Powershell could parse each of the files for "GO" on its own line, and issue each batch (separated by GO) separately. It's possible that `ExecuteScalar()` can't do this automatically for you - did you try `ExecuteNonQuery()`? – Aaron Bertrand Feb 13 '19 at 13:05
  • Thanks @AaronBertrand, I get the same error message – Dan Stef Feb 13 '19 at 13:12
  • @Dan Guzman : How is the C# question a duplicate from my powershell question? At the end they also use `server.ConnectionContext.ExecuteNonQuery` which is the same that I tried, but it's not working! – Dan Stef Feb 13 '19 at 13:15
  • Can you show the output of a `Get-Content` that fails? Are you sure it exactly matches the format and sequence of the sp_Test sample you posted? – Aaron Bertrand Feb 13 '19 at 13:27
  • @DanStef, both PowerShell and C# can use .NET classes, it's just language syntax. You are using `SqlClient` rather than SMO in the referenced answer. Try using SMO instead: `server.ConnectionContext.ExecuteNonQuery()` so the script with GO batch separators can be executed. – Dan Guzman Feb 13 '19 at 13:40
  • @DanGuzman: Thanks, I understand and will try it and revert back. – Dan Stef Feb 13 '19 at 14:09

0 Answers0