April 4, 2014 at 8:46 am
I am using SQL 2008 R2, SP2. I am having a problem completing a maintenance plan for Update Statistics. I am getting an error: "Executing the query "UPDATE STATISTICS [dbo].[ana_ledger_all] WITH FU..." failed with the following error: "Table 'ana_ledger_all' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
I generated the script and tried to run it manually and got the same results. I have verified that the table does not exist' although it id at one point.
What is causing this to happen? I am sure that it is because the table once existed, but since the database has been moved to the new server it is gone. It sounds like there is something somewhere that is telling SQL that the table is still there. How do I change that?
Thanks.
Steve
April 4, 2014 at 9:05 am
I don't use MPs all that often, so I haven't seen this error before, but have you checked sys.objects if this table "exists"? select * from sys.objects where type = 'U' and name 'ana_ledger_all'
I would also like to take this time to promote some great scripts that I use instead of MPs http://ola.hallengren.com/ 😀
April 4, 2014 at 9:21 am
Keith,
First, thanks for pointing me to the other scripts. I will definitely start using them instead.
As to your query, it shows that the table exists. However, when I do this:
select * from tmp_TrackRows_ana_ledger_all
I get this:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tmp_TrackRows_ana_ledger_all'.
What is happening? Is there corruption? Is there anything I can do to reconcile this?
Thanks.
Steve
April 4, 2014 at 11:02 am
sdownen05 (4/4/2014)
Keith,First, thanks for pointing me to the other scripts. I will definitely start using them instead.
As to your query, it shows that the table exists. However, when I do this:
select * from tmp_TrackRows_ana_ledger_allI get this:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tmp_TrackRows_ana_ledger_all'.
What is happening? Is there corruption? Is there anything I can do to reconcile this?
Thanks.
Steve
You're missing the schema in your SELECT statement. You should have someowner.tmp_TrackRows_ana_ledger_all. Otherwise, SQL Server will look in your default schema and if it can't find it, it'll throw the error you see. It's a best practice to use the schema anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2014 at 11:26 am
Grant is right use this query to find the name of the schema owner: SELECT ss.name as SchemaName, so.name as ObjectName
FROM sys.objects so
join sys.schemas ss on so.schema_id = ss.schema_id
where type = 'U' and name 'ana_ledger_all'
April 4, 2014 at 1:37 pm
You are both right. I need to make a habit of putting the owner in the query. So I did, and I still have the same issue. When I select from the table select * from dbo.tmp_TrackRows_ana_ledger_all I still get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tmp_trackrows_ana_ledger_all'.
But when I run your code
SELECT ss.name as SchemaName, so.name as ObjectName
FROM sys.objects so
join sys.schemas ss on so.schema_id = ss.schema_id
where type = 'U' and so.name = 'ana_ledger_all'
I get this result:
SchemaNameObjectName
------------- ------------
dbo ana_ledger_all
So, there is still a discrepancy. Any insights on this?
Thanks.
April 4, 2014 at 1:57 pm
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tmp_trackrows_ana_ledger_all'.
SchemaNameObjectName
------------- ------------
dbo ana_ledger_all
So, there is still a discrepancy. Any insights on this?
I"m confused, you are looking at a different table than the one that is causing the error. Which table are we try to track down ana_ledger_all or tmp_trackrows_ana_ledger_all?
April 4, 2014 at 2:09 pm
Keith,
Sorry about that. I got us all confused. I am working with the table tmp_TrackRows_ana_ledger_all. When I try to select something from it, I get the error above indicating the table does not exist. When I use your code to look for it, it shows up in sys.objects. My question is, how can I best go about eliminating this problem? Can I just delete the entry from the sys.objects table, or is this indicative of a bigger problem?
April 4, 2014 at 2:31 pm
So let me just do a reset here. The following code: SELECT ss.name as SchemaName, so.name as ObjectName
FROM sys.objects so
join sys.schemas ss on so.schema_id = ss.schema_id
where type = 'U' and so.name = 'tmp_TrackRows_ana_ledger_all' return this:
SchemaNameObjectName
-------------------------
dbotmp_TrackRows_ana_ledger_all
but this code: SELECT * FROM dbo.tmp_TrackRows_ana_ledger_all returns an error?
If this is true then please run this and return the entireresult set: DBCC CHECKDB (Database Name) WITH NO_INFOMSGS, ALL_ERRORMSGS
Is there any chance that this database was upgraded from SQL 2000?
April 4, 2014 at 3:28 pm
Keith,
I will do that. However, you are correct. We upgraded this database from 2000 to 2008 R2.
April 4, 2014 at 5:05 pm
DBCC ran with no errors.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply