Our application uses a SQL Server back-end with many stored procedures. Recently, while trying to fix an unrelated problem, we scripted a stored procedure to an ALTER statement to a SQL window to try something. Renaming our proc for testing purposes, we ran the script, and got this perplexing error:
Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added.
Property 'MS_SubdatasheetName' already exists for 'dbo.sp_One_Of_Our_Stored_Procedures'.
The ALTER statement had included code to set an extended property:
EXEC sys.sp_addextendedproperty @name=N'MS_SubdatasheetName', @value=N'[None]' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'PROCEDURE',@level1name=N'sp_One_Of_Our_Stored_Procedures'
Our code does not create or reference this property anywhere. We're 开发者_运维百科puzzled by where it came from.
Does anyone know which software creates and uses this extended property and what the setting would mean? A Google search didn't help. From the search results, I would guess the property has something to do with Microsoft Access, but what does it mean?
This does come from MS Access.
KB275085 explains that the extended property MS_SubdataSheetName setting effects whether the SYSREFENCES or INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS is cached on the first open of a SQL VIEW or TABLE for an ADP. The article says it applies to MDBs only, but it also applies to ADPs.
http://support.microsoft.com/kb/275085
Also, since the property is already created, you can work around it by using sp_UpdateExtendedProperty
精彩评论