Re: recursive sp, fmtonly, ado,

From: Attila Gyuri (gya_site_at_freemail.hu)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 19:31:03 +0100

You're right, I'm using Delphi.
This was the first thing I've checked, because I suspect Delphi.
But there is no explicit FMTONLY call.
I looked behind the Delphi object model,
and an ADO Command.Execute (calling the recursive sp) causes the FMTONLY.
It's in somewhere inside ADO or OLEDB.
Delphi might change a property resulting this.

I will check all the Connection and Command properties.

Thanks,

Attila

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:uh6kViJ2EHA.2572@tk2msftngp13.phx.gbl...
> Attila Gyuri wrote:
>> Hello!
>>
>> There are some recursive procedures in my app. Normally they have a
>> maximum nesting level of 4-5.
>>
>> ADO uses FMTONLY ON to get metadata from sql server. Recursive stored
>> procedures sometimes will fail with "Maximum stored procedure,
>> function, trigger, or
>> view nesting level exceeded (limit 32)".
>> These errors will be ignored by ADO, because FMTONLY is ON, but the
>> transaction started from the client will be rolled back.
>>
>> Is it possible to get rid of this fmtonly metadata checking?
>>
>> SQL Server 2000 SP3, MDAC 2.8
>>
>> Thanks,
>>
>> Attila Gyuri
>> SQL Server MCP
>>
>> Here is a simple script to reproduce the problem:
>>
>> set fmtonly off
>>
>> GO
>>
>> drop procedure fmttest
>>
>> GO
>>
>> create procedure fmttest
>> as
>>
>> if 1 = 0
>> begin
>> /*this won't be invoked except when FMTONLY is ON*/
>> exec fmttest
>> end
>>
>> GO
>>
>> set fmtonly off
>> /*this will be OK*/
>> exec fmttest
>>
>> /*this will cause "Maximum stored procedure, function, trigger, or
>> view nesting level exceeded (limit 32)*/
>> set fmtonly on
>> exec fmttest
>> /*here the transaction is rolled back*/
>
> I've never seen this in any ADO application, except some written in
> Delphi. It's not and ADO thing. It is something set in the ADO library
> when used by an application. Check you application code and try and change
> whatever properties are set that are causing the FMTONLY ON. It just adds
> unnecessary overhead to the application and database in most cases. Some
> grid controls use this feature to grab the empty result set in order to
> set up columns. Still, I think it's unnecessary.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Relevant Pages

  • Re: Delphi 8 and ado.net
    ... > different to using say ADO in Delphi? ... The Delphi ADO implementation is basically a wrapper around the Recordset ... have cursors into the database, there are no cursors in ADO.NET. ...
    (borland.public.delphi.non-technical)
  • Re: Sub Selects?
    ... > some nice Delphi DB technique to do this, or should I just build up the ... Due to the way that the ado ... lets assume that you have a list called SelectedIds ... I'd recommend against using the filtering method. ...
    (borland.public.delphi.database.ado)
  • Re: Field position in a record, how to change?
    ... If you know how to move fields with DAO you can use Ole to access the ... I have found how to obtain the (ADO) FieldDefs for the ... (I am searching documentations for this option but your answer might ... I may just have found the answer in my saved Delphi 5 help files ...
    (borland.public.delphi.database.ado)
  • Re: BDE alternatives?
    ... Remember that Hannes is a developer for NexusDB, ... Anyway, I think every Delphi DB solution will, after the next Delphi.NET ... In either case, in practice, you put down an ADO connection ...
    (borland.public.delphi.non-technical)
  • Re: slow ADO refresh, ending in error message
    ... It seems to me that if the feature works fine with some implementations ... with Delphi 7) and poorer with other implementations (the Delphi 7 ... >>Is ADO only for small databases then? ... > You can use any size database with ADO but if you want good performance you ...
    (borland.public.delphi.database.ado)