locked
No data returned sometimes RRS feed

  • Question

  • I have a T-SQL script most of which is a select statement.  Running this query returns data only sometimes.  When it does it is correct.  When it doesn't it completes fairly quickly but returns no rows.  This behaviour happens in both SQL server 2005 and SQL server 2000 with clients: SQL Analyzer, SSMS 2005, SSMS 2008 R" and .NET Native SQL client.  The script follows.  Any ideas ?

    declare @CampaignID int

    set @CampaignID = 4

    --set a table with reg/site/pos of completed data
    create table #DataFilter
     (RegionID int
     ,SiteID  int
     ,PositionId int
     )
    create index ixDataFilter on #DataFilter
     (RegionID asc
     ,SiteID  asc
     ,PositionID asc
     )
    insert #DataFilter
     select DISTINCT RegionID, SiteID, PositionID
      from PDAData_Data D
      join PDAData_Config C
        on D.selectID = C.selectID
      where C.CampaignID = @CampaignID

     select distinct
       S.RegionID
      ,S.SiteID
      ,S.Customer
      ,C.[Customer Name]
      ,G.CustomerGroupDesc
      ,ME.PositionID
      ,ME.MachineNameCode
      ,PH.MinNo
      ,PH.MachineName
      ,ME.MachineType
      ,ME.CollectionFrequency
      ,X.Collector
      ,U.Name  --Collector name
      ,'   '
      ,'     '
      ,US.Name
     from Site S
     left join tblMachineExtractNon90Day ME
       on S.RegionID = ME.RegionID
      and S.SiteID = ME.SiteID
     join #DataFilter
       on S.RegionID  = #DataFilter.RegionID
      and S.SiteID  = #DataFilter.SiteID
      and ME.PositionID  = #DataFilter.PositionID
     left join tblPositionHistory PH
          on S.RegionID = PH.RegionID
         and S.SiteID = PH.SiteID
         and ME.PositionID = PH.PositionID
         and ME.MachineNameCode = PH.MachineNameID
         and isnull(PH.RemovalDate,getdate()) >= getdate() 
      left join tblCustomer C
       on C.CustomerID = S.Customer
     left join CustomerGroup G
       on G.GroupAmedisLabel = C.[Customer Amedis Code]
     left join CollectionPlanSite X
       on X.RegionID = S.regionID
      and X.SiteID   = S.SiteID  
         and X.customer <> 0
         and X.CollectionDate between dateadd(d,-ME.CollectionFrequency,getdate()) and getdate()
     left join CompanyUsers U
       on U.RegionID = S.RegionID
      and U.UserID   = X.Collector
     left join CompanyUsers US
       on US.UniqueUserID = U.ReportsTo 

     where S.regionID between 127 and 154
          --and ME.RegionID is not null
          and PH.RegionID is not null 
          and ME.MachineNameCode <> 0
         

    drop table #dataFilter

    Tuesday, July 27, 2010 2:29 PM

Answers

  • Hi,

    Many thanks - by replacing getdate() by a variable which is set at the beginning it cures the problem.

    When I was getting the problem I could be in SSMS and execute the query and get 430 rows returned.  Then execute again, and again and again...   all within a span of about 5 to 10 minutes, and about 65% of the executes returned zero rows, and 35 % returned 430 rows.  I even restored the database to a test database, with absolutely no other open connections and experienced the same activity.  I also ran it on SQL server 2000, 2005 2008 and 2008R2 with the same symptoms!

    If you examine the use of getdate() in this query it shouldn't affect the number of records being returned, only the content of some returned items may be inadvertantly null.  So why does this change cure the problem ???

    Again many thanks - I was becoming very frustrated...

    Thursday, July 29, 2010 11:19 AM

All replies

  • Hi ...

      Did you verify the base tables data .... I GUESS .. some other process is manipulating the base table data .. So based on that the query that u mentioned may give the results differently ...

    -- Reddy Balaji C.

    Tuesday, July 27, 2010 4:05 PM
  • I would agree with Balaji.

    Also notice that you are using getdate in the where clause which means output may change even if the data is unchanged. If you feel that you are seeing some inconsisent behaviour, provide complete repro to me blakhani (at) koolmail (dot) in

     


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, July 28, 2010 5:53 AM
  • Hi,

    Many thanks - by replacing getdate() by a variable which is set at the beginning it cures the problem.

    When I was getting the problem I could be in SSMS and execute the query and get 430 rows returned.  Then execute again, and again and again...   all within a span of about 5 to 10 minutes, and about 65% of the executes returned zero rows, and 35 % returned 430 rows.  I even restored the database to a test database, with absolutely no other open connections and experienced the same activity.  I also ran it on SQL server 2000, 2005 2008 and 2008R2 with the same symptoms!

    If you examine the use of getdate() in this query it shouldn't affect the number of records being returned, only the content of some returned items may be inadvertantly null.  So why does this change cure the problem ???

    Again many thanks - I was becoming very frustrated...

    Thursday, July 29, 2010 11:19 AM