SQL Requests not Returning to MTS

[byrne1]

I am experiencing a problem with an MTS component that queries an MS SQL 7.0 database. When I issue a time-consuming SQL command to MS SQL Server 7.0 through MTS, it appears to "die" inside of SQL - no errors or recordsets are returned. I can issue other SQL commands from the same component without any problems as long as they do not require a great deal of processing.

I have basically narrowed my problem down to the following lines of code:

Dim obj As ObjectContext
Set obj = GetObjectContext()
Obj.SetComplete

Whenever I use these in my MTS component in conjunction with my SQL command (SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%') my request never makes it out of the SQL Server. If I do not use these lines, my code works but then I get an error message stating that the "transaction context is in use by another session".

I've used the SQL Profiler to analyze the requests being passed from MTS. It shows the above command from MTS starting but it never stops (or at least the profiler never reports it as stopping). I can issue the same command from Query Analyzer without any problems.

I can issue less demanding commands (ie, one that doesn't take so long to process) through the same MTS component and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.

I am really at a loss as to what I need to do to resolve this problem. I would greatly appreciate any help you can offer.

 

[vishnuprasad]

I think this sort of problem is very rare, but these might enhance the performance and result without any probs.

Create a clustered index on LastName at the sql end.
Use createinstance to create the subcomponents from the root obejct in MTS.
Call Setcomplete/setabort at appropriate code levels
Retreive only columns which are required, since unneccasary overheads in network traffic blocks the I/O buffer.
Increase your connectiontimeout value in the connectionstring of the connection object.

 

[mkuan]

And not to forget about using sprocs for your queries too.

Mink