Object is invalid. Extended properties are not permitted on '<*>.<

[bsdz]

Hi

I am having trouble saving extended properties via my Access 2007 ADP
project. When I make a design change and attempt to save it down I receive an
"Object is invalid. Extended properties are not permitted on '<schema
name>.<stored proc name>', or the object does not exist." error.

I have done a little investigating using SQL profiler and discovered that
Access sends the following SQL call: -

exec sp_executesql N' exec "sp_addextendedproperty"
@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8 ',N'@P1 nvarchar(14),@P2 tinyint,@P3
nvarchar(4),@P4 nvarchar(3),@P5 nvarchar(9),@P6 nvarchar(13),@P7
nvarchar(1),@P8 nvarchar(1)',N'MS_Orientation',0,N'user',N'<schema
name>',N'procedure',N'<stored proc name>',NULL,NULL

Please note the level0type is set to 'USER'. In the help on-line it is
recommended to be set to 'SCHEMA' to stop object name ambiguity.

Actually if I attempt to run the above stored proc in SSMS I receive the
same error; however, if I change to level0type to 'SCHEMA' the procedure
completes successfully and I can see the extended property attached to my
object.

Does anyone know of any work around? Perhaps something that will force my
ADP project to use SCHEMA instead of USER?

Thanks in advance
Blair

 

[Sylvain Lafontaine]

It's hard to untangle all these names, users and schemas and the situation
is even more complicated with SQL-2005 because the schema of an object
(table, stored procedure, etc.) can be very different from the name of its
owner.

With ADP 2003 against SQL-2000, I would have suggested to always use dbo as
the schema/owner of every object and set the Record Source Qualifier of
every form to dbo.

With ADP 2007, SQL-2005 and your situation, I don't know. If you have
chosen to use the schema property of SQL-2005 at its full extend, maybe it's
time to set back and make things a little simpler: create a sample database,
create a few object with dbo as the owner/schema and connect to it (from
ADP) using an account that is the owner (dbo) of this database. If
everything is OK, then check your previous database and make the necessary
adjustements until it works properly; using your sample/test database to
make some tests.

 

[bsdz]

Thanks for your suggestion. Sadly though, I can't really redesign my SQL
Server 2005 database. I also use database schemas in other applications that
connect to the database.