Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    3

    Unanswered: Dropping constraint on temporary table

    I made a constraint on a temporary table in a stored procedure but now i can't delete it.

    Here's what happened:
    I ran this in a stored procedure

    CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

    next time i ran the stored procedure it gave me
    There is already an object named 'UniqueFields' in the database.

    but since the temporary table is out of scope i cannot delete the constraint
    I tried
    delete from tempdb..sysobjects where name = 'UniqueFields'
    and
    declare @name
    set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
    drop table @name

    giving me
    Ad hoc updates to system catalogs are not allowed.
    or
    Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

    To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:
    Code:
    DROP TABLE #teFotograferen
    -PatP

  3. #3
    Join Date
    Mar 2007
    Posts
    3
    Pat

    That's exactly what defines my problem
    If i run
    DROP TABLE #teFotograferen

    i get
    Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

    because the table was a temporary table and there's no way to get back in the scope where it was defined.

    If i recreate the table and then drop it the constraint still remains in my database.

    create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
    alter table #tefotograferen drop constraint UniqueFields
    drop table #tefotograferen
    gives me
    Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
    because it is not the same table

    on the other hand

    create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
    alter table #tefotograferen drop constraint UniqueFields
    drop table #tefotograferen
    gives me
    There is already an object named 'UniqueFields' in the database.

    In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

    It is not a design problem in my application, i just put some garbage in that i can't get out

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Pat Phelan
    This kind of problem is symptomatic of multiple sub-problems.
    That comment wasn't an accident.

    One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

    You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

    I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

    The solution to this problem is to:

    a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
    b) Create the constraint with a default name (which is unique for each execution).

    This should get you far enough to find the next problem!

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [smacks forehead]

    why would you need contraints on a temp table?

    [/smacks forehead]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2007
    Posts
    3

    Thumbs up NEVER use constraints on temp tables

    After a restart of the server the offending constraint was gone.

    Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

    I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

    But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

    Thanks for the advice and input.

  7. #7
    Join Date
    Feb 2012
    Posts
    1

    Wink Solution

    Quote Originally Posted by Lxocram View Post
    I made a constraint on a temporary table in a stored procedure but now i can't delete it.

    Here's what happened:
    I ran this in a stored procedure

    CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

    next time i ran the stored procedure it gave me
    There is already an object named 'UniqueFields' in the database.

    but since the temporary table is out of scope i cannot delete the constraint
    I tried
    delete from tempdb..sysobjects where name = 'UniqueFields'
    and
    declare @name
    set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
    drop table @name

    giving me
    Ad hoc updates to system catalogs are not allowed.
    or
    Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.
    ---------------------------------------------------------------
    Solution: plz use
    IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#TeFotograferen'))
    BEGIN
    DROP TABLE #TeFotograferen
    END
    else
    begin
    create table #TeFotograferen (...)
    end

  8. #8
    Join Date
    Feb 2012
    Location
    DUBAI
    Posts
    6
    -- You may try the following

    IF OBJECT_ID('tempdb.dbo.#TeFotograferen') IS NOT NULL
    BEGIN
    DROP TABLE #TeFotograferen
    END


    -- akhilesh

Posting Permissions

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