Can't drop table :( - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Can't drop table :(

Author

Topic

Largo
Starting Member

22 Posts

Posted - 2010-02-09 : 03:11:09

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

22859 Posts

Posted - 2010-02-09 : 08:04:55

"(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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-09 : 20:06:12

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."

Largo
Starting Member

22 Posts

Posted - 2010-02-10 : 00:12:31

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-10 : 02:54:51

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
[spoiler]Time is always against us[/spoiler]

Largo
Starting Member

22 Posts

Posted - 2010-02-10 : 03:13:53

Yes, CE doesn't support IF. :( However, it supports INFORMATION_SCHEMA. :)

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

Kristen
Test

22859 Posts

Posted - 2010-02-10 : 03:27:24

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)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-10 : 03:44:02

Nope. exec() is not supported (i think). However, the query should be fine.

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-10 : 07:19:57

"exec() is not supported"

sp_ExecuteSQL? OPENQUERY (to "self")?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 07:32:43

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

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2010-02-23 : 18:35:54

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-23 : 19:01:38

use the method i posted on 02/10/2010 : 02:54:51

KH
[spoiler]Time is always against us[/spoiler]

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2010-02-23 : 21:11:37

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-23 : 21:14:01

do it in your application and execute the DROP TABLE command based on the result

KH
[spoiler]Time is always against us[/spoiler]

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-24 : 06:23:23

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

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2010-02-24 : 09:46:20

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 21:38:06

drop all existing tables ?

might as easier to delete and re-create the SDF

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources