Options

Incorrectly reporting missing Extended Property

ccollinsccollins Posts: 64
edited September 25, 2006 12:14PM in SQL Compare Previous Versions
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
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Options
    ccollinsccollins Posts: 64
    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.
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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'
    · Share on Twitter
  • Options
    ccollinsccollins Posts: 64
    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)
    · Share on Twitter
  • Options
    ccollinsccollins Posts: 64
    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)
    · Share on Twitter
Sign In or Register to comment.