Recently I saw an issue in SSMS after upgrading from SQL2000 to 2008. Every time you selected the tables in the object explorer an error popped up.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Unfortunately the error message is not overly informative but it did at least make me think there was an issue with the query SSMS was using. I then broke out profiler to observe what query SSMS was using to pull the list of tables. Here is the query.
exec sp_executesql N'
DECLARE @is_policy_automation_enabled bit
SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)
FROM msdb.dbo.syspolicy_configuration
WHERE name = ''Enabled'')
SELECT
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
tbl.create_date AS [CreateDate],
stbl.name AS [Owner],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/Table\[@ID='' + convert(nvarchar(20),tbl.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.tables AS tbl
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ''OwnerId'')))
WHERE
(CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit)=@_msparam_0)and((db_name()=@_msparam_1)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'msn_iris'
I then broke down the query to find out exactly where the error was coming from. After some work it turned out the error was being generated by “ISNULL(principal_id, (OBJECTPROPERTY(object_id, ‘OwnerId’)))”. I then queried sys.tables and saw that all rows for principal_id where null. I then turned my attention to the schema id. By joining sys.tables and sys.schemas I was able to find that one table had a schema_id that did not exist in sys.schemas.
It turns out that the schema_id in sys.schemas is always generated as the next highest available number. In this case I had to create 4 new schemas in order to have one with the id I needed. I then transferred the troublesome table to the dbo schema and removed the 4 schemas I had created.
At this point the tables are now showing correctly in SSMS.
-Jeremy
