#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Philadephia
    Posts
    16
    Rep Power
    0

    Question SQL 2008 vs 2005 New Error on "If"


    Good afternoon,
    I have a query that generates a table and its fields that works in 2005. Now I am on a 2008 Server and the query no longer works.

    Was working code:

    SELECT NCOS
    USE

    if exists (select * from dbo.sysobjects where id = object_id(N'dbo.NCOS_Data') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE dbo.NCOS_Data
    GO

    CREATE TABLE dbo.NCOS_Data (
    NC_UniqueID int identity(1,1),
    NC_Lead_Number varchar (50)
    )
    GO

    Now I get this error in 2008

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'if'.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0
    You are missing the database name in your statement.

    SELECT NCOS
    USE ???

    if exists (select * from

    You need to insert your table name for this to work. Change ??? to your table name.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    it should be

    Code:
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NCOS_Data]') AND type in (N'U'))
    DROP TABLE [dbo].[NCOS_Data]
    GO

IMN logo majestic logo threadwatch logo seochat tools logo