Incorrectly reporting missing Extended Property

ccollins

The only difference being reported is a table extended property. The table extended property is in both databases but is showing up as missing.

If I compare two databases on the same server it does not show the difference. This is correct.

If I compare two databases on a local server and a remote server, the remote server reports the extended property as missing.

The OrderByStatement is the one reported missing on the remote server.




Table DDL:

CREATE TABLE [dbo].[Attribute] (
[AttributeID] [int] NOT NULL ,
[CategoryID] [int] NOT NULL ,
[Name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rank] [smallint] NOT NULL ,
[RowID] [uniqueidentifier] NOT NULL ,
[dbaDateCreated] [datetime] NULL ,
[dbaDateUpdated] [datetime] NULL ,
[dbaIsActive] [tinyint] NULL ,
[dbaIsDeleted] [tinyint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Attribute] ADD
CONSTRAINT [DF__Attribute__Rank__09DE7BCC] DEFAULT (0) FOR [Rank],
CONSTRAINT [AttributedbaRowIDDefault] DEFAULT (newid()) FOR [RowID],
CONSTRAINT [AttributedbaDateCreatedDefault] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateCreated],
CONSTRAINT [AttributedbaDateUpdatedDefault] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateUpdated],
CONSTRAINT [AttributedbaIsActiveDefault] DEFAULT (1) FOR [dbaIsActive],
CONSTRAINT [AttributedbaIsDeletedDefault] DEFAULT (0) FOR [dbaIsDeleted],
CONSTRAINT [PK_Attribute] PRIMARY KEY NONCLUSTERED
(
[AttributeID]
) ON [PRIMARY] ,
CHECK ([Rank] >= 0)
GO

CREATE INDEX [IX_Attribute_CategoryAttribute] ON [dbo].[Attribute]([CategoryID], [AttributeID]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_Attribute_RowID] ON [dbo].[Attribute]([RowID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Attribute] ADD
CONSTRAINT [FK_Attribute_CategoryID_Category_CategoryID] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category] (
[CategoryID]
)
GO


exec sp_addextendedproperty N'EventGUID', 'cfe3b79b-b94a-4348-8083-524f9195c5dd', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeGUIDColumn', 'false', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeHeaderColumns', 'true', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeRankColumn', 'true', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeRowIDColumn', 'false', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeRowLevelProcs', 'true', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IncludeSoftDeletes', 'false', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IsBulkLoaded', N'false', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IsDBSEnabled', 'true', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'IsReplicationArticle', 'true', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'OrderByStatement', 'order by rank asc', N'user', N'dbo', N'table', N'Attribute'
GO
exec sp_addextendedproperty N'ParentTableName', 'category', N'user', N'dbo', N'table', N'Attribute'
GO

Brian Donahue

Hi,

Is this discrepancy appearing in the side-by-side windows? If so, it may appear this way because if the extended property order is different, it may appear as the extended property doesn't exist, when it is really existing somewhere higher in the list.

ccollins

I checked the comparison results again. When comparing a local to a remote server, the extended property is not listed in the side-by-side window on the remote server side. When I look in Query analyzer or select the table's extended properties it is listed on the remote server's database.

Brian Donahue

No idea. What do you get when you query the database with the missing extended property vs the one where the extended property appears?

SELECT * from sysproperties where name='OrderByStatement'

ccollins

Select
@ServerName +'/' + DB_Name() as varchar(35)) as ServerDBName,
sp.type,
CAsT(sp.name As varchar(20)) As XP_Name,
CAsT(sp.[value] As varchar(45)) As XP_Value,
Cast(so.name as varchar(25)) As TableName
From
sysproperties sp INNER JOIN
sysobjects so ON sp.id = so.id
Where
sp.name = 'OrderByStatement'

ServerDBName type XP_Name XP_Value TableName

---

----

---

---

---

CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc PatientInsurance
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc Attribute
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc PatientPharmacy
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc PatientPractitioner
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by entrydate desc POCJournalEntry
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc POCPractitioner
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by weekday asc,starttimeoffset asc EmployeeAvailability
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EncounterService
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc ReferralContact
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EntityAddress
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EntityEMail
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc Selection
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EntityLocation
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EntityNarrative
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc EntityTelephone
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by startdate asc HospiceBenefitPeriod
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc PatientContactRole
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc AdmissionEmployee
CCOLLINS-COMP/POCOffice1168 3 OrderByStatement order by rank asc AdmissionHealthPlan

(19 row(s) affected)

ccollins

Select
@ServerName +'/' + DB_Name() as varchar(35)) as ServerDBName,
sp.type,
Cast(sp.name As varchar(20)) As XP_Name,
Cast(sp.[value] As varchar(45)) As XP_Value,
Cast(so.name as varchar(25)) As TableName
From
sysproperties sp INNER JOIN
sysobjects so ON sp.id = so.id
Where
sp.name = 'OrderByStatement'


ServerDBName type XP_Name XP_Value TableName

---

----

---

---

---

T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc PatientPractitioner
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc Attribute
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by entrydate desc POCJournalEntry
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc POCPractitioner
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by weekday asc,starttimeoffset asc EmployeeAvailability
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc ReferralContact
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EncounterService
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc Selection
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EntityAddress
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EntityEMail
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EntityLocation
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EntityNarrative
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc EntityTelephone
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc PatientContactRole
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc PatientInsurance
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc AdmissionEmployee
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc AdmissionHealthPlan
T001827-WXP5/OFFICE01_POCOFFICE 3 OrderByStatement order by rank asc PatientPharmacy

(18 row(s) affected)