[dba-SQLServer] Deleting Global Temporary Tables

Try:

If Object_Id('tempdb..##tblCAFRClientAsset') is Not Null
DROP TABLE dbo.##tblCAFRClientAsset

-- 
Stuart

On 2 Jun 2008 at 11:39, David Emerson wrote:

> Group,
> 
> I have this code in a sproc:
> 
>          IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = 
> object_id(N'dbo.##tblCAFRClientAsset') AND OBJECTPROPERTY(id, 
> N'IsUserTable') = 0)
>                  DROP TABLE dbo.##tblCAFRClientAsset
> 
>          CREATE TABLE dbo.##tblCAFRClientAsset (
>                  [ClientAssetID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
>                  [AssetType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
>                  [Owner] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
>                  [Description] [varchar] (50) COLLATE 
> Latin1_General_CI_AS NULL ,
>                  [Description2] [varchar] (50) COLLATE 
> Latin1_General_CI_AS NULL ,
>                  [AccountNo] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
>                  [MarketValue] [money] NULL ,
>                  [Contributions] [money] NULL ,
>                  [ContributionsImp] [money] NULL ,
>                  [InterestRate] [real] NULL ,
>                  [PropertyType] [varchar] (50) COLLATE 
> Latin1_General_CI_AS NULL ,
>                  [PropertyExpenses] [money] NULL ,
>                  [InvestmentType] [varchar] (50) COLLATE 
> Latin1_General_CI_AS NULL ,
>                  [InsuranceType] [varchar] (50) COLLATE 
> Latin1_General_CI_AS NULL ,
>                  [InsCover] [money] NULL ,
>                  [InsCoverImp] [money] NULL ,
>                  [InsPolicyTerm] [smallint] NULL ,
>                  [Comment] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
>                  [SortOrder] [smallint] NULL
>                  )
> 
> I am getting an error message when I run it "There is already an 
> object named '##tblCAFRClientAsset' in the database.".  This is on 
> the line trying to create the table.  It seems that the table is not 
> being dropped.  When I try to run just the select portion of the 
> first line nothing is returned.  How do I identify if the temporary 
> table exists so I can drop it?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>