0

I am using ODBC driver to transfer data from Postgres to SQL Server https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-postgresql-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16

The issue is that some tables are throwing,

Error 0xc0047062: Data Flow Task 1: System.Data.Odbc.OdbcException (0x80131937): ERROR [57014] ERROR: canceling statement due to statement timeout; Error while executing the query at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) 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)

I am getting this error after 30 seconds. I have increased statement_timeout in postgres and set Command Timeout in the connection string but no help. Is there is a way to increase OdbcCommand.CommandTimeout when using SQL Server Import feature?

Improve this question
4
  • Removed my other answer as it was wrong. Does this one work? "Query Timeout=60"
    – Peter
    Commented Jun 13, 2023 at 13:53
  • Look in the PostgreSQL server log file to see it reports the error differently or with more info.
    – jjanes
    Commented Jun 13, 2023 at 23:02
  • @Peter it worked thanks a lot
    – Imran Qadir Baksh - Baloch
    Commented Jun 14, 2023 at 4:02
  • Great, Í've changed the answer to the right one.
    – Peter
    Commented Jun 14, 2023 at 5:53

1 Answer 1

Reset to default
1

Can you change the Command Timeout to Query Timeout=

Improve this answer
1
  • Tried both and not working. BTW, its OdbcConnection and OdbcCommand.
    – Imran Qadir Baksh - Baloch
    Commented Jun 13, 2023 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.