Running long query using DBImmediateSQL function and Command Timeout

[PeggyC]

Hi,

I am have a SQL procedure that could potentially take > 5 minutes to run.  And, as I am trying to call that procedure from CVI 2009, I am using 'DBImmediateSQL' and that gaves me timeout error.  

I did some search online and found that it might be the Command Timeout attribute that I need to adjust.  How do I set that timeout value?

And also, if there is a recommended way to code the execute long SQL procedure?

I am using LabWindows/CVI 2009 Version 9.1.0 with SQL Server 2008 SP2

Thanks.

Peggy

[PeggyC]

An update to what I have posted.  I found out that I can use 'DBSetStatementAttribute' function to set the command timeout, which along with the cache size and other stuff.  However, one thing that I am wondering, after 'DBOpenSQLStatement', do I need to wait for the statement to finish executing or the statement is finished already?

The following is my revised code

------------------------------------

hstmt = DBNewSQLStatement (hdbc, "exec myProcedure");
resCode = DBSetStatementAttribute (hstmt, ATTR_DB_STMT_COMMAND_TIMEOUT,3600);
resCode = DBSetStatementAttribute (hstmt, ATTR_DB_STMT_CACHE_SIZE,1000);
resCode = DBSetStatementAttribute (hstmt, ATTR_DB_STMT_COMMAND_TYPE,DB_COMMAND_STORED_PROC);

resCode = DBOpenSQLStatement (hstmt);

....

------------------------------------

Thanks.

Peggy

[PeggyC]

Another update to my original question.  I gave up on the 'DBOpenSQLStatement' function and use 'DBPrepareSQL' and 'DBExecutePreparedSQL' with 'DBSetStatementAttribute' function instead.  It seems working good now with no error returned.