Powershell Invoke-SQLcmd Query argument inconsistent?

Question

I do apologies if this has been asked before. At the time I wrote this I was unable to find this behavior addressed on this site.

When using Invoke-SQLcmd in powershell I get inconsistencies. I receive SQL connection arguments at the scripts invocation from the user. Inside of the this single test script I have two queries. Both invocations use the same variables.

This works and returns the name of the database.

$ConnectQuery = "SELECT DBName = DB_NAME()"

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDBName -Username $SQLUsername -Password $SQLPassword -Query $ConnectQuery

This does not work but does not give me an error but returns nothing. I checked the variables and they continue to have the same content as that this query immediately follows the previous.

$ConnectQuery01 = "SELECT * FROM [dbo].[test.csv]"
    
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDBName -Username $SQLUsername -Password $SQLPassword -Query $ConnectQuery01

Additional Notes:

As I understand Invoke-Sqlcmd it is a module that provides Powershell access to the same features as the command shell command "sqlcmd". So I tried this using "sqlcmd". In both cases, these commands worked and return the expected data The databases name, and the table content which is a single test row.

In SQLcmd executable:

These both work and return the expected data.

sqlcmd -S <MyDesktopName> -d test -U "<TestAccountName>" -P "<TestPassword>" -q "SELECT DBName = DB_NAME()"

sqlcmd -S <MyDesktopName> -d test -U "<TestAccountName>" -P "<TestPassword>" -q "SELECT * FROM [dbo].[test.csv]"

In SSMS:

These both work and return the expected data.

SELECT DBName = DB_NAME()

SELECT * FROM [dbo].[test.csv]

Just in case someone asks

$PSVersionTable

Name                           Value                                                                                                                         
----                           -----                                                                                                                         
PSVersion                      5.1.18362.1171                                                                                                                
PSEdition                      Desktop                                                                                                                       
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                       
BuildVersion                   10.0.18362.1171                                                                                                               
CLRVersion                     4.0.30319.42000                                                                                                               
WSManStackVersion              3.0                                                                                                                           
PSRemotingProtocolVersion      2.3    
SerializationVersion           1.1.0.1   

Answer 1

Ok I think I have found a way to mitigate this behavior.

If I do this:

$ConnectQuery01 = "SELECT * FROM [dbo].[test.csv]"

$MyResult = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDBName -Username $SQLUsername -Password $SQLPassword -Query $ConnectQuery01

Then I do this:

Write-Host $MyResult. | Format-Table

I get the table results that I expect. This is significantly different behavior from the first evocation of "Invoke-sqlcmd" So, I believe that there are different return behaviors depending on the expected return. I could be wrong here. Please let me know if you have a better explanation.

Thank you for you attention.