sqlpowershellmcafee

SQL query to variable in powershell

There is my script:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=xxxxx;Database=xxxx;User ID=xxxx\xxxx;Password=xxxxx;Trusted_Connection=True;"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT version FROM xxxx.dbo.EPOAvertContentUpdates where productId=VSCANDAT1000"
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-output "version" $dbname

The error what I get:

Exception calling "ExecuteScalar" with "0" argument(s): "ExecuteScalar requires an open and available Connection. The connection's current state is closed."
At line:12 char:1
+ $results = $SqlCmd.ExecuteScalar()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

I want to get the product ID number from our database, and store it to variable to comapre it with other variable. Any suggestion what is the problem? Thanks for the help!

@vonPryz: the connection state Open

Solution

  • Issue speculation: You executed the posted script snippet once, and then just re-executed the lines and changed the variable to $results:

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT version FROM xxxx.dbo.EPOAvertContentUpdates where productId=VSCANDAT1000"
$SqlCmd.Connection = $SqlConnection

    But this time for another database?

    When executing any other TSQL, please make sure to open the SQL connection first, execute the TSQL, and then close it.