Re: recursive sp, fmtonly, ado,
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/02/04
- Next message: Aaron [SQL Server MVP]: "Re: User Defined Function"
- Previous message: David Gugick: "Re: How to model Enumeration's in a Table"
- In reply to: Attila Gyuri: "Re: recursive sp, fmtonly, ado,"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 2 Dec 2004 14:31:57 -0500
Attila Gyuri wrote:
> 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
Call Borland and have them help you. It is Delphi that is triggering it,
not ADO. Delphi is either setting a property to trigger it or is doing
it manually in its code for some reason.
-- David Gugick Imceda Software www.imceda.com
- Next message: Aaron [SQL Server MVP]: "Re: User Defined Function"
- Previous message: David Gugick: "Re: How to model Enumeration's in a Table"
- In reply to: Attila Gyuri: "Re: recursive sp, fmtonly, ado,"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
