Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: Problem with dropping tables when set showplan_all is on

    I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.

    I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Smile

    Quote Originally Posted by disruptivehair
    I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.

    I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?

    From BOL,

    The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time.
    When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table; the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by mallier
    From BOL,

    The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time.
    When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table; the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

    Thanks; the DBAs figured it out. It didn't say anything about that in Books Online so I was completely stumped.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by disruptivehair
    Thanks; the DBAs figured it out. It didn't say anything about that in Books Online so I was completely stumped.
    What? I think mallier very rightly quoted the segment that provides the answer of your problem.
    I suggest you to go through it again....

Posting Permissions

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