INSERT INTO - OBJECT NOT EXIST SQL2005 SP3

Question

Post reply

INSERT INTO - OBJECT NOT EXIST SQL2005 SP3

ERIC CRUDELI

SSCommitted

Points: 1786
More actions
December 15, 2011 at 6:59 am

#143079

Hello,
I meet some problem when I tried to insert some data inside a new table.
Basically, I connected to my server with "sa". I created a new table inside an existing database. When I tried to execute the following script and I got a error :
INSERT INTO tblAdmin_Audit_DbSize (dbname, db_size, unallocated, reserved, data, index_size, unused)
select db_name(), db_size, unallocated,
reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1 and d.type = 'E'
Error : Msg 208, Level 16, State 1, Line 1
'tblAdmin_Audit_DbSize' Invalid object name
I checked if the table exists via the following scripts :
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tblAdmin_Audit_DbSize')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'
Result : 'tablename does not exist.'
SELECT OBJECT_ID('tblAdmin_Audit_DbSize','U') as objectid
Result : NULL
Could you help me ?
Thanks,
Eric

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

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

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 1

Devendrakumar Shirbad

SSC-Forever

Points: 42493

December 15, 2011 at 7:02 am

#1421920

Check for schema name.

Also, try NOT to use 'sa' login.

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 2

ERIC CRUDELI

SSCommitted

Points: 1786

December 15, 2011 at 7:05 am

#1421922

schema name of my database ?

Thanks,

Eric

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 3

ERIC CRUDELI (12/15/2011)
schema name of my database ?
Thanks,
Eric

Schema name for this table 'tblAdmin_Audit_DbSize'. If it's not your login's default schema you have to mention it the query (SCHEMA_NAME.TABLE_NAME).

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

John Mitchell-245523

SSC Guru

Points: 148786

December 15, 2011 at 7:12 am

#1421928

Eric

How did you create the table?

John

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 5

I just read it carefully. Table doesn't exist & you want to insert?

Please try SELECT * INTO statement.

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 6

I executed the following script :

Use theriaque

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'

AS SchemaTable

FROM sys.tables

Result : [dbo].[tblAdmin_Audit_DBSize]

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 7

USE [THERIAQUE]

GO

/****** Object: Table [dbo].[tblAdmin_Audit_DBSize] Script Date: 12/15/2011 15:15:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblAdmin_Audit_DBSize](

[DBName] [varchar](25) NOT NULL,

[db_size] [varchar](25) NOT NULL,

[unallocated] [varchar](25) NOT NULL,

[reserved] [varchar](25) NOT NULL,

[data] [varchar](25) NOT NULL,

[index_size] [varchar](25) NOT NULL,

[unused] [varchar](25) NOT NULL,

[RecordDate] [smalldatetime] NOT NULL,

[RecordID] [int] IDENTITY(1,1) NOT NULL,

PRIMARY KEY CLUSTERED

(

[RecordID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___DBNam__55F35E8E] DEFAULT ('Missing') FOR [DBName]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___db_si__56E782C7] DEFAULT ('Missing') FOR [db_size]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___unall__57DBA700] DEFAULT ('Missing') FOR [unallocated]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___reser__58CFCB39] DEFAULT ('Missing') FOR [reserved]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin_A__data__59C3EF72] DEFAULT ('Missing') FOR [data]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___index__5AB813AB] DEFAULT ('Missing') FOR [index_size]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___unuse__5BAC37E4] DEFAULT ('Missing') FOR [unused]

GO

ALTER TABLE [dbo].[tblAdmin_Audit_DBSize] ADD CONSTRAINT [DF__tblAdmin___Recor__5CA05C1D] DEFAULT (getdate()) FOR [RecordDate]

GO

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 8

Devendrakumar Shirbad

SSC-Forever

Points: 42493

December 15, 2011 at 7:16 am

#1421933

bad post... removed.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

When you ran the insert, were you using the correct database? Default for sa is usually master, not a user database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 10

I'm guessing your database has a case-sensitive collation. Look at the name of the table you created, and look at the name of the table you're trying to insert into. Notice the difference?

John

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 11

I guess you are executing your script in Master Database.

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 12

I have several database on my server. I use the script just to follow database size. The script doesn't work only on this database. I don't understand.

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 13

ERIC CRUDELI (12/15/2011)
I have several database on my server. I use the script just to follow database size. The script doesn't work only on this database. I don't understand.

I made a small change in your code... Please try it on your system. Let me know if it work's I will explain later. Please COPY & PASTE full code.

USE [THERIAQUE]

GO

INSERT INTO tblAdmin_Audit_DbSize (dbname, db_size, unallocated, reserved, data, index_size, unused)

select db_name(), db_size, unallocated,

reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1 and d.type = 'E'

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 14

Thanks John, :w00t:

Case sensitive on database

Table name : tblAdmin_Audit_DBSize

Column name : DBName

Old script :

INSERT INTO tblAdmin_Audit_DbSize (dbname, db_size, unallocated, reserved, data, index_size, unused)

select db_name(), db_size, unallocated,

reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1 and d.type = 'E'

New script :

INSERT INTO tblAdmin_Audit_DBSize (DBName, db_size, unallocated, reserved, data, index_size, unused)