Thread: Issue related to command timeout after 30s

Hi Carlos,

On 2020/05/18 23:45, Carlos Augusto wrote:
> Hi Guys,
>
> I'm facing a issue related to command timeout when using psqlodbc.
>
> When i tried to copy data from a remote postgresql database to a sql 
> server database, i'm get the following error:
>
> Do you know how to fix this?
>
> Error 0xc0047062: Data Flow Task 1: System.Data.Odbc.OdbcException 
> (0x80131937): ERROR [57014] ERROR: cancelling command because of the 
> command timeout;
> Error while executing the query
>    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, 
> RetCode retcode)

Could you please try to set
     System.Data.Odbc.OdbcCommand.CommandTimeout=0
?

regards,
Hiroshi Inoue

>    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior 
> behavior, String method, Boolean needReader, Object[] methodArguments, 
> SQL_API odbcApiMethod)
>    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior 
> behavior, String method, Boolean needReader)
>    at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
>    at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior 
> behavior)
>    at 
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior 
> behavior)
>    at 
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
>    at 
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 
> wrapper) (SQL Server Import and Export Wizard)
>
> Error 0xc004701a: Data Flow Task 1: Source 1 - alarme_ciclo failed the 
> pre-execute phase and returned error code 0x80131937.
>  (SQL Server Import and Export Wizard)

Hi Hiroshi,

Thanks for the reply.

I have tried to import the data from PostgreSQL to SQL Server following the steps that this page:

https://docs.microsoft.com/pt-br/sql/integration-services/import-export-data/connect-to-a-postgresql-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15

I tried to configure this property, CommandTimeout, in the string connection, but it did not work.

Do you know how to set this property , using CommandTimeou as parameter from the string connection?

Is there any place on the SQL Server that we can set this property?

Thanks

On Tue, May 19, 2020 at 2:02 AM Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:

Hi Carlos,

On 2020/05/18 23:45, Carlos Augusto wrote:
> Hi Guys,
>
> I'm facing a issue related to command timeout when using psqlodbc.
>
> When i tried to copy data from a remote postgresql database to a sql
> server database, i'm get the following error:
>
> Do you know how to fix this?
>
> Error 0xc0047062: Data Flow Task 1: System.Data.Odbc.OdbcException
> (0x80131937): ERROR [57014] ERROR: cancelling command because of the
> command timeout;
> Error while executing the query
>    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)

Could you please try to set
     System.Data.Odbc.OdbcCommand.CommandTimeout=0
?

regards,
Hiroshi Inoue

>    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
> behavior, String method, Boolean needReader, Object[] methodArguments,
> SQL_API odbcApiMethod)
>    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
> behavior, String method, Boolean needReader)
>    at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
>    at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
>    at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
> behavior)
>    at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
>    at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100
> wrapper) (SQL Server Import and Export Wizard)
>
> Error 0xc004701a: Data Flow Task 1: Source 1 - alarme_ciclo failed the
> pre-execute phase and returned error code 0x80131937.
>  (SQL Server Import and Export Wizard)