I’m using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
I have a daily scheduled job that calls a PowerShell script. This ps1 script do a big SELECT on an ODBC connection to postgresql server above. The query itself completes between 3 and 6 minutes and return around 3500 records. We looked on the postgres logs for this.
50% of the time I can say, the job takes 2:00:30 to run and failed. However, in the postgres logs, we can see that the query completed in less than 6 minutes. Also, when it fails, it gives this:
Executed as user: NT Service\SQLSERVERAGENT. A job step received an error at line 94 in a PowerShell script. The corresponding line is '(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Fill" with "1" argument(s): "The connection has been disabled." '. Process Exit Code -1.
I assume the query is fine. All my other ps1 script with smaller queries and ODBC Fill() seem to work fine always, event those taking 2-3 minutes.
I don’t know what I should check or what could be related to this.
Any idea what we should look at?
Thanks a lot
David Parenteau
