Post reply

Different results when executing from .NET component compare to executing from SQL Management Studio

  • October 10, 2006 at 6:16 pm

    #120990

    Hi all,

    I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.

    This started to really annoying me, any thoughts or solution?

    Thanks very much guys

  • October 10, 2006 at 11:23 pm

    #665130

    hi

    maybe is has got something to do with the "SET" options.. for ex SET ANSI_NULLS and other like options.

    "Keep Trying"

  • October 11, 2006 at 5:34 pm

    #665336

    I didn't do anything special when I call the stored procedure, if it is the SET_ANSI_NULLS i think that should return same result when I am querying through SQL Management Studio.

    The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.

    I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.

    SELECT

    main.*,fa.*

    FROM [NetFare] main

    INNER JOIN dbo.Agent fa

    ON main.FareId = fa.FareId

    LEFT OUTER JOIN travel.dbo.currencies c

    ON c.Code = main.FareCurrency

    Where

    AND (PriceReturnType = @ReturnType)

    And main.PortSetID in (Select Distinct PortSetID

    From FarePortSetMember Where PortCode = @DestinationPort Or PortCode = @DestinationCity)

    And main.OriginPortSetId in (Select Distinct FarePortSetId

    From FarePortSetMember Where PortCode = @OriginPort Or PortCode = @OriginCity)

    AND ((DateFrom = @EarliestDepartureDate))

    AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)

    And status in ('Active','Updating')

    Option (KEEPFIXED PLAN)

    I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.

    So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.

    Could MVP dudes verify this for us?

    Btw, we are running SQL 2000 with ASP.NET 2.0 calling .NET 1.1 component

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply