[Microsoft][ ODBC SQL Server Drive ] Timeout expired

I am running a VB application which makes connection to the SQL server
database using DSN connection using ODBC. This application pulls data using
stored procedure. The application does not insert / delete / update data.
The application was working fine since it has been installed (more than ONE
year). Suddendly it has started generating runtime error '-2147217871
(80040e31)'. I have taken the following actions so far to identify the
problem:
1. Checked user permission set on the database. Its fine. ODBC test
connection is successful.
2. Debugged VB source. It successfully makes connection to the database but
generated error while executing Command object which is using a stored
procedure which pulls the data into the grid.
3. Changed the timeout property of the Command object to 60 seconds. Still
generating error.
4. Copy pasted Query from stored procedure into Query Analyzer. The query
ran within 10 seconds generating desired resultset. Hence, Table index /
fragmentation problem is ruled out.
5. Copy data and Object from Live database into another database on the same
server using DTS wizard. Changed database name in the ODBC configuration
pointing it to the new database having same data & objects (including users &
permissions). The application ran succussefully without generating any
error. Hence database server problem is ruled out.
6. The stored procedure used by the command object in VB code fails to
execute.
Any help on how to fix this problem?
Manish Acharya
Systems Analyst & database developer
Task Transactions Ltd., New Zealand.
Have you considered taking ODBC or SQL Server Profiler traces? These might
give more clues.
"Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>I am running a VB application which makes connection to the SQL server
> database using DSN connection using ODBC. This application pulls data
> using
> stored procedure. The application does not insert / delete / update data.
> The application was working fine since it has been installed (more than
> ONE
> year). Suddendly it has started generating runtime error '-2147217871
> (80040e31)'. I have taken the following actions so far to identify the
> problem:
> 1. Checked user permission set on the database. Its fine. ODBC test
> connection is successful.
> 2. Debugged VB source. It successfully makes connection to the database
> but
> generated error while executing Command object which is using a stored
> procedure which pulls the data into the grid.
> 3. Changed the timeout property of the Command object to 60 seconds.
> Still
> generating error.
> 4. Copy pasted Query from stored procedure into Query Analyzer. The query
> ran within 10 seconds generating desired resultset. Hence, Table index /
> fragmentation problem is ruled out.
> 5. Copy data and Object from Live database into another database on the
> same
> server using DTS wizard. Changed database name in the ODBC configuration
> pointing it to the new database having same data & objects (including
> users &
> permissions). The application ran succussefully without generating any
> error. Hence database server problem is ruled out.
> 6. The stored procedure used by the command object in VB code fails to
> execute.
> Any help on how to fix this problem?
> Manish Acharya
> Systems Analyst & database developer
> Task Transactions Ltd., New Zealand.
|||Thanks Chris for your response. I have been working on SQL server profiler
traces to identify this problem. My experience on using SQL server profiler
is a bit limited and I see this as an opportunity to hone my skills in this
area. Your reply confirms that I am on the right direction. I will post the
solution in this discussion group once I crack it.
Cheers.
Manish Acharya.
"Chris Lee[MS]" wrote:

> Have you considered taking ODBC or SQL Server Profiler traces? These might
> give more clues.
> "Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
> news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>
>
|||Hi Manish Acharya,
Had you find the solution for the problem above? I have the same problem
like you, so hope you can shared the solution with us.
Thank you.