0

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!

sridharnetha
  • 2,104
  • 8
  • 35
  • 69
  • 2
    what makes you think the issue is `Out-File` ? Are you sure your query returns the expected results ? – Santiago Squarzon Aug 20 '22 at 14:22
  • `out-File` is writing as expected for ~90 iteration, after that writing zero bytes. I am sure my query is returning the expected result. – sridharnetha Aug 20 '22 at 14:26
  • what happens if you change `Out-File -Encoding ascii` for `Set-Content -Encoding ascii` ? – Santiago Squarzon Aug 20 '22 at 14:27
  • Is `Set-Content` action similar `Out-File`? I used `Out-File` for write the result in .json file – sridharnetha Aug 20 '22 at 14:30
  • Yes, the exact same. – Santiago Squarzon Aug 20 '22 at 14:31
  • The both `Set-Content`, `Out-File` are working fine when I ran the script manually in PS. It seems the issue occurring from`C#` PowerShell library `System.Management.Automation`. – sridharnetha Aug 20 '22 at 14:42
  • 1
    I think you should share the full contents of your code to get the proper context on the issue. `Out-File` is most likely __not the issue__ here. – Santiago Squarzon Aug 20 '22 at 14:46
  • Most likely the issue is in `ConvertTo-Json`, I don't think it processes line by line and needs to receive all input before processing it (90gb of input, making the powershell instance to crash). Though this is based on assumptions, it would be easier to troubleshoot the issue if you try the code using 100% powershell instead of a pwsh instance from c#. – Santiago Squarzon Aug 20 '22 at 14:53
  • 2
    Select Query is hitting the famous 30 seconds timeout of the SMO object. I used the parameter `-Querytimeout 0` to fix zero byte issue. – sridharnetha Aug 20 '22 at 15:13
  • You should post this a self-answer then so others with a similar issue in the future can see it – Santiago Squarzon Aug 20 '22 at 15:16
  • 1
    Sure:-) I will post as self-answer. Thank you! – sridharnetha Aug 20 '22 at 15:18

1 Answers1

0

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
sridharnetha
  • 2,104
  • 8
  • 35
  • 69
  • Nice! I suggest to add line breaks and indentation after each `|` to make the code easier to read. – zett42 Aug 20 '22 at 16:37