Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Dropping a table( sybase 11.9.2 under NT 4.0

4 posts in Windows NT Last posting was on 2000-05-31 11:20:20.0Z
mitesh shah Posted on 2000-05-30 18:26:13.0Z
From: "mitesh shah" <mitesh_shah@princetonsoftech.com>
Subject: Dropping a table( sybase 11.9.2 under NT 4.0
Date: Tue, 30 May 2000 14:26:13 -0400
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <HQRXAWmy$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 38.162.135.57
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2083
Article PK: 1089616

Hello All,

I have a table test!_tmp in the database. When I try to drop the table, it
says invalid syntax.
I am not sure that the problem is. It gives invalid syntax near '!". Yes,
there is an exclamation character as part of tablename (drop table
test!_tmp)
Does any have a clue , what is wrong. I tried putting quote characters
etc, but it does not like it.

Please let me know

thanks,
Mitesh


Rob Verschoor Posted on 2000-05-31 11:20:20.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <HQRXAWmy$GA.203@forums.sybase.com>
Subject: Re: Dropping a table( sybase 11.9.2 under NT 4.0
Date: Wed, 31 May 2000 13:20:20 +0200
Lines: 58
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <yd5YDRvy$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0275.pvu.euronet.nl 194.134.165.20
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2072
Article PK: 1089545

Mitesh,

You can use one of the following solutions:


Solution 1:

set quoted_identifier on
go
drop table "test!_tmp"
go


Solution 2:

sp_rename "test!_tmp", somefunnyname
go
drop table somefunnyname
go

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA/SQL Developer for ASE 11.5
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

"mitesh shah" <mitesh_shah@princetonsoftech.com> wrote in message
news:HQRXAWmy$GA.203@forums.sybase.com...
> Hello All,
>
> I have a table test!_tmp in the database. When I try to drop the
table, it
> says invalid syntax.
> I am not sure that the problem is. It gives invalid syntax near '!".
Yes,
> there is an exclamation character as part of tablename (drop table
> test!_tmp)
> Does any have a clue , what is wrong. I tried putting quote
characters
> etc, but it does not like it.
>
> Please let me know
>
> thanks,
> Mitesh
>
>


"Mark A. Parsons" <pegasys Posted on 2000-05-31 08:31:20.0Z
Message-ID: <3934CDD8.F5983050@_internet.co.nz>
Date: Wed, 31 May 2000 20:31:20 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Dropping a table( sybase 11.9.2 under NT 4.0
References: <HQRXAWmy$GA.203@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 54
NNTP-Posting-Host: p320.ipa1-n8-16.iconz.net.nz 210.48.25.64
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2074
Article PK: 1089549


> I have a table test!_tmp in the database. When I try to drop the table, it
> says invalid syntax.
> I am not sure that the problem is. It gives invalid syntax near '!". Yes,
> there is an exclamation character as part of tablename (drop table
> test!_tmp)
> Does any have a clue , what is wrong. I tried putting quote characters
> etc, but it does not like it.

My guess is that you've found a glitch in the server. The '!' isn't a normal
character used in naming Sybase objects. I'm not surprised that it won't
recognize the name, however, I *am* surprised that you were able to create it
in the first place.

My first suggestion would be to contact Sybase TS and get an 'approved' method
from them for dropping said table ... because the other options I have in mind
require direct modifications to system tables.

My second suggestion would be to update the sysobjects table setting 'name' to
something more relevant. Just make sure you reference the table in the
'where' clause with it's 'id' and not it's name (test!_tmp). See the P&T
manuals for steps involved in modifying a system table. If, after the rename
you can't drop the procedure then a) try bouncing the dataserver and
re-dropping the table and then b) look at the source of sp_rename and see what
steps it takes when renaming a table (i.e., what else is updated besides
sysobjects.name)?

Assuming that the second suggestion doesn't work, grab an ER (entity
relationship) diagram of the system tables, figure out which system tables
contain info about your table (e.g., sysobjects, syscolumns, sysindexes, etc.)
... nah, forget that one ... unless you *REALLY* understand your system table
layouts you'll just cause more problems than what it's worth.

Last option would be to drop and re-create the database.

Personally I think the second option will probably work the best.

NOTE: Don't perform any of these steps in a production environment until
you've tested in a development or test environment!!!

--

Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz


David Owen Posted on 2000-05-31 03:40:33.0Z
Sender: dowen@kashka.midsomer.org
Subject: Re: Dropping a table( sybase 11.9.2 under NT 4.0
References: <HQRXAWmy$GA.203@forums.sybase.com>
From: David Owen <dowen@midsomer.org>
Organization: Midsomer Consultants Inc.
Date: 30 May 2000 21:40:33 -0600
Message-ID: <m2g0qze6a6.fsf@kashka.midsomer.org>
Lines: 19
User-Agent: Gnus/5.0802 (Gnus v5.8.2) Emacs/20.5
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: h29-5d-f4-26324826.cg.shawcable.net 24.70.23.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2076
Article PK: 1089550

>>>>> "mitesh" == mitesh shah <mitesh_shah@princetonsoftech.com> writes:

mitesh> Hello All, I have a table test!_tmp in the database. When I try
mitesh> to drop the table, it says invalid syntax. I am not sure that
mitesh> the problem is. It gives invalid syntax near '!". Yes, there is
mitesh> an exclamation character as part of tablename (drop table
mitesh> test!_tmp) Does any have a clue , what is wrong. I tried
mitesh> putting quote characters etc, but it does not like it.

You could update the status in the system tables and use the

dbcc dbrepair(dbid,dropdb)

option. I think this the syntax, but check the FAQ for a better run down.
I am amazed that you could create it in the first place!!!

--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ