SQL Connection Issue

Ask your 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.
This topic is 5 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
wayne_Avery
Posts: 30
Last visit: Tue Feb 01, 2022 2:25 pm

SQL Connection Issue

Post by wayne_Avery »

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: 8473
Last visit: Tue Mar 19, 2024 1:15 am
Answers: 19
Been upvoted: 37 times

Re: SQL Connection Issue

Post by Alexander Riedel »

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: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: SQL Connection Issue

Post by jvierra »

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: 30
Last visit: Tue Feb 01, 2022 2:25 pm

Re: SQL Connection Issue

Post by wayne_Avery »

Alexander Riedel wrote: Mon Nov 26, 2018 11:41 pm 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: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: SQL Connection Issue

Post by jvierra »

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: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: SQL Connection Issue

Post by jvierra »

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: 30
Last visit: Tue Feb 01, 2022 2:25 pm

Re: SQL Connection Issue

Post by wayne_Avery »

jvierra wrote: Tue Nov 27, 2018 4: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: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: SQL Connection Issue

Post by jvierra »

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.
This topic is 5 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked