0

Any help is always appreciated! I have a Powershell script that pulls lists of usernames and assets from a SQL Server data warehouse using Invoke-Sqlcmd.

The script pulls around 20 of these lists, but I'm running into a weird issue with one specific pull: The script is returning incomplete results... around 20% of what I see when running the same SQL query from SQL Server Management Studio.

I don't think I'm hitting any kind of limit. Other queries from the same SQL warehouse in other parts of my script pull close to 10,000 rows without issue.

In this case, I'm receiving around 170 rows when using Powershell, but close to 700 when running the same SQL query from SSMS.

I've tried the following: run Invoke-Sqlcmd with the query embedded in the script, like this, results in 172 rows:

$intern_pre = Invoke-Sqlcmd -Query "
SELECT lower(useraccount) as username
 FROM ---Redacted---
WHERE [field] = 1 AND [Title] IN ('Job title1',  
'Job title2'
..... and another 60 or so job titles.......
))
UNION
((SELECT [Name]
FROM ---Redacted---
WHERE ([isElevated] = 1 AND ([Name] like 'Name1%' OR 
[Name] like 'Name2%' OR
[Name] like 'Name3%')) AND [Name] != 'AnotherName' AND [Owner] IN 
(SELECT lower(useraccount) as username
 FROM ---AnotherRedactedTable---
WHERE [field] = 1 AND [JobTitle] IN ('Job title1',  
'Job title2'
..... and another 60 or so job titles.......))))
" -ServerInstance $sql_server

Displayed the array in terminal.... so I could be sure it's not an issue with cleanup or something else done by Powershell:

$intern_pre

Viewed the results in PowerShell terminal to be sure the CSV export didn't mess things up. Missing rows.

Exported the results to a CSV file and confirmed I'm missing rows.

Ran the script in ISE and VS Code.

Removed all cmdlets/code in the Powershell script that came after the SQL pull, to ensure Powershell isn't changing anything.

Removed about half of the job titles I was searching for resulted in 124 results in Powershell, but 381 in SSMS.

Running Invoke-Sqlcmd using a script input file also results in too few results:

Invoke-Sqlcmd -InputFile $sql_script -ServerInstance $sql_server
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gunnys
  • 15
  • 2
  • 1
    Just to make sure: change the query to return `count()` of the results, and see if the Powershell version gets that right. If it does, then it's about receiving the results. If it doesn't, then there's something with the query. – vonPryz Jun 29 '23 at 19:57
  • 1
    Could be row-level security (or an in-house version of it). Are you using **identical** authentication credentials in SSMS and PowerShell? – AlwaysLearning Jun 29 '23 at 21:49
  • You can check the account/group when you run from powershell. Using SSMS the log files can be found in the explorer under Management. The log file will show the account/group that logged in when you ran from PS. – jdweng Jun 29 '23 at 23:24
  • Should you provide a database name to Invoke-SqlCmd? -Database ThisDatabase. Else, it will use the default database which is most probably master, – PollusB Jul 05 '23 at 16:03

1 Answers1

0

It turns out there was nothing wrong with my PowerShell script or SQL query.

The cause of the issue: The listener address of the data warehouse I was querying was changed, but the old address remained active.... but giving less results and no errors (that I could find).

Updated the listener address and all is well!

gunnys
  • 15
  • 2