Problem with "_" in Table/View-Names

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Problem with "_" in Table/View-Names

Author

Topic

RichardW
Starting Member

4 Posts

Posted - 2004-05-10 : 15:34:06

Is there a know problem with tables or views which have a underscore (_) in the name?
Everything runs correctly but when i try to import a table or a view form SQL-Server A to SQL-Server B occurs always an error.

Thanks for your help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 15:37:59

No. What are you using to import the tables? What is the error?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-10 : 15:44:07

Where's the code?

Brett

8-)

RichardW
Starting Member

4 Posts

Posted - 2004-05-10 : 15:55:30

I use the DTS-asistent to import tables and views from SQL-database A into SQL-database B.

As I'm using the german-version of the SQL-Server i have to transmit the error message:
[Microsoft][ODBC SQL Server Driver][SQL Server] objectname 'users_backup' not alowed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 15:58:19

Why not use this instead of DTS:

SELECT ColumnA, ColumnB
INTO databaseB.dbo.Some_Table
FROM databaseA.dbo.Some_Table

If the table already exists in databaseB, then:

INSERT INTO databaseB.dbo.Some_Table(ColumnA, ColumnB)
SELECT ColumnA, ColumnB
FROM databaseA.dbo.Some_Table

In English version, underscores are fine. So if it doesn't work for you, use brackets around the table name.

Tara

RichardW
Starting Member

4 Posts

Posted - 2004-05-10 : 16:05:38

I have to copy whole databases or at least numerous tables between the different SQL-servers.
I think it's boring to copy table by table..?

It works correctly if there is no "_" in the sourcetable's name.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 16:08:37

Did you try brackets?

[TableName]

If you have to copy whole databases, use BACKUP/RESTORE or sp_detach_db/sp_attach_db. You can easily write out the code for the copies doing something like this:

SELECT 'SELECT * INTO databaseB.dbo.' + name + ' FROM databaseA.dbo.' + name
FROM databaseA.dbo.sysobjects
WHERE type = 'U' and name <> 'dtproperties'

Just copy the output into a new window and execute. Change the WHERE clause if you don't want all tables.

Tara

RichardW
Starting Member

4 Posts

Posted - 2004-05-10 : 16:17:30

OK! Thanks, first i try to backup and restore the whole database.
If it don't works --> I'll be back!! ;-))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources