SQL Connection Issue

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
wayne_Avery
Posts: 27
Joined: Mon Sep 27, 2010 12:43 pm

SQL Connection Issue

Post by wayne_Avery » Mon Nov 26, 2018 8: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:

User avatar
Alexander Riedel
Posts: 7107
Joined: Tue May 29, 2007 4:43 pm

Re: SQL Connection Issue

Post by Alexander Riedel » Tue Nov 27, 2018 12:41 am

I am admittedly stumped on that one. No idea. I will move the post to the general PowerShell area to see if anyone else has any input.
Questions:
- Does the script run correctly (without the $zzzzzzzzzz variable) from a powershell console?
- The use of the variable as described creates basically empty output. Replace it with "This is just some output" (with the quotes of course). Does that also work?
- If not, leave the variable in place but assign it some text value above. $zzz = "make it work" and then use $zzz on a line by itself.
Alexander Riedel
SAPIEN Technologies, Inc.

jvierra
Posts: 13953
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: SQL Connection Issue

Post by jvierra » Tue Nov 27, 2018 7:29 am

I cannot reproduce this behavior. It may be because the file is damaged so try creating a new file and try again.

Here is the correct way to create a command in PowerShell. Your method will work but can create issues. You will find that this method eliminates "run-on" exceptions and makes it easier to build and manage ADO scripts.

Code: Select all

# ****** SQL Connection Script******
$SQL_dataSource = '<server instance>'
$SQL_Database = 'MyDB'
$SQL_Table = 'MyTable'
$position = 'CPE010'
$Revision = '26'
$connStr = "Data Source=$SQL_dataSource;Database=$SQL_Database;Integrated Security=SSPI;"
$cmdText = "Select * FROM $SQL_Table WHERE PD_PosNum='$position' AND Status=1 AND Revision='$Revision' Order By [PD_PosNum]"
$dt = New-Object System.Data.DataTable

Try{
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $cmdText
    $rdr = $cmd.ExecuteReader()
    $dt.Load($rdr)
    $conn.Close()
    # test table value
    $dt['PD_Pos_Auth']
}
Catch{
    Throw $_
}

User avatar
wayne_Avery
Posts: 27
Joined: Mon Sep 27, 2010 12:43 pm

Re: SQL Connection Issue

Post by wayne_Avery » Tue Nov 27, 2018 5:28 pm

Alexander Riedel wrote:
Tue Nov 27, 2018 12:41 am
I am admittedly stumped on that one. No idea. I will move the post to the general PowerShell area to see if anyone else has any input.
Questions:
- Does the script run correctly (without the $zzzzzzzzzz variable) from a powershell console?
- The use of the variable as described creates basically empty output. Replace it with "This is just some output" (with the quotes of course). Does that also work?
- If not, leave the variable in place but assign it some text value above. $zzz = "make it work" and then use $zzz on a line by itself.
Hi Alexander, To answer your questions
Yes - The script does run successfully with or without the $zzzzzzz variable in a normal powershell console. This is why I thought there may be a bug in the PrimalScript application.
No - The script does not run successfully in PrimalScript with the variable having a value, only if there is no value.
I have also tried running the PS1 file outside of PrimalScript and it does work with or without the $zzzzzzz variable.
Thanks for looking into this.
Regards
Wayne

jvierra
Posts: 13953
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: SQL Connection Issue

Post by jvierra » Tue Nov 27, 2018 5:43 pm

My example above had one error. Here is the fix.

Code: Select all

# ****** SQL Connection Script******
$SQL_dataSource = '<server instance>'
$SQL_Database = 'MyDB'
$SQL_Table = 'MyTable'
$position = 'CPE010'
$Revision = '26'
$connStr = "Data Source=$SQL_dataSource;Database=$SQL_Database;Integrated Security=SSPI;"
$cmdText = "Select * FROM $SQL_Table WHERE PD_PosNum='$position' AND Status=1 AND Revision='$Revision' Order By [PD_PosNum]"
$dt = New-Object System.Data.DataTable

Try{
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $cmdText
    $rdr = $cmd.ExecuteReader()
    $dt.Load($rdr)
    $conn.Close()
    # test table value
    $dt  | select PD_Pos_Auth
}
Catch{
    Throw $_
}
It is hard to test your code without your database. This code runs on my database with no issues in or out of PrimalScript.

jvierra
Posts: 13953
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: SQL Connection Issue

Post by jvierra » Tue Nov 27, 2018 5:47 pm

I can suggest that you are not running under a correct version of Net Framework or that you have system issues that prevent the code from running correctly. Perhaps there are bad characters in you PS1 file. Open in binary mode to see if there are unprintable characters in the code.

User avatar
wayne_Avery
Posts: 27
Joined: Mon Sep 27, 2010 12:43 pm

Re: SQL Connection Issue

Post by wayne_Avery » Tue Nov 27, 2018 6:07 pm

jvierra wrote:
Tue Nov 27, 2018 5:47 pm
I can suggest that you are not running under a correct version of Net Framework or that you have system issues that prevent the code from running correctly. Perhaps there are bad characters in you PS1 file. Open in binary mode to see if there are unprintable characters in the code.
Using your code and my version of PrimalScript 7.4.118 64bit it still fails if there is no blank variable present.
I have opened the exact same file in Visual Studio Code V1.27.2 and it runs fine with or without the variable. It also runs OK in a PowerShell console or Windows PowerShell ISE. To me it looks like a bug in the application because PrimalScript is the only place it fails on my system.
Thanks for looking at this.
Regards
Wayne

jvierra
Posts: 13953
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: SQL Connection Issue

Post by jvierra » Tue Nov 27, 2018 6:22 pm

I am not saying that you are not getting an error. I am just pointing out that the issue is not the script. I recommend trying it on a different system with a fresh copy of PrimalScript Something in your installation is broken.

I can run your exact code in PrimalScript with no issues.

Locked