DBActivateMap and DBPutRecord Failed due to timeout error

[RKGupta]

My database application is failing and giving timeout error. Attached file has to snapshot of error messages and Database status health. Due to timeout error i also tried to increase 'Query Wait' time of SQL Server 2000 to 6000 sec. But it is still giving me the same error. Presently database and this appliaction is running on the same machine.
Awaiting for your help.

[RKGupta]

Additional Information:
Presently database has 55 Millions records into it.
following the table structures:
Table Structures
Master Table- DateID
ID Bigint PK
DateTime Smalldatetime

Detail Table- Ch_Details
Ch_No int
ID bigint FK
Value numeric(5,2)

[Jeremy_L]

Hi,

This may be caused by just the size of the database. Because the error is an error generated by the database provider and not necessarily CVI, this is more of a provider (ole db or odbc) issue. One thing, however, that I have seen help is changing the CommandTimeout property to zero (0), and making it wait indefinitely. You can do this using DBSetConnectionAttribute and select ATTR_DB_CONN_COMMAND_TIMEOUT.

Jeremy L.
National Instruments

[RKGupta]

Hi Jeremy

Thanks for your help. As you suspected i wrote one test application in VB and tried Adding the new record in the same table and with same DSN (ODBC). I was able to insert the and retrieve the records successfully. I have kept default setting of ADO data control and data grid. CommandTimeOut for ADODC was by default set to 30 Sec. Please advise me further. If you need any information from my side please let me know.
Thanks again for your support.
Regards
Rakesh

[RKGupta]

Hi Jeremy

As you suggested i set CommandTimeout property to Zero. Still Timeout Error exists. I am encoutering the error at DBActivateMap statement. I am attaching the part of the code at end of the message.
Awaiting for reply and help...
Regards
Rakesh

//Here is the code for your reference...
hdbc = DBNewConnection();
resCode = DBSetConnectionAttribute (hdbc,ATTR_DB_CONN_CONNECTION_STRING, "DSN=SingleTable;UserID=sa;Password=");
resCode = DBSetConnectionAttribute (hdbc,ATTR_DB_CONN_CONNECTION_TIMEOUT, DB_TIMEOUT);
resCode = DBSetConnectionAttribute (hdbc,ATTR_DB_CONN_ISOLATION_LEVEL, DB_ISOLATION_LEVEL_SERIALIZABLE);
resCode = DBOpenConnection (hdbc);

if (resCode != DB_SUCCESS) {ShowError(); goto Error;}

// begin map for construct
ed SQL statement
hmap = DBBeginMap (hdbc);
if (hmap <= 0) {ShowError(); goto Error;}


// specify the columns to be selected and the variables where column
// values will be placed.
resCode = DBMapColumnToInt (hmap, "Ch_NO",&Ch_Num,&Ch_NumStat);
if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
resCode = DBMapColumnToFloat (hmap, "Value", &Val, &ValStat);
if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
resCode = DBMapColumnToDouble (hmap, "ID",&nID, &nIDStat);
if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
//Point of error**********************
hstmt = DBActivateMap (hmap, "Ch_Details");
if (hstmt <= 0) {ShowError(); goto Error;}
//DBActivateMap is giving the Timeout Error
.
.
.
. resCode = DBPutRecord (hstmt_ID);
if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
.
.
.
.

[Jeremy_L]

Hi,

I see in your code that you changed the connection timeout, but didn't actually change the command timeout. Did you just happen to comment this code out of your posted code? The one I see is "resCode = DBSetConnectionAttribute (hdbc,ATTR_DB_CONN_CONNECTION_TIMEOUT, DB_TIMEOUT);". This is actually for the connection timeout, not the command timeout. You actually want ATTR_DB_CONN_COMMAND_TIMEOUT. Another thing is to be sure you are deactivating the map at the end of your execution to free resources by calling DBDeactivateMap. I also find it interesting that you were able to successfully do this in VB. Do you perform similar functions in VB and use ActiveX Data Objects in VB like you do in CVI and the SQL toolkit? That may also be part o
f the problem we are running into. I'll keep looking around here for other possible causes and solutions. Thanks!

Jeremy L.
National Instruments