×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Requests not Returning to MTS

SQL Requests not Returning to MTS

SQL Requests not Returning to MTS

(OP)
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.

RE: SQL Requests not Returning to MTS

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.


RE: SQL Requests not Returning to MTS

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

Mink

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close