February 8, 2010 at 10:05 am
I have an application that creates and removes tables as part of its process. For some reason it doesn't always remove the tables it creates.
Running on SQL Server 2000 this does not create an issue, however I have upgraded to SQL 2005.
When I attempt to view the tables in Management Studio I receive an error "Value cannot be NULL" and no tables are displayed.
The following code displays the culprit:
SELECT TABLE_SCHEMA,TABLE_NAME, OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable') AS type FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IS NULL
TABLE_SCHEMA TABLE_NAME type
--------------- ------------- ------
NULL GRSA0000002H5 NULL
How can I remove this table with a Table_Schema of NULL?
I've tired:
ALTER SCHEMA dbo TRANSFER NULL.GRSA0000002H5
UPDATE INFORMATION_SCHEMA.TABLES SET TABLE_SCHEMA = 'dbo' WHERE TABLE_SCHEMA IS NULL
as well as some code I found using 'sp_changeobjectowner'.
The response I get is either it doesnt exist or I dont have permissions.
February 10, 2010 at 12:57 pm
what an odd problem.
what's your DB's compatibility level ?
also what does this tell you
select * from sys.schemas
select schema_id, * from sys.objects
where name = 'GRSA0000002H5'
Craig Outcalt
February 11, 2010 at 11:19 am
Compatibility Level is SQL Server 2000
In the sys.schemas query there is no schema_id with the value of 122 as is displayed in the
results from the sys.objects query.
schema_id: 122
name: GRSA0000002H5
object_id: 419688743
principal_id: NULL
schema_id:122
parent_object_id: 0
type: U
type_desc: USER_TABLE
create_date: 2006-11-01 14:47:55.077
modify_date: 2006-11-01 14:47:55.077
is_ms_shipped: 0
is_published: 0
is_schema_published: 0
February 11, 2010 at 12:12 pm
freaky. I think the SQL 2000 compat level might have something to do with it.
The only thing I can think of is to allow system catalog updates and insert that row into sys.schemas
or change the row in sysobjects to use the dbo schema.
you definitely have a ghost in the database.
Craig Outcalt
May 16, 2013 at 12:28 am
Hi,
I'm having exactly the same problem, please let me know how to drop this table as I have tried all solutions above and none work.
Also allow system catalog updates is not allowed so I'm unable to update the system table.
Many thanks,
Dao.
May 16, 2013 at 8:40 am
dao.tran (5/16/2013)
Hi,I'm having exactly the same problem, please let me know how to drop this table as I have tried all solutions above and none work.
Also allow system catalog updates is not allowed so I'm unable to update the system table.
Many thanks,
Dao.
How exactly do you end up getting a table with no schema?
Are you able to select from the table? If so, maybe you can use alter schema?
http://msdn.microsoft.com/en-us/library/ms173423.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2013 at 4:57 am
I'm not sure how the developer/application managed to create this object with a schema_id that does not exist in sys.schemas. Maybe it has something to do with database compatibility 80 on SQL 2005.
I had it resolved by using DAC in single mode and updated this object in sysschobjs to dbo id and it works fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply