SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL 2008 vs 2005 If Exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 03/18/2013 :  12:45:57  Show Profile  Reply with Quote
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'.


Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/18/2013 :  12:55:25  Show Profile  Reply with Quote
SELECT NCOS
USE

is invalid

is NCOS db name? if yes, it should be

USE NCOS
GO

..


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 03/18/2013 :  13:03:38  Show Profile  Reply with Quote
Monday brains ugg

Thanks

Bryan Holmstrom
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/18/2013 :  13:07:49  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 03/18/2013 :  13:16:55  Show Profile  Reply with Quote
You can also use the object_id function instead of exists:

IF object_id('dbo.NCOS_Data', 'U') IS NOT NULL
DROP TABLE dbo.NCOS_Data;
GO

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000