public inbox for [email protected]help / color / mirror / Atom feed
ODBC Calling Fill - Unexptected closed connection after 2 hours 3+ messages / 2 participants [nested] [flat]
* ODBC Calling Fill - Unexptected closed connection after 2 hours @ 2021-05-10 21:14 David Parenteau <[email protected]> 2021-05-12 00:35 ` Re: ODBC Calling Fill - Unexptected closed connection after 2 hours Inoue,Hiroshi <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David Parenteau @ 2021-05-10 21:14 UTC (permalink / raw) To: [email protected] <[email protected]> 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 use driver PostgresSQL Unicode(x64) version 13.00.00.00 I have a query that is executed through an ODBC connection in a Powershell 5.1 script. I use the Fill() method to retreive about 3500 records daily. When the script works, it takes 2-5 minutes to execute and retrieve data. Probleme is that the script "fails" half of the time approx. When this occurs, the powershell script does stop only after 2h and 30 seconds. We double checked the postgres logs and when this occurs, we see that the query successfully completed within 6 minutes always. I don't know what to look for. any Idea? Below is the error got: 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. Not too familiar with postgreSQL. Thanks! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ODBC Calling Fill - Unexptected closed connection after 2 hours 2021-05-10 21:14 ODBC Calling Fill - Unexptected closed connection after 2 hours David Parenteau <[email protected]> @ 2021-05-12 00:35 ` Inoue,Hiroshi <[email protected]> 2021-05-12 13:35 ` Re: ODBC Calling Fill - Unexptected closed connection after 2 hours David Parenteau <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Inoue,Hiroshi @ 2021-05-12 00:35 UTC (permalink / raw) To: David Parenteau <[email protected]>; +Cc: [email protected] <[email protected]> Hi David, How about increasing the CommandTimeout(default 30sec)? $cmd.CommandTimeout = 300, for example. regards, Hiroshi Inoue 2021年5月11日(火) 6:14 David Parenteau <[email protected]>: > 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 use driver PostgresSQL Unicode(x64) version 13.00.00.00 > > > I have a query that is executed through an ODBC connection in a Powershell > 5.1 script. I use the Fill() method to retreive about 3500 records daily. > When the script works, it takes 2-5 minutes to execute and retrieve data. > Probleme is that the script "fails" half of the time approx. When this > occurs, the powershell script does stop only after 2h and 30 seconds. > We double checked the postgres logs and when this occurs, we see that the > query successfully completed within 6 minutes always. I don't know what to > look for. any Idea? > > Below is the error got: > > 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. > > Not too familiar with postgreSQL. > > Thanks! > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ODBC Calling Fill - Unexptected closed connection after 2 hours 2021-05-10 21:14 ODBC Calling Fill - Unexptected closed connection after 2 hours David Parenteau <[email protected]> 2021-05-12 00:35 ` Re: ODBC Calling Fill - Unexptected closed connection after 2 hours Inoue,Hiroshi <[email protected]> @ 2021-05-12 13:35 ` David Parenteau <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: David Parenteau @ 2021-05-12 13:35 UTC (permalink / raw) To: Inoue,Hiroshi <[email protected]>; +Cc: [email protected] <[email protected]> Actually, I already have these timeout limits in my code for a while. My query takes less than 10 minutes for all execution in the past. $connection.ConnectionTimeout = 3600 #10 minutes $cmd.CommandTimeout=3600; #10 minutes The Keepalive 120 on the ODBC might have worked since the today execution run first time but in less than 5 minutes. Let's see what arrives then if longer than 5 minutes (might be an other default timeout limit triggering my 2hrs problem randomly...) Thanks, David Parenteau. ________________________________ From: Inoue,Hiroshi <[email protected]> Sent: Tuesday, May 11, 2021 8:35 PM To: David Parenteau <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: ODBC Calling Fill - Unexptected closed connection after 2 hours Hi David, How about increasing the CommandTimeout(default 30sec)? $cmd.CommandTimeout = 300, for example. regards, Hiroshi Inoue 2021年5月11日(火) 6:14 David Parenteau <[email protected]<mailto:[email protected]>>: 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 use driver PostgresSQL Unicode(x64) version 13.00.00.00 I have a query that is executed through an ODBC connection in a Powershell 5.1 script. I use the Fill() method to retreive about 3500 records daily. When the script works, it takes 2-5 minutes to execute and retrieve data. Probleme is that the script "fails" half of the time approx. When this occurs, the powershell script does stop only after 2h and 30 seconds. We double checked the postgres logs and when this occurs, we see that the query successfully completed within 6 minutes always. I don't know what to look for. any Idea? Below is the error got: 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. Not too familiar with postgreSQL. Thanks! ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-05-12 13:35 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-05-10 21:14 ODBC Calling Fill - Unexptected closed connection after 2 hours David Parenteau <[email protected]> 2021-05-12 00:35 ` Inoue,Hiroshi <[email protected]> 2021-05-12 13:35 ` David Parenteau <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox