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;')
GOALTER 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()
}
}