Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: The Stubborn Table!!!!!

    I am running a canned wizard for an accounting program that uses Microsoft SQL 7 server, and it locks up when attempting to access a particular table. I was told that I needed to change permissions for the Public Role, granting "insert, update & delete" rights. (select is already enabled)

    The problem is that SQL locks up and goes into a "not responding" mode whenever I attempt to change permissions on this table. Not only that, but it also locks up if I try to drop the table or rename the table. I've tried it though scripting as well, but that locks up too. I have tried to go into the role properties and it locks up there. I've done it from multiple workstations as well as the server, and I have rebooted the server and verified that there are not any locks in place. I am logged in as the sa, and I have rights that should enable me to accomplish this simple task. I can also touch any other table in the database and successfully change the rights. The table is consists of only 3 records each containing 2 columns of data, and I have let my scripts run for 2 hours before bailing out of them....

    Any ideas? I hate to give Bill Gates $250 more....

    (BTW, the server is running on Windows NT 4)

  2. #2
    Join Date
    Sep 2003
    Location
    Australia
    Posts
    2

    Cool

    suggestion:

    Run SQL Profiler and Trace your query.
    see whether there is any trigger occur.

    have you run DBCC CHECKTABLE on the table?

    Hope this help!

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Hi

    Running profiler will be usefult o see what is actually going on. We found an app used to crash because a table lock was used instead of a row lock, thus stopping anyone except the current user accessing the table required. Check the table isnt locked using Enterprise Manager , Management, Current Activity, Process Info. Look on far right for locking/blocking. Also check this against the Profiler activity recorded.

    If that doesnt work, detach the database, copy the MDF & LDF files to another server of same SQL version & SP level, & reattch the database using the same db name and see if the problem follows it.
    I dont think it will.

    Also, probably best to avoid using the public role to access the db, set up a SQL user if possible - easier to audit & track in situations such as this. This is a nice to have.

    Let us know how it goes.

    SG.

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Solution

    Thanks for the ideas.

    What I ended up doing was going into the sysops table and changing the name of the table there, then I was able to build a new identical table with the proper permissions.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Hi

    And that worked OK?

    Cheers

    SG.

  6. #6
    Join Date
    Sep 2003
    Location
    Australia
    Posts
    2

    Post

    That's Great izmorrow (You've Figure it out) .

    Regards Dat.

    Just for the Record, I found this article:
    ---------------------------------------------------------------------------
    http://www.quest-pipelines.com/pipel...ver/tips01.htm

    I have a corrupt SQL Server table that I can't drop. What can I do?
    Compliments of Neil Pike

    DBCC checkdb and newalloc the database in question and look up any other error messages that occur - then you can see the extent of the problem.

    The reason SQL won't let you drop a table in this situation is because the allocation pages/extent chain appears to be damaged or cross-linked in some way. So SQL Server thinks that there is actually data from other tables in pages/extents belonging to the problem object. If it lets you drop the table it might remove data from another table by mistake.

    Your choices are:

    Restore from a known good backup.

    Create a new database and copy all the data/objects out. You can use transfer manager/bcp for this. Then rename the databases round. (Or, dump and re-load if you had to do this across a network connection.)

    With a VERY large database with consequent loss of data then if you don't want to do 1 or 2, then you can rename the table by directly updating sysobjects. Then create a new table and just leave the old one there. Note that this is dangerous as there may be more severe corruption problems that will still be there.

    Call MS PSS and pay for a support call. They have utilities that allow page/link editting and they MAY try and "fix" the problem for you. However, this is not 100% successful, they may cause more damage, and you have to sign a form accepting all responsibility (and absolving them of any) for any problems that occur.
    Last edited by dati; 09-25-03 at 23:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •