Re: Stored procedure executes twice

From: Toby Herring (therring*_at_*teletrack.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 13:03:32 -0400

The SET NOCOUNT issue is an unrelated issue to yours (judging by your
descriptions in other posts) that can cause a result that would make many
people think that a precedure was running twice, or that an extra procedure
was being executed.

I've never experienced a problem in ADO.Net similar to what you're
describing. My guess would be that you're doing two things in your .Net
code, each of which is causing the command to be executed, resulting in the
duplicate executions. 

-- 
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life? 
http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e
"Derrick Pope" <DerrickPope@discussions.microsoft.com> wrote in message 
news:FEC73502-C3AA-48CB-A988-8D9C11E588F2@microsoft.com...
>I do have the set nocount on.
>
> I would like more information about how not having that would cause this
> issue.  The more information I can gather the better my chances of solving
> this.
>
> Thanks
>
> "Adam Machanic" wrote:
>
>> Derrick,
>>
>> Do your stored procedures contain SET NOCOUNT ON?  If not, they should.
>> That can cause this issue.
>>
>> I also encountered this issue when using certain ADO lock types;
>> unfortunately, I can't remember which ones (it's been a few years since 
>> I've
>> used ADO); try changing the lock types to see if that helps.  Sorry I 
>> can't
>> give you more detail!
>>
>>
>> "Derrick Pope" <DerrickPope@discussions.microsoft.com> wrote in message
>> news:4CDB46D0-D601-42BB-967D-E97F419A4EB9@microsoft.com...
>> > I have a large application.  All access to the DB is through ADO
>> Recordsets
>> > running in COM+.  The programming language is VB6.
>> >
>> > The ADO recordset executes stored procedures.  Some of these stored
>> > procedures are quite complex in what they do.  In the end they all 
>> > select
>> a
>> > row from the DB and/or status information kept in variables.  So I 
>> > always
>> get
>> > some kind of record set.
>> >
>> > I have had a problem where sometimes the stored procedure gets executed
>> > twice from a single call.  I have not been able to figure out how to 
>> > make
>> > this happen predictably, which has made trying to solve this very
>> difficult.
>> >
>> > We are currently still using MDAC 2.5.
>> >
>> > Is there something in ADO that would cause it to call my SP twice?  I 
>> > have
>> > read about FMTOnly setting which is supposed to allow the caller to 
>> > then
>> get
>> > metadata without any rows being returned.  If ADO was doing this it 
>> > could
>> > account for what I am seeing.
>> >
>> > Can anyone help me with this issue?
>> > -- 
>> > Thanks,
>> > Derrick
>>
>>
>>

Relevant Pages

  • Re: Stored procedure executes twice
    ... Could you provide any more detail on the SET NOCOUNT ON issue? ... > Do your stored procedures contain SET NOCOUNT ON? ... > used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure executes twice
    ... > Do your stored procedures contain SET NOCOUNT ON? ... > used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure executes twice
    ... Do your stored procedures contain SET NOCOUNT ON? ... I also encountered this issue when using certain ADO lock types; ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure error handling
    ... i'm trying to do some error checking on stored procedures and am ... > DECLARE @newSupplierId as bigint ... > INSERT INTO Supplier ... > SET NOCOUNT OFF ...
    (comp.databases.ms-sqlserver)
  • Re: Updating sql05 with Stored Procedure
    ... Have you provided stored procedures for select/insert/update/delete? ... @Original_CategoryID int, ... SET NOCOUNT OFF; ... SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY()) ...
    (microsoft.public.dotnet.framework.adonet)