Post reply

Query won''t return records that exist

  • October 19, 2006 at 5:02 pm

    #173192

    Hi,

    I'd really appreciate some insight with this problem. I'm not a DBA, and our organisation doesn't have a dedicated DBA to work on this.

    The problem is, when I run a select * on a table with approximately 15000 records the query never runs to completion. When I stop it's execution the records displayed are only a subset of the total records that there should be.

    Out of desperation I tried exporting the data to another table and dropping the problem table. I couldn't drop the table. I couldn't delete from the table and I couldn't trucate the table.

    Any ideas anyone?

  • October 19, 2006 at 5:25 pm

    #666957

    What permissions do you have on the table, and in the database?

    Please post the actual query you're trying to run and the table DDL, including indexes.

    What error messages do you get when you try to delete or truncate the table?

    What tool are you using to run the query that never completes?

    And then again, I might be wrong ...
    David Webb

  • October 19, 2006 at 6:04 pm

    #666960

    I have select, insert, update, delete and dri(whatever that means) on the table. I'm in the public and db_owner roles.

    I get no error messages from trying to delete or truncate table, the execution just hangs without completing.

    I've been using SQL Query Analyzer for these tasks.

    This is the query:

    SELECT *

    FROM [IMSLive].[IMSV7].[HIA_CUSTPROB_REQUEST]

    And the DDL:

    CREATE TABLE [IMSV7].[HIA_CUSTPROB_REQUEST](

     [HIA_CUSTPROB_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

     [SERVNO] [int] NULL,

     [ACCTKEY] [int] NULL,

     [ADDBY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [ADDDTTM] [datetime] NULL,

     [ADDRKEY] [int] NULL,

     [APKEY] [int] NULL,

     

    [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [ASGNFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [BGTNO] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [CASEFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [CLERICDTTM] [datetime] NULL,

     [COMMENTS] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [COMPKEY] [int] NULL,

     [CUSTFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [DISTFROMFT] [float] NULL,

     [DISTFROMM] [float] NULL,

     [DISTRICT] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [DISTTOFT] [float] NULL,

     [DISTTOM] [float] NULL,

     [INSPDAYS] [smallint] NULL,

     [INSPDTTM] [datetime] NULL,

     [INSPECTR] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INSPFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INSPHRS] [smallint] NULL,

     [INSPMINS] [smallint] NULL,

     [INWORKFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [LOC] [varchar](254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [MAPNO] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [MODBY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [MODDTTM] [datetime] NULL,

     [MOREWORK] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [PRCLKEY] [int] NULL,

     [PRI] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [PROB] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

     [PROBDTTM] [datetime] NULL,

     [PROJ] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [QTYCALLS] [smallint] NULL,

     [RESCODE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [RESDTTM] [datetime] NULL,

     [RESDUEDTTM] [datetime] NULL,

     [RESFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [RESP] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [SCDCMPDTTM] [datetime] NULL,

     [SCHDUEDTTM] [datetime] NULL,

     [SCHEDDTTM] [datetime] NULL,

     [SERVLINK] [int] NULL,

     [SRC] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [SRTYPE] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [STARTDTTM] [datetime] NULL,

     [STDUEDTTM] [datetime] NULL,

     [SUBAREA] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [SUSPDTTM] [datetime] NULL,

     [TAKENBY] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

     [INFRA_PROP_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_ISSUE_TYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_REF_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_PARENT_REF_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_TITLE] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_FNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_LNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_ADDR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_ADDR2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CUST_PHONE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CALL_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [INFRA_CALL_DESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [PROCESSED] [int] NOT NULL DEFAULT (0),

     [PROCESSED_MESSAGE] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

     [PAR_ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    PRIMARY KEY CLUSTERED

    (

     [HIA_CUSTPROB_ID] ASC

    ) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Thanks,

    Andrew

  • October 23, 2006 at 5:52 pm

    #667481

    Thanks for putting some time into this for me, David. We've fixed the table by rebooting the server. We still don't know what went wrong, but fixing it with a reboot would indicate a corruption somewhere I think.

    Thanks once again,

    Andrew

  • October 24, 2006 at 6:45 am

    #667542

    I wouldn't say so it was corruption.

    Did you check for any locks on the table?

    Bouncing a server is a harsh way to resolve an access issue - this really is a last resort scenario.

  • October 24, 2006 at 1:38 pm

    #667678

    We released all locks, with no success.

  • October 25, 2006 at 2:27 am

    #667784

    What was the state of the indexes?  Had the stats been updated recently?  Was there any other process hogging resources - were other queries run at an acceptable speed?

  • October 25, 2006 at 7:24 pm

    #668066

    Other queries seemed to work ok. I'm not sure of the answers to your other questions. We didn't check those, at least not that I'm aware of. Someone in our IT team may have done that. I know that sounds funny, but I'm not in the IT team, although I am employed as a software developer in another team that liases closely with IT. My level of expertise with SQL Server is not at a DBA level and I'm still learning some of the basics. The problem has been resolved for now, but if there's a next time I'll be sure to check those points that you've indicated. Many thanks for your help.

Viewing 8 posts - 1 through 7 (of 7 total)

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