Click here to Skip to main content
15,435,847 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.

$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
        #Default parameters if none are sent
Write-Log "** Testing SQL-GETIT query **" -Path $LogFile
      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
      return $Table
      Write-Log "SQL statement complete in: $($TotalElapsed.Elapsed.ToString())." -Path $LogFile
       # Clean Up
        $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
***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.
Updated 27-Mar-21 22:00pm

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:
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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
A perfectly valid "delete the table" command
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!
Share this answer
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