weird problem

Question

weird problem

petercabus

SSC Veteran

Points: 251
More actions
June 29, 2005 at 10:59 am

#89635

Hi all,
We are using a migration tool to update our SQL databases (Aelita SQL Update Wizard).
On one server it has a problem. It wants to create a table but that table seems to exist already (probably a leftover from a previous test). But the table cannot be dropped or truncated because it doesn't exist anymore????
I get the following error messages:
Error nycs3906:Microsoft OLE DB Provider for SQL Server -2147217900 There is already an object named 'aelita_accounts' in the database.
Error nycs3906:Microsoft OLE DB Provider for SQL Server -2147217865 Could not truncate table 'aelita_accounts' because this table does not exist in database 'master'.
When I manually try to create a table I get the following error.
'Aelita_accounts' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'Aelita_accounts' in the database.
I can't find that object anywhere in the database.
Any ideas on how to remove this 'hidden' object?

Thanks,

Peter

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

First of all. Why do you have a table object in master?? You should have as little objects as possible in there. And mostly only admin stored procs.

Anyways run this and post the results :

use master

Select Name, XType, user_name(uid) as Owner from dbo.SysObjects where name = 'aelita_accounts'

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Just a couple of questions.

Is the table supposed to be in the master database, as the error indicates?

Might it have been created under an id other than dbo?

If you do a select * from sysobjects where type = 'u' in the database where the table is supposed to be, does the tables show up?

And then again, I might be wrong ...
David Webb

petercabus SSC Veteran Points: 251 More actions · Answer 3

The migration tool seems to create temporary tables in the master DB.

I have no idea which account was used to create this table. I'm not even sure whether the object really exists at all since none of queries on sysobjects return the object.

Is it possible that the ODBC error is misleading? The server is running Windows 2003 - SQL 2000. The tools are running on a Windows 2000 server.

petercabus SSC Veteran Points: 251 More actions · Answer 4

further info

I cannot create any table in the master DB.

I always get the error message

'test' table

- Unable to create table.

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'test' in the database.

I tried this with Enterprise Manager on the local server to avoid connectivty issues.

Ideas please.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

could be... can you fire up the profiler, restart the process and reexecute in QA to have the actual error and more info on what's going on?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Could you run profiler while this is running so you can see what the script is trying to do and the exact database context? What id are you logged on as when you run this and what server level permissions does that id have?

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

June 29, 2005 at 11:52 am

#569476

Is it echo in here?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 8

Perhaps we were identical twins separated at birth?

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

My mother, sisters and father would remember that one as they assisted my birth.

petercabus SSC Veteran Points: 251 More actions · Answer 10

Ok - I ran the following command in QA

use master

CREATE TABLE [test1] (

[pk] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

Server: Msg 2714, Level 16, State 6, Line 1

There is already an object named 'test1' in the database.

Profiler did not give any extra info.

I'm a full admin on the box and the sql server.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 11

I've seen these kind of confusing messages before when the script assumes that the user running it is either SA or has the target database set as his default and that he is DBO there. I'm guessing that it's a flaw in the script and that using an id that matches the assumptions might make it run correctly.

Please tell me you didn't just post something that said the same thing!

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

If there's no object in master, then maybe it is create twice in the same transaction... are you sure you got everything statement in that batch?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

"Please tell me you didn't just post something that said the same thing!"

Might I remind you that you are the one repeating after me... not the other way around.

petercabus SSC Veteran Points: 251 More actions · Answer 14

-----

use master

CREATE TABLE [test1] (

[pk] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

------

Nothing else. Very strange.