Click here to Skip to main content
15,030,062 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I am so happy I found this forum! I am new to Powershell and SQL.
Please help me get passed this error: calling "ExecuteReader" with "0" argument(s): "Ex
ecuteReader: CommandText property has not been initialized

This is in the script:

$SQLQuery = $Query # <- When I enter the $Query in SQL, it works.

PowerShell
$SQLResult = SQL-GETIT -SQLServer $SQLServer -ADCredential $ADCredential -EncryptionFileName $EncryptionFileName -ConvertCredToPassword $ConvertCredToPassword `
-SQLDatabase $SQLDatabase -SQLUserName $SQLUserName -WorkingFolder $WorkingFolder -LogFile $LogFile <- all variables contain data
             
  Write-Host "***ExceptionMessage:" $error[0].Exception.Message
  Write-Host "***Target Object: " $error[0].TargetObject
  Write-Host "***Category Info: " $error[0].CategoryInfo
  Write-Host "***ErrorID: " $Error[0].FullyQualifiedErrorId

# This is the Function:

Function SQL-GETIT
{
    param
    (
        #Default parameters if none are sent
        [String]$WorkingFolder,
        [String]$LogFile,
        [String]$SQLServer,
        [String]$SQLDatabase,
        [String]$SQLQuery,
        [String]$SQLUserName,
        [String]$ADCredential,
        [String]$EncryptionFileName,
        [String]$ConvertCredToPassword
    )
Write-Log "** Testing SQL-GETIT query **" -Path $LogFile
    try
    {
      Write-Log "Starting SQL query $SQLQuery." -Path $LogFile
      $TotalElapsed = [System.Diagnostics.Stopwatch]::StartNew() 

      $SQLConn = Connect-SQL -SQLServer $SQLServer -ADCredential $ADCredential -EncryptionFileName $EncryptionFileName -ConvertCredToPassword $ConvertCredToPassword `
-SQLDatabase $SQLDatabase -SQLUserName $SQLUserName -WorkingFolder $WorkingFolder -LogFile $LogFile 
      
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $SQLConn
      $Command.CommandText = $SQLQuery
    
      $Result = $Command.ExecuteReader()
      $Table = new-object System.Data.DataTable
      $Table.Load($Result)
  
      return $Table
            
      Write-Log "SQL statement complete in: $($TotalElapsed.Elapsed.ToString())." -Path $LogFile
       # Clean Up
      $reader.Close()
         }
    catch 
    {
        $ErrorMessage = ("ERROR: Could not process query")
        Write-Log $ErrorMessage -Path $LogFile
        $SQLConn = 0
        Return "0"
    }
}


Execution Results:
VERBOSE: ** Testing SQL-GETIT query **
VERBOSE: Starting SQL query .
VERBOSE: Opened connection for SQL Server: oagcs4wvdwsql02,59999, DB: MISSION_CO
NTROL
***ExceptionMessage: Exception calling "ExecuteReader" with "0" argument(s): "Ex
ecuteReader: CommandText property has not been initialized"
***Target Object:  
***Category Info:  NotSpecified: (:) [], MethodInvocationException
***ErrorID:  InvalidOperationException


What I have tried:

I've tried:
1. searching the internet for hours for a solution. There are sooo many examples of other code in other languages.
2. debugging - running section of the code. The Query I am passing looks fine and works fine in MS SQL Server Management Studio:
SELECT SERVERPROPERTY('ProductLevel') as SP_installed,SERVERPROPERTY('ProductVersion') as Version
3. Access Microsoft and read the documentation... but, could not find anything helpful regarding how to resolve this error.
Posted
Updated 27-Mar-21 22:00pm
v2

1 solution

You don't pass your query string o SQL-GETIT:
$SQLResult = SQL-GETIT -SQLServer $SQLServer -ADCredential $ADCredential -EncryptionFileName $EncryptionFileName -ConvertCredToPassword $ConvertCredToPassword `
-SQLDatabase $SQLDatabase -SQLUserName $SQLUserName -WorkingFolder $WorkingFolder -LogFile $LogFile

And without a query, SQL has no idea what to do!

But ... don't do it like that. Most SQL needs information passed to it:
SQL
SELECT ID, Name FROM Customers WHERE Balanace > 1000

When you make a function to do it for you, you have to pass the variable bit - 1000 in this case as part of the string, that means you have to concatenate strings.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you? - OriginalGriff

It is possible to do this with a function, but it's even messier than what you have at the moment!
   
Comments
pFerden 29-Mar-21 9:48am
   
THANK YOU! very much for all info ! It is resolved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900