why cant anyone answer the odbc timeout problem?

why cant anyone answer the odbc timeout problem?

Post by Mark Williamso » Thu, 09 Sep 1999 04:00:00



many people have posted the question, no one has answered it.

"How do i disable the ODBC Timeout when using the SQL Server 7 query
tools?"

For example, click on Views, New View, type in a select statement or
update statement, if it takes longer than whatever the magical number
is, you get a timeout.  Yes, I know all about the registry settings
referred to in Microsoft's knowledge base but it doesn't affect it.  I
cannot find any settings specific to the sql server manager application.

Regards,
Mark Williamson

 
 
 

why cant anyone answer the odbc timeout problem?

Post by Jens K Jense » Sat, 11 Sep 1999 04:00:00


Hi Mark

My Query Analyzer offers setting of Query TimeOut in Query/Current
Connection Options. An ODBC Trace of what is sent to the driver (when one
sets timeout anything but 0) and hits 'Apply'), shows:

isqlw           d6:e9 ENTER SQLSetConnectAttrW
  SQLHDBC             0x00cb1248
  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER          0x00000001
  SQLINTEGER                  -5

isqlw           d6:e9 EXIT  SQLSetConnectAttrW  with return code 0
(SQL_SUCCESS)
  SQLHDBC             0x00cb1248
  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER          0x00000001 (BADMEM)
  SQLINTEGER                  -5

which suggests that the Query Analyzer (despite ODBC 3.0 recommandations)
tries to set querytimeout (actually an ODBC V1.0 define) on the connection
level, instead of setting it on the statement leve (once for statement
allocated). All values but 0 (meaning unlimited) will result in above
(BADMEM) from the ODBC trace DLL.

Assuming that setting the QUERY TIMEOUT per statement is supported by the
ODBC driver, sending below PER STATEMENT:

ODBCTE32        8c:d8 ENTER SQLSetStmtAttr
  SQLHSTMT            0x00d31ae8
  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER          0x00000000
  SQLINTEGER                  -5

ODBCTE32        8c:d8 EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
  SQLHSTMT            0x00d31ae8
  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER          0x00000000
  SQLINTEGER                  -5

would probably set the timeout of 0 (unlimited) in effect. This would agree
very well with ODBC 3.0 Documentation, which states:

    Note   The ability to set statement attributes on the connection level
has been deprecated.
                Statement attributes should never be set on the connection
level by an ODBC 3.0 application.

If all above guesswork is correct, then the Query Analyzer should be fixed
by Microsoft.

A possible work-around is to set Query Analyzer Time-Out to 0 (default) and
change the Server Wide settings of 'query wait' and possibly 'query governor
cost limit ', both 'advanced option's settable with sp_configure.

regards
jensk


Quote:> many people have posted the question, no one has answered it.

> "How do i disable the ODBC Timeout when using the SQL Server 7 query
> tools?"

> For example, click on Views, New View, type in a select statement or
> update statement, if it takes longer than whatever the magical number
> is, you get a timeout.  Yes, I know all about the registry settings
> referred to in Microsoft's knowledge base but it doesn't affect it.  I
> cannot find any settings specific to the sql server manager application.

> Regards,
> Mark Williamson


 
 
 

why cant anyone answer the odbc timeout problem?

Post by mar » Sun, 03 Oct 1999 04:00:00


wow.  That's good info, but unfortunately, doesn't answer the
question.

I have the following questions:

In the enterprise manager, when running queries in the "Create View",
if I issue a statement that takes too long, it times out.  I'd like to
get rid of that problem.

Secondly, occassionally, I have a timeout occur when doing an INSERT
for some reason.  The application just hangs and then errors out on
the insert, haven't figured this one out.

BTW, my asp application has all users logging in with the same login,
since we dont have the authentication turned on (dont need that
feature)..IS this a problem for me?

On Fri, 10 Sep 1999 19:51:46 +0200, "Jens K Jensen"


>Hi Mark

>My Query Analyzer offers setting of Query TimeOut in Query/Current
>Connection Options. An ODBC Trace of what is sent to the driver (when one
>sets timeout anything but 0) and hits 'Apply'), shows:

>isqlw           d6:e9 ENTER SQLSetConnectAttrW
>  SQLHDBC             0x00cb1248
>  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
>  SQLPOINTER          0x00000001
>  SQLINTEGER                  -5

>isqlw           d6:e9 EXIT  SQLSetConnectAttrW  with return code 0
>(SQL_SUCCESS)
>  SQLHDBC             0x00cb1248
>  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
>  SQLPOINTER          0x00000001 (BADMEM)
>  SQLINTEGER                  -5

>which suggests that the Query Analyzer (despite ODBC 3.0 recommandations)
>tries to set querytimeout (actually an ODBC V1.0 define) on the connection
>level, instead of setting it on the statement leve (once for statement
>allocated). All values but 0 (meaning unlimited) will result in above
>(BADMEM) from the ODBC trace DLL.

>Assuming that setting the QUERY TIMEOUT per statement is supported by the
>ODBC driver, sending below PER STATEMENT:

>ODBCTE32        8c:d8 ENTER SQLSetStmtAttr
>  SQLHSTMT            0x00d31ae8
>  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
>  SQLPOINTER          0x00000000
>  SQLINTEGER                  -5

>ODBCTE32        8c:d8 EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
>  SQLHSTMT            0x00d31ae8
>  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
>  SQLPOINTER          0x00000000
>  SQLINTEGER                  -5

>would probably set the timeout of 0 (unlimited) in effect. This would agree
>very well with ODBC 3.0 Documentation, which states:

>    Note   The ability to set statement attributes on the connection level
>has been deprecated.
>                Statement attributes should never be set on the connection
>level by an ODBC 3.0 application.

>If all above guesswork is correct, then the Query Analyzer should be fixed
>by Microsoft.

>A possible work-around is to set Query Analyzer Time-Out to 0 (default) and
>change the Server Wide settings of 'query wait' and possibly 'query governor
>cost limit ', both 'advanced option's settable with sp_configure.

>regards
>jensk



>> many people have posted the question, no one has answered it.

>> "How do i disable the ODBC Timeout when using the SQL Server 7 query
>> tools?"

>> For example, click on Views, New View, type in a select statement or
>> update statement, if it takes longer than whatever the magical number
>> is, you get a timeout.  Yes, I know all about the registry settings
>> referred to in Microsoft's knowledge base but it doesn't affect it.  I
>> cannot find any settings specific to the sql server manager application.

>> Regards,
>> Mark Williamson

 
 
 

why cant anyone answer the odbc timeout problem?

Post by Jens K Jense » Sun, 03 Oct 1999 04:00:00


Hi Mark

Sorry I was not more clear ! Apparantly I became to eager finding out why
QueryTimeOut sometimes had no effect in Query Analyzer.

Your Enterprise Manager and Query Analyzer both have options to change the
timeout. (At least with SQL Server 7.0 SP1)

- In Enterprise Manager you use Tools/Options/Connection and set your
QueryTimeout  to whatever you like.
- In Query Analyzer you set it in Current Connect Options. The latter should
work if you set it to a high number. At least, if I issue a query that runs
for 7 seconds it will time out if i change the Query time out to below 6
seconds.

Are you actually saying that above does not work ? How long does your query
run before it times out ?

According to BOL you can get timed out by the server if 'resource timeout'
is to low or  'query wait ' is to low. Again according to BOL, the SQL
Server Errorlog may give you a clue on which option is to low. I have left
the values at defaults and have never experienced any unreasonable timeouts.

regards
jensk


> wow.  That's good info, but unfortunately, doesn't answer the
> question.

> I have the following questions:

> In the enterprise manager, when running queries in the "Create View",
> if I issue a statement that takes too long, it times out.  I'd like to
> get rid of that problem.

> Secondly, occassionally, I have a timeout occur when doing an INSERT
> for some reason.  The application just hangs and then errors out on
> the insert, haven't figured this one out.

> BTW, my asp application has all users logging in with the same login,
> since we dont have the authentication turned on (dont need that
> feature)..IS this a problem for me?

> On Fri, 10 Sep 1999 19:51:46 +0200, "Jens K Jensen"

> >Hi Mark

> >My Query Analyzer offers setting of Query TimeOut in Query/Current
> >Connection Options. An ODBC Trace of what is sent to the driver (when one
> >sets timeout anything but 0) and hits 'Apply'), shows:

> >isqlw           d6:e9 ENTER SQLSetConnectAttrW
> >  SQLHDBC             0x00cb1248
> >  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
> >  SQLPOINTER          0x00000001
> >  SQLINTEGER                  -5

> >isqlw           d6:e9 EXIT  SQLSetConnectAttrW  with return code 0
> >(SQL_SUCCESS)
> >  SQLHDBC             0x00cb1248
> >  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
> >  SQLPOINTER          0x00000001 (BADMEM)
> >  SQLINTEGER                  -5

> >which suggests that the Query Analyzer (despite ODBC 3.0 recommandations)
> >tries to set querytimeout (actually an ODBC V1.0 define) on the
connection
> >level, instead of setting it on the statement leve (once for statement
> >allocated). All values but 0 (meaning unlimited) will result in above
> >(BADMEM) from the ODBC trace DLL.

> >Assuming that setting the QUERY TIMEOUT per statement is supported by the
> >ODBC driver, sending below PER STATEMENT:

> >ODBCTE32        8c:d8 ENTER SQLSetStmtAttr
> >  SQLHSTMT            0x00d31ae8
> >  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
> >  SQLPOINTER          0x00000000
> >  SQLINTEGER                  -5

> >ODBCTE32        8c:d8 EXIT  SQLSetStmtAttr  with return code 0
(SQL_SUCCESS)
> >  SQLHSTMT            0x00d31ae8
> >  SQLINTEGER                   0 <SQL_ATTR_QUERY_TIMEOUT>
> >  SQLPOINTER          0x00000000
> >  SQLINTEGER                  -5

> >would probably set the timeout of 0 (unlimited) in effect. This would
agree
> >very well with ODBC 3.0 Documentation, which states:

> >    Note   The ability to set statement attributes on the connection
level
> >has been deprecated.
> >                Statement attributes should never be set on the
connection
> >level by an ODBC 3.0 application.

> >If all above guesswork is correct, then the Query Analyzer should be
fixed
> >by Microsoft.

> >A possible work-around is to set Query Analyzer Time-Out to 0 (default)
and
> >change the Server Wide settings of 'query wait' and possibly 'query
governor
> >cost limit ', both 'advanced option's settable with sp_configure.

> >regards
> >jensk



> >> many people have posted the question, no one has answered it.

> >> "How do i disable the ODBC Timeout when using the SQL Server 7 query
> >> tools?"

> >> For example, click on Views, New View, type in a select statement or
> >> update statement, if it takes longer than whatever the magical number
> >> is, you get a timeout.  Yes, I know all about the registry settings
> >> referred to in Microsoft's knowledge base but it doesn't affect it.  I
> >> cannot find any settings specific to the sql server manager
application.

> >> Regards,
> >> Mark Williamson

 
 
 

1. ODBC Timeout - 2nd attempt at getting an answer

I posted this message last week, but have not received any response.
Perhaps no-one knows?
----------------------------------------------------------------------
----------------
It's very easy to create a View that you cannot execute in EM (not
even a Top 1000) because
of ODBC timeouts. Does anyone know how to change the default timeout
setting? I've scoured the registry and BOL, without success.

--
Regards

Paul Thornett

2. Shape command and the DataGrid

3. Can anyone answer this??? - trigger problem

4. sql 7 slower on dual processors

5. more ISQL problems - ANYONE know the answer?

6. searching terxt fields

7. ODBC Timeout - anyone know how to solve it ?

8. ODBC Query Timeout expired, WHY ??

9. Betya cant answer this!!!

10. Really stuck and cant find an answer help.

11. Answers, answers, who has the answers?

12. ODBC client SDK for NT problem: answer