SQL Connection Issue
Posted: Mon Nov 26, 2018 7:55 pm
To help you better we need some information from you.
Product, version and build: PrimalScript Version 7.4.118
32 or 64 bit version of product: 64 bit
Operating system: Windows 7
32 or 64 bit OS: 64 Bit
*** Please add details and screenshots as needed below. ***
I have a script that connects to a SQL database and retrieves data. I may have stumbled over a possible bug. Below is the script.
If I run this script without the variable $zzzzzzzzzzzzzzzzz or have a value assigned to that variable it fails, error is after the script. If I have the (or any) variable included but don’t assign a value as shown it runs correctly and returns the desired data. Is this a bug or am I missing something?
Many Thanks is advance.
Wayne
# ****** SQL Connection Script******
$SQL_Database = "MyDB"
$SQL_Table = "MyTable"
$position = "CPE010"
$Revision = "26"
$zzzzzzzzzzzzzzzzz
#
#
$connCD = New-Object System.Data.SqlClient.SqlConnection
$connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_Database+";Integrated Security=SSPI;"
$cmdTextCD = "Select * FROM " + $SQL_Table + " WHERE [PD_PosNum] = '" + $position + "' AND [Status] = '1' AND [Revision] = '"+ $Revision +"' Order By [PD_PosNum]"
$connCD.Open()
$cmdCD = New-Object System.Data.SqlClient.SqlCommand($cmdTextCD,$connCD)
$rdrCD = $cmdCD.ExecuteReader()
if ($rdrCD.Read()){
$rdrCD["PD_Pos_Auth"].ToString()
}else{
Write-Host "Nothing"
}
$connCD.Close()
# ****** SQL Connection Script Error******
ERROR: New-Object : Cannot find type [System.Data.SqlClient.SqlConnection]: verify that the assembly containing this type is loaded.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:9 char:11
ERROR: + $connCD = New-Object System.Data.SqlClient.SqlConnection
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
ERROR: + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
ERROR:
ERROR: The property 'ConnectionString' cannot be found on this object. Verify that the property exists and can be set.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:10 char:1
ERROR: + $connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_ ...
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : PropertyNotFound
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:12 char:1
ERROR: + $connCD.Open()
ERROR: + ~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader: Connection property has not been initialized."
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:14 char:1
ERROR: + $rdrCD = $cmdCD.ExecuteReader()
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : InvalidOperationException
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:15 char:6
ERROR: + if ($rdrCD.Read()){
ERROR: + ~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:20 char:1
ERROR: + $connCD.Close()
ERROR: + ~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
Product, version and build: PrimalScript Version 7.4.118
32 or 64 bit version of product: 64 bit
Operating system: Windows 7
32 or 64 bit OS: 64 Bit
*** Please add details and screenshots as needed below. ***
I have a script that connects to a SQL database and retrieves data. I may have stumbled over a possible bug. Below is the script.
If I run this script without the variable $zzzzzzzzzzzzzzzzz or have a value assigned to that variable it fails, error is after the script. If I have the (or any) variable included but don’t assign a value as shown it runs correctly and returns the desired data. Is this a bug or am I missing something?
Many Thanks is advance.
Wayne
# ****** SQL Connection Script******
$SQL_Database = "MyDB"
$SQL_Table = "MyTable"
$position = "CPE010"
$Revision = "26"
$zzzzzzzzzzzzzzzzz
#
#
$connCD = New-Object System.Data.SqlClient.SqlConnection
$connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_Database+";Integrated Security=SSPI;"
$cmdTextCD = "Select * FROM " + $SQL_Table + " WHERE [PD_PosNum] = '" + $position + "' AND [Status] = '1' AND [Revision] = '"+ $Revision +"' Order By [PD_PosNum]"
$connCD.Open()
$cmdCD = New-Object System.Data.SqlClient.SqlCommand($cmdTextCD,$connCD)
$rdrCD = $cmdCD.ExecuteReader()
if ($rdrCD.Read()){
$rdrCD["PD_Pos_Auth"].ToString()
}else{
Write-Host "Nothing"
}
$connCD.Close()
# ****** SQL Connection Script Error******
ERROR: New-Object : Cannot find type [System.Data.SqlClient.SqlConnection]: verify that the assembly containing this type is loaded.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:9 char:11
ERROR: + $connCD = New-Object System.Data.SqlClient.SqlConnection
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
ERROR: + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
ERROR:
ERROR: The property 'ConnectionString' cannot be found on this object. Verify that the property exists and can be set.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:10 char:1
ERROR: + $connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_ ...
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : PropertyNotFound
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:12 char:1
ERROR: + $connCD.Open()
ERROR: + ~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader: Connection property has not been initialized."
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:14 char:1
ERROR: + $rdrCD = $cmdCD.ExecuteReader()
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : InvalidOperationException
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:15 char:6
ERROR: + if ($rdrCD.Read()){
ERROR: + ~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:20 char:1
ERROR: + $connCD.Close()
ERROR: + ~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR: