1

I'm getting the following exception:

New-Object : Cannot find an overload for "SqlCommand" and the argument count: "2". At C:\Users\aaaaaaaaaaaaaaaaaaps.ARMTemplate\CoreDbScripts\RunSqlScripts.ps1:128 char:19

  • ... $comm = New-Object System.Data.SqlClient.SqlCommand( $file,$conn) ...
  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidOperation: (:) [New-Object], MethodException
    • FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Here's my code:

Using-Object ($conn = New-Object System.Data.SqlClient.SqlConnection $connString) {
    $conn.Open();
    
    Using-Object ($tran = $conn.BeginTransaction) {
        foreach ($file in $sqlFiles)
        {
          Write-Host "file: " $file.Name
          $comm = New-Object System.Data.SqlClient.SqlCommand($file, $conn);
          $comm.Transaction = $tran
        [void]$comm.ExecuteNonQuery()
        }
        $tran.Commit();
    };
};

What am I doing wrong? How do we execute the SqlCommand?

Improve this question
1
  • As an aside: It's best to pseudo method syntax: Instead of New-Object SomeType(arg1, ...), use New-Object SomeType [-ArgumentList] arg1, ... - PowerShell cmdlets, scripts and functions are invoked like shell commands, not like methods. That is, no parentheses around the argument list, and whitespace-separated arguments (, constructs an array as a single argument, as needed for -ArgumentList). However, method syntax is required if you use the PSv5+ [SomeType]::new() constructor-call method. See this answer Commented Oct 29, 2021 at 4:33

2 Answers 2

Reset to default
3

The error message implies that at least one of the constructor arguments you're passing is of the wrong data type (given that it isn't the number of arguments that is the problem).

The two-argument constructor of class System.Data.SqlClient.SqlCommand you're trying to call has the following signature:

public SqlCommand (string cmdText, System.Data.SqlClient.SqlConnection connection);

By contrast, your code suggests that you're passing an instance of type System.IO.FileInfo as the first argument, i.e. an object describing a file as a file-system entry. Instead, pass the file's content, which you can obtain in full via Get-Content -Raw:

$comm = New-Object System.Data.SqlClient.SqlCommand ($file | Get-Content -Raw), $conn

Or, using the alternative PSv5+ syntax for invoking constructors, via the intrinsic static ::new() method PowerShell makes available on types:

$comm = [System.Data.SqlClient.SqlCommand]::new(($file | Get-Content -Raw), $conn)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+ ... ata.SqlClient.SqlCommand (Get-Content -Raw $filesconcatenated), $conn + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: ( /****** Object:String) [Get-Content], DriveNotFoundException + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.GetContentCommand
As stated, the assumption is that $file is a single FileInfo instance (and that it refers to an existing file containing SQL commands). I don't know what $filesconcatenated is, but it doesn't sound like a single FileInfo instance.
@AlexGordon, however, I just realized that in Windows PowerShell (no longer in PowerShell Core) Get-Content -Raw $file can situationally fail (depending on how the FileInfo instance was obtained, it may stringify to the mere file name rather than the full path), so I've updated the answer to use the robust $file | Get-Content -Raw
P.S., @AlexGordon: If you do want to merge the content of multiple files and pass that as a single string, use (($sqlFiles | Get-Content -Raw) -join "`n")
@AlexGordon, looking at your previous questions suggests that $filesconcatenated may already be the merged content from multiple files, in which case you don't need Get-Content and can pass the value as-is - assuming that it is a single string. In essence, testing the first argument with -is [string] must return $true, and -is [System.Data.SqlClient.SqlConnection] must be $true for the second one in order for the constructor call to succeed. Let's try to bring closure to this and some of your previous questions: Please accept answers or provide feedback.
0

I see quite a few examples here suggesting Using-Object but that's custom function (see some of the other SO pages) to simulate the using() block in C#. Powershell doesn't have that. You simply need to declare the SqlConnection and SqlCommand objects as an objects. Don't forget to dispose of them. I strongly recommend a try-catch-finally block, and be sure to dispose of the SqlConnection object in the finally block.

I think you're looking for this. (Warning: I didn't run test this, but it's very close):

try
{
  $conn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
  $conn.Open();
  $tran = $conn.BeginTransaction()
  foreach ($file in $sqlFiles)
  {
    Write-Host "file: " $file.Name
    $comm = New-Object System.Data.SqlClient.SqlCommand($file, $conn);
    $comm.Transaction = $tran
    [void]$comm.ExecuteNonQuery()
    $comm.Dispose()
  }
  $tran.Commit()
}
catch
{
  # Handle your error here.
}
finally
{
  # Make sure the SQL connection closes.
  $conn.Dispose()
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.