powershell out-file writing zero bytes

Question

I am trying to export the SQL Server query results chunks in to .json file. Here I have large table around the 90GB I need to automate the script using the pagination approach.

The following Powershell script is generating within the For loop in c# application and working fine, but after some time in a random point the Out-File is not writing and giving zero bytes file. May be you can help!

Invoke-Sqlcmd -Query "Select NAME,FULL_NAME,DOB,GENDER,ADDRESS from [222db] ORDER BY NAME OFFSET 24300000 ROWS FETCH NEXT 300000 ROWS ONLY" 
-ServerInstance "localhost\MSSQLSERVER2019" -U admin -P xxx -Database "222dbs" | Select-Object NAME,FULL_NAME,DOB,GENDER,ADDRESS | ConvertTo-Json -Compress -Depth 100 | Out-File -Encoding ASCII C:\gen\\file_81.json

Appreciated your help!

Answer 1

Posting this as a self answer to my question so others with a similar issue in the feature can see it.

I referred in Error Log

Invoke-Sqlcmd : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Solution

The Select Query is hitting the famous 30 seconds timeout of the SMO object. I added parameter -Querytimeout X where X is time in seconds. 0 make infinite timeout we can use an integer value between 1 and 65535.

Invoke-Sqlcmd -Query "Select NAME,FULL_NAME,DOB,GENDER,ADDRESS from [222db] ORDER BY NAME OFFSET 24300000 ROWS FETCH NEXT 300000 ROWS ONLY" 
-ServerInstance "localhost\MSSQLSERVER2019" -Querytimeout 0 -U admin -P xxx -Database "222dbs" | Select-Object NAME,FULL_NAME,DOB,GENDER,ADDRESS | ConvertTo-Json -Compress -Depth 100 | Out-File -Encoding ASCII C:\gen\\file_81.json