When I use Import/Export feature of SQL Server 2000 to migrate SQL Server
2000 database to Oracle, SQL Server converts all table names to something
like this:
"table name", instead of just table names without any quotes.

Because of this when the tables are successfully migrated to Oracle and when
I run SQL Query, I have to use like this:

Select * from "table name"

instead of simply,

Select * from table name.

What's the problem? Why SQL Server includes double quotes with the table
names and how to get rid of this?

In message <A6D624EA-CBDF-4E81-B5BA-2C6EAD7E7C38 (AT) microsoft (DOT) com>, RPK
<RPK (AT) discussions (DOT) microsoft.com> writes
In SQL Server, if a table name has spaces in then you must delimit the
object name. Quotes can be used, and this is referred to "quoted
identifier", and can be controlled to some degree by SET
QUOTE_IDENTIFIER option as documented in Books Online. You can also use
square brackets similarly. The key point is fancy object names need to
be delimited, otherwise how does SQL now that the table is really called
"table name" as opposed to "table" and you want to alias it as "name".


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

It's a know issue with the Microsoft OLE DB provider for
Oracle. Refer to:
BUG: Table Names Enclosed in Double Quotes When Transferred
from SQL Server to Oracle
http://support.microsoft.com/?id=247750
In addition to what's posted in the KB article...which is
just to query the table like you are already doing, you can
use SQL Plus and go through and do a RENAME on the tables in
Oracle, e.g. RENAME "SomeTable" TO SomeTable

-Sue

On Wed, 23 Feb 2005 10:41:01 -0800, RPK
<RPK (AT) discussions (DOT) microsoft.com> wrote:

Exactly. That is a nice solution of renaming the Table Names.

However my Tables are of single names and so there is no question of
separating the Table Name with spaces.

But for a single word Table Name also it converts like this:

Original Table Name: TRANSAC
Converted to Oracle as: "TRANSAC"

And because of this if I change my Connection String in VB application to
use Oracle Tables, it gives error, since the Table Name has been changed to
"TRANSAC".

Is it a bug with OLE DB?


"Sue Hoegemeier" wrote:

Yup...that sounds like the bug for the KB article I posted.
It really doesn't have anything to do with having spaces in
the table names or what case is used. It just adds the
quotes.

-Sue

On Wed, 23 Feb 2005 21:01:02 -0800, RPK
<RPK (AT) discussions (DOT) microsoft.com> wrote: