Please help me get passed this error: "executereader" error

Question

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 27-Mar-21 18:16pm

pFerden

Updated 27-Mar-21 22:00pm

Richard MacCutchan

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2021-03-27T19:50:00

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!