Hi ,
We are loadind data using odbc connector stage to load data to SQL server in multiple jobs however very frequenlty we are receiving below error and which leads failing the load cycle.
Can some one help to fix this issue.I also contacted my DBA team but they are not very sure about this error.
very well appreciated your help!
ODBC function "SQLConnect" reported: SQLSTATE = HYT00: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Native Wire Protocol driver]Timeout expired. (CC_OdbcConnection::connect, file CC_OdbcConnection.cpp, line 550)
Thanks,
Basu.
SQL Server connetion time out error
Moderators: chulett, rschirm, roy
Well, we can't fix the issue for you. HA
We can guide you on how to find the root cause and have you fix it.
Understand that all connections to the data sources and targets are established FIRST, then data is sent for the extract, numbers crunched in your job... then data sent to your target ODBC connection to LOAD the data.
That means that your LOAD target had a connection open from the start of the job. But it might not see any data for a long time. It depends upon your job design and the quantity of data being manipulated at the time.
If your job took an hour to provide row #1 to your output ODBC stage... it may have triggered the TIMEOUT on the odbc connection. Talk to the DBA to see if they have a stale connection timeout setting.
Optimize your job to take into account that long wait time. Should your job be simply that big or slow... you might want to process the data once. Drop it to a file (aka restart point), then job #2 loads the data. I'm typically not a big fan of dropping data to disk in order to have #2 job to load it. But there are instances and environments that would benefit from that approach. Only you know if yours fits that statement.
We can guide you on how to find the root cause and have you fix it.
Understand that all connections to the data sources and targets are established FIRST, then data is sent for the extract, numbers crunched in your job... then data sent to your target ODBC connection to LOAD the data.
That means that your LOAD target had a connection open from the start of the job. But it might not see any data for a long time. It depends upon your job design and the quantity of data being manipulated at the time.
If your job took an hour to provide row #1 to your output ODBC stage... it may have triggered the TIMEOUT on the odbc connection. Talk to the DBA to see if they have a stale connection timeout setting.
Optimize your job to take into account that long wait time. Should your job be simply that big or slow... you might want to process the data once. Drop it to a file (aka restart point), then job #2 loads the data. I'm typically not a big fan of dropping data to disk in order to have #2 job to load it. But there are instances and environments that would benefit from that approach. Only you know if yours fits that statement.