MS SQL Server ODBC - Help!!! [Microsoft][ODBC SQL Server Driver]Timeout expired

Author

Help!!! [Microsoft][ODBC SQL Server Driver]Timeout expired

Chan

2005-04-18, 8:23 pm

Hi all...

Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall

i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server

so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367

almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database

[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG--> OR_20001_SN_ReceiptO
rder
IDispatch error #3121 Error Count:1 GetLastError Number:0.

once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime

i found the fact that sp starts and never completed after tracing by using
query profiler

is this related to network resource or table locking?
has anybody seen this case? or any ideas?

Mike Epprecht (SQL MVP)

2005-04-19, 3:23 am

Hi

Sounds like blocking at table level. If network connection issues occur, DTC
notices it and aborts.

Regards
Mike

"Chan" wrote:

> Hi all...
>
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
>
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
>
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
>
> almost for the past 1.5 years. my com+ application has worked 99.9999% fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
>
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG--> OR_20001_SN_ReceiptO
rder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
>
> once this error occurrs, all other components which are using "transaction"
> get the same error over and over and over again till i restart MSDTC service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
>
> i found the fact that sp starts and never completed after tracing by using
> query profiler
>
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Chan

2005-04-19, 9:23 am

Hi Mike

i am a litlte bit newbie for mssql
just typed "'sp_lock" to see if there is a table level lock in query analyzer
and no table lock information found on the target database which causing
troubles
would you please let me know how you find lock information?

one more thing i did not mention
number of ports opened is 10, not microsofts' recommendation minimum of 15~20

any comments helps me :)

"Mike Epprecht (SQL MVP)" wrote:
[color=darkred]
> Hi
>
> Sounds like blocking at table level. If network connection issues occur, DTC
> notices it and aborts.
>
> Regards
> Mike
>
> "Chan" wrote:
>

Sponsored Links