Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near '='."

Question

I am writing a powershell code with an SQL query to get information from one of our Databases and then put that information into a variable for further use. My entire code works, and does exactly what i want it to do.

But somewhere in this snippet of the code two errors are produced that I can't figure out why happen if the script is actually doing what it's supposed to do.

$Server = "######"
$Database = "######"
$UserSqlQuery = "SELECT [UdstyrsId]
      ,[Model]
      ,[Serienr]
      ,[Udlevdato]
      ,[Repnr]
      ,[Notat]
  FROM [IT-Support].[dbo].[Udstyrsoplysninger] where aflevdato is null and repnr = $repnr"

    function ExecuteSqlQuery($Server, $Database, $UserSQLQuery) 
    {
        $Datatable = New-Object System.Data.DataTable

        $Connection = New-Object System.Data.SQLClient.SQLConnection
        $Connection.ConnectionString = "server='$Server';database='$Database';integrated security=true;"
        $Connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.Connection = $Connection
        $Command.CommandText = $UserSQLQuery
        $Reader = $Command.ExecuteReader();
        $Datatable.Load($Reader);
        $Connection.Close()

        return $Datatable
    }    
        $ResultsDataTable = New-Object System.Data.DataTable
        $ResultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery

Error code:

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax        near '='."
At C:\Users\adm-#######\Desktop\UserResignation.ps1:70 char:13
+             $Reader = $Command.ExecuteReader();
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Exception calling "Load" with "1" argument(s): "Value cannot be null.
Parameter name: dataReader"
At C:\Users\adm-#######\Desktop\UserResignation.ps1:71 char:13
+             $Datatable.Load($Reader);
+             ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

Line 70 and 71 is these lines in the script:

$Reader = $Command.ExecuteReader();
$Datatable.Load($Reader);

Did you set $repnr ? Seems like your $UserSqlQuery has syntax error because your where condition missing value. — Kirill Pashkov
– Kirill Pashkov, Commented Oct 12, 2018 at 9:33
@KirillPashkov Hi, yes the $repnr is set earlier in the script through a $repnr = (Get-ADuser -identity JohnDoe -properties * | select Office).Office — Simon Bøgelund
– Simon Bøgelund, Commented Oct 12, 2018 at 9:44
what is the value in the $Reader in the $Datatable.Load($Reader)? Btw. you can omit the ; at the end with powershell. — tukan
– tukan, Commented Oct 12, 2018 at 9:49
That strikes me as odd, cause just a few lines above the code I posted I save repnr in $RepNr = (Get-ADUser -Identity $User -Properties * | Select-Object Office).office. So it should be populated. — Simon Bøgelund
– Simon Bøgelund, Commented Oct 12, 2018 at 10:06

Kirill Pashkov · Accepted Answer · 2018-10-12 09:39:19Z

3

You forgot to declare $repnr for query's where condition and because of that you get syntax error where aflevdato is null and repnr = .

$repnr = 123
$UserSqlQuery = "SELECT [UdstyrsId]
      ,[Model]
      ,[Serienr]
      ,[Udlevdato]
      ,[Repnr]
      ,[Notat]
  FROM [IT-Support].[dbo].[Udstyrsoplysninger] where aflevdato is null and repnr = $repnr"

answered Oct 12, 2018 at 9:39

Kirill Pashkov

3,2461 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mötz Over a year ago

Good spotting Kirill!

Simon Bøgelund Over a year ago

This turned out to be the reason actually. Earlier on in my script I have another variable: $CN = Get-ADUser -Properties accountexpirationdate -Filter { (Enabled -EQ $True -and AccountExpirationDate -GE 0) } | Where-Object { ($_.distinguishedname -notlike 'Eksterne') } | Select-Object -ExpandProperty DistinguishedName This returns all the users in our AD with an expiration date. They are then put through a foreach loop, where the $repnr variable is set.

Simon Bøgelund Over a year ago

The $repnr is based on the value in the Office Property. The first line caught a user that I thought I had exempted and that specific users Office property wasn't populated resulting in the $repnr to be null in the SQL query later on in the script.

Collectives™ on Stack Overflow

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near '='."

Error code:

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error code:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related