SQL query to variable in powershell

Question

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

Cannot reproduce. What's the result if you execute `$SqlConnection.state` after calling `.Open()`? Please add this to the question, don't post as a comment as those are hard to read. — vonPryz, Jul 25 '18 at 08:12
Is there any way, that you are only reusing the `$SqlCmd.ExecuteScalar()` without executing/opening the connection again? e.g. you only executed the 4th to 6th line. Just an observation since in your error it shows as `$results` instead of your posted code which is `$dbname` — CodeNagi, Jul 25 '18 at 08:18

CodeNagi · Accepted Answer · 2018-07-25T08:55:32.457

1

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.

edited Jul 25 '18 at 08:55

answered Jul 25 '18 at 08:23

CodeNagi

419
2
10

Thanks the $results solved the problem. I got the correct result what I needed, also I made some changes in my script with the suggestions in this post. – damHU Jul 25 '18 at 08:53

SQL query to variable in powershell

1 Answers1