Set FMTONLY On

Last Post 27 Sep 2002 06:18 AM by satya. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Cameron_SQL
New Member
New Member

--
23 Apr 2002 09:03 PM
Here is our problem

We're using ADO and SQLOLEDB to connect to a Sql Server database. We use ADO Dataset objects, to set the commandtext property and then open the dataset. Nothing fancy.

For reasons unknown, ADO seems to like to replicate some of our select queries and wrap them inside SET FMTONLY ON and SET FMTONLY OFF. So for a simple select statement, two queries are sent to the server resulting in a performance hit due to network round trips.

The thing that confuses the heck out of me is that some queries work fine (without the SET FMTONLY) whereas others don't, and I can't see a difference in the ADO Dataset objects we're using.

I know FMTONLY has something to do with sending metadata back instead of a resultset but we really don't need this to happen.

The issue is basically to do with the performance hit involved with an additional, unnecessary network round trip.

Can we stop this from happening?

Any Suggestions would be much appreciated.

jbarszc
New Member
New Member

--
26 Sep 2002 09:23 AM
Cameron,

Did you ever resolve the problem of the Set FMTONLY on - Set FMTONLY off being issued by ADO?

We are experiencing the exact same problem with our VB applications.

Jim
satya
New Member
New Member

--
26 Sep 2002 11:50 PM
Jim have you seen any errors during this process.
jbarszc
New Member
New Member

--
27 Sep 2002 06:08 AM
Yes, the complete string that we are tracing is:

Set FMTONLY on EXEC Set FMTONLY off

which results on a syntax error. It looks like the ADO environment is sending this with a null after the EXEC instead of a T-SQL statement.
satya
New Member
New Member

--
27 Sep 2002 06:18 AM
You should not use EXEC for SET statements.
jbarszc
New Member
New Member

--
27 Sep 2002 06:27 AM
I am not submitting this command. The VB or the ADO environment is submitting this on our behave. I would like to shut it off. But can't find the parameter or commmand to do it.

The application works just fine. It's just that we see this traffic in our traces and would like to eliminate the extraneous network traffic.
satya
New Member
New Member

--
27 Sep 2002 06:27 AM
I feel Umachander's tip has some resolution, have a thorough check on application code or where this code is getting submitted by using PROFILER.
You are not authorized to post a reply.

Acceptable Use Policy