| Author |
 Topic  |
|
|
Largo
Starting Member
Russia
22 Posts |
 Posted - 02/09/2010 : 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
United Kingdom
19683 Posts |
Posted - 02/09/2010 : 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
USA
28965 Posts |
|
|
Largo
Starting Member
Russia
22 Posts |
Posted - 02/10/2010 : 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. |
Edited by - Largo on 02/10/2010 00:14:58 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
13672 Posts |
Posted - 02/10/2010 : 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
Time is always against us
|
|
|
|
Largo
Starting Member
Russia
22 Posts |
Posted - 02/10/2010 : 03:13:53
|
Yes, CE doesn't support IF. :( However, it supports INFORMATION_SCHEMA. :)
-----
There is no knowledge that is not power. |
|
|
|
Kristen
Test
United Kingdom
19683 Posts |
Posted - 02/10/2010 : 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)
Singapore
13672 Posts |
Posted - 02/10/2010 : 03:44:02
|
Nope. exec() is not supported (i think). However, the query should be fine.
KH
Time is always against us
|
|
|
|
Kristen
Test
United Kingdom
19683 Posts |
Posted - 02/10/2010 : 07:19:57
|
"exec() is not supported"
sp_ExecuteSQL? OPENQUERY (to "self")? |
|
|
|
madhivanan
Premature Yak Congratulator
India
20644 Posts |
Posted - 02/10/2010 : 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
USA
68 Posts |
Posted - 02/23/2010 : 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)
Singapore
13672 Posts |
Posted - 02/23/2010 : 19:01:38
|
use the method i posted on 02/10/2010 : 02:54:51
KH
Time is always against us
|
|
|
|
amsqlguy
Yak Posting Veteran
USA
68 Posts |
Posted - 02/23/2010 : 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)
Singapore
13672 Posts |
Posted - 02/23/2010 : 21:14:01
|
do it in your application and execute the DROP TABLE command based on the result
KH
Time is always against us
|
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2729 Posts |
Posted - 02/24/2010 : 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
USA
68 Posts |
Posted - 02/24/2010 : 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)
Singapore
13672 Posts |
Posted - 02/24/2010 : 21:38:06
|
drop all existing tables ?
might as easier to delete and re-create the SDF
KH
Time is always against us
|
|
|
| |
Topic |
|
Can't drop table :(
