[dba-SQLServer] Deleting Global Temporary Tables

With further testing I was able to get both methods to work:

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = 
object_id(N'tempdb.dbo.##tblCAFRClientAsset'))
DROP TABLE dbo.##tblCAFRClientAsset


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

Does one method have any advantages over the other?

David.

At 2/06/2008, Stuart wrote:
>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
> >
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com