Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Can't drop table :(
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Largo
Starting Member

Russia
22 Posts

Posted - 02/09/2010 :  03:11:09  Show Profile  Reply with Quote
I'm usin' SQL Server CE 3.5 SP2 Beta. I wanna check whether table exists. If yes, then drop it. I wrote this statement:

IF EXISTS (SELECT * FROM Act) DROP TABLE Act;


However, SQL Server says:

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM Act) DROP TABLE Act
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]


What is wrong?

-----
There is no knowledge that is not power.

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/09/2010 :  08:04:55  Show Profile  Reply with Quote
"(SELECT * FROM Act)" will give you an error if the table ACT does not exist.

You need to check for the existing of the Table Name (and Schema / Owner) in the system table - not sure what that is in CE version, sys.tables maybe?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/09/2010 :  20:06:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use the INFORMATION_SCHEMA views instead of the system tables wherever possible. Try this:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Act')
DROP TABLE Act

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Largo
Starting Member

Russia
22 Posts

Posted - 02/10/2010 :  00:12:31  Show Profile  Reply with Quote
I doesn't work :(

Here's error:


Major Error 0x80040E14, Minor Error 25501
> IF (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division') IS NOT NULL
DROP TABLE Division
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]



It seems that server doesn't like word "IF". Nuff weird. May be it's impossible to create such statements in Compact Edition?

-----
There is no knowledge that is not power.

Edited by - Largo on 02/10/2010 00:14:58
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/10/2010 :  02:54:51  Show Profile  Reply with Quote
I think SQLCE might not support IF

just execute the statement "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division'"
and checks for the result


KH
Time is always against us

Go to Top of Page

Largo
Starting Member

Russia
22 Posts

Posted - 02/10/2010 :  03:13:53  Show Profile  Reply with Quote
Yes, CE doesn't support IF. :( However, it supports INFORMATION_SCHEMA. :)

-----
There is no knowledge that is not power.
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/10/2010 :  03:27:24  Show Profile  Reply with Quote
Will any of this work on CE?

DECLARE @strSQL varchar(8000)

SELECT @strSQL = CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division')
                      THEN 'DROP TABLE Division'
                      ELSE ''
                      END

EXEC (@strSQL)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/10/2010 :  03:44:02  Show Profile  Reply with Quote
Nope. exec() is not supported (i think). However, the query should be fine.



KH
Time is always against us

Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/10/2010 :  07:19:57  Show Profile  Reply with Quote
"exec() is not supported"

sp_ExecuteSQL? OPENQUERY (to "self")?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 02/10/2010 :  07:32:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Largo

I doesn't work :(

Here's error:


Major Error 0x80040E14, Minor Error 25501
> IF (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division') IS NOT NULL
DROP TABLE Division
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]



It seems that server doesn't like word "IF". Nuff weird. May be it's impossible to create such statements in Compact Edition?

-----
There is no knowledge that is not power.


Have you tried this?

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division')
DROP TABLE Division

or

IF (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division') IS NOT NULL
DROP TABLE Division



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

amsqlguy
Yak Posting Veteran

USA
89 Posts

Posted - 02/23/2010 :  18:35:54  Show Profile  Reply with Quote
This doesnt seem to work either, is there no way to check and drop

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division')
DROP TABLE Division
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Major Error 0x80040E14, Minor Error 25501
> IF (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Division') IS NOT NULL
DROP TABLE Division
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Any suggestions and inputs would help

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/23/2010 :  19:01:38  Show Profile  Reply with Quote
use the method i posted on 02/10/2010 : 02:54:51


KH
Time is always against us

Go to Top of Page

amsqlguy
Yak Posting Veteran

USA
89 Posts

Posted - 02/23/2010 :  21:11:37  Show Profile  Reply with Quote
khtan, I can execute the select statement. However based on the result of the select I want to run drop table <tablename>, is there any way to write a conditional based statement.

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/23/2010 :  21:14:01  Show Profile  Reply with Quote
do it in your application and execute the DROP TABLE command based on the result


KH
Time is always against us

Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 02/24/2010 :  06:23:23  Show Profile  Reply with Quote
You would be far better off working out a strategy to prevent having to drop the table in the first place, either by using

proper system supported temporary tables
or
working with a permanent table and just clearing out the data items you do not need
or
reexamining your strategy for processing whatever data you are massaging.

Can you advise what you are trying to do in overall terms and maybe we can advise without getting lost in the symantics of what is/is not supported by SQL CE 3.5
Go to Top of Page

amsqlguy
Yak Posting Veteran

USA
89 Posts

Posted - 02/24/2010 :  09:46:20  Show Profile  Reply with Quote
Every night we drop tables from the CE database and refresh the DDLs with corresponding insert statements for select few tables from the SQL 2005 database. The CE will reside on the client app along with DDLs.

Or is there anyway to drop all the existing tables from CE without doing if exists check.

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/24/2010 :  21:38:06  Show Profile  Reply with Quote
drop all existing tables ?

might as easier to delete and re-create the SDF


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000