Optimizations job error message
-
Hi everyone!! Happy New Year!!
ok, here is my current issue! The QA guys are adding tables to my databases. These tables are owned by "qatest". When I run the optimization job, it is throwing an error, saying it can't find the table "qatest_flash1" and to check sysobjects. It isn't in sysobjects. Is it failing because it is owned by someone other than 'dbo'?? Help!!!!!!
Thanks a ton in advance!!
-
Open the table, sysobjects, to see if it is there to confirm your guess.
If the ownership is not dbo, you may try to use the full name of an object, something like, SELECT * FROM dbName.owner.tableName
Hopefully, it will be helpful.
-
sysobjects is a system table, not a dbName. You should run
SELECT name, type FROM sysobjects WHERE name = 'yourTableName'
If it returns something, it indicates you have this object.
Run
SELECT * FROM dbName.ownerOfThisTable.tableName
To find more info about a table, you can run
SELECT TABLE_CATALOG AS [Database],
TABLE_SCHEMA AS [Owner],
TABLE_NAME AS [Name],
TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-
This is one of the enoying things where they use actual "work"-tables in stead of #-temp-tables.
So the create them on the fly, use them and drop the at the end of whatever they are doing.
This causes your maintenance jobs to fail.
Establish naming conventions so you can build your propriatary dbreindex-jobs where you exclude objects with these "work"-table-naming-convention.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ok, I found the tables in sysobjects on the db. The optimization script I am using is searching the information_schema.tables to loop thru the tables. When I query the information_schema.tables, I do in fact see all the tables. so...if they are there...why is it saying that it can't find the object. They are in the sysobjects table and the information_schema.tables?????????????????????
-
- If you're using a "sqlserver maintenance plan", it will optimize _all_ objects of the selected databases.
- verify that they don't drop and recreate their tablesat runtime ! because then the object-id's will change, causing your maintenanceplan to fail because the old-object-id cannot be found.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ok, back to this project again!!
I did a test on a qa server db. I created a simple 2 column table. the owner was dbo. I did the sp_changeobjectowner stored procedure and changed the owner to myself. It ran fine. I then tried running it again to change it back to 'dbo' and it said: Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'Dana_Test_Table' does not exist or is not a valid object for this operation.
What does this mean?? When I try to change the ownership on the qatest* tables that are giving me trouble, it says the same thing. THEY DO EXIST...what is the issue??????
Thanks all...I really want to understand this issue...
-
ok, more testing...more finds:
if I qualify the table and then run the same sp_, it changes it to dbo. YEAH!! Then for giggles, I ran the optimization script I am using and it did indeed find and reindex that table and failed at the next table that is not owned by 'dbo'. so.....I am guessing that the script isn't reindexing anything not owned by 'dbo'....
Why is that????? The script only looks in the information_schema.tables for 'base tables' which all of them are, regardless of their owner......
-
this could be your test-script ....
print '00 Creating dbo.test';
go
create table dbo.Test (col1 int not null )
go
print '01 inserting rows into dbo.test';
go
insert into test values(1);
go
print '02 Selecting from unqualified table test';
go
select * from Test;
go
print '03 sp_changeobjectowner ''dbo.Test'', ''vrz'' will go fine (warning)';
go
exec sp_changeobjectowner 'dbo.Test', 'vrz';
go
print '04 Selecting from UNqualified table test will give error ';
go
select * from Test;
go
print '05 Selecting from qualified (vrz) table test';
go
select * from vrz.Test;
go
print '06 sp_changeobjectowner ''Test'', ''dbo'' will give error';
go
-- will give "unknown object"
exec sp_changeobjectowner 'Test', 'dbo';
go
print '07 sp_changeobjectowner ''vrz.Test'', ''dbo'' will go fine (warning)';
go
-- will go fine
exec sp_changeobjectowner 'vrz.Test', 'dbo';
go
print '08 Selecting from UNqualified table test (so it will use dbo.)';
go
select * from Test;
go
print '09 Selecting from qualified (vrz) table test will give error';
go
select * from vrz.Test;
go
print '10 dropping qualified (dbo) table test';
go
drop table dbo.test;
Conclusion : always QUALIFY YOUR OBJECTS !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply