locked
How would I expose SQL Server system tables to a light-switch application? RRS feed

  • Question

  • I want to make an interface for business users to be able to look at the database object definitions, and add Extended Properties.   These tables exist as sys.objects, sys.columns, sys.types, and sys.extended_properties to name a few.  As they are system views, they do not appear in the "Attache Data Source Wizard."

    I saw a recommendation to create a standard view to reference the system objects, and reference the standard views.  For example:

    CREATE VIEW [dbo].[vSysExtendedProperties]
    AS
    SELECT
    [class]
    ,[class_desc]
    ,[major_id]
    ,[minor_id]
    ,[name]
    ,[value]
    FROM [sys].[extended_properties]

    Unfortunately when I referenced these in the Wizard, it came up with this message:

    "The table/view 'AdventureWorksDW2012.dbo.vSysExtendedProperties' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view."  

    Let's just say, I wasn't over-joyed from the inference that it made with regards to its choice of columns.

    So, I could create a Indexed view, that might... not it wouldn't you can't create an indexed view on a System Table either...

    So... Thoughts... Is there a quick trick without a LOT of .NET code or a bunch of Entity Framework back flips?

    Any help would be much appreciated!

    Beth Massi, do you have any thoughts?  :)

    Cheers,

    Scott


    I suppot PASS - www.SQLPASS.org

    Tuesday, October 1, 2013 3:59 AM

Answers

  • Hi,

    Ad hoc updates to system catalogs are not allowed - you need to use the sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty stored procedures. Otherwise you would have been able to fake it like this:

    Logical view of sys.extended_properties

    So you can get the read/view/keys working great but you will need to modify the save pipeline to call the stored procedures.

    I would probably prefer a WCF RIA service in this scenario.

    Cheers

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Tuesday, October 1, 2013 6:00 PM

All replies

  • Unfortunately, LightSwitch does not support CUD operations on Views.

    The other option I would think of is by using RIA services.

    Tuesday, October 1, 2013 3:53 PM
  • Hi,

    Ad hoc updates to system catalogs are not allowed - you need to use the sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty stored procedures. Otherwise you would have been able to fake it like this:

    Logical view of sys.extended_properties

    So you can get the read/view/keys working great but you will need to modify the save pipeline to call the stored procedures.

    I would probably prefer a WCF RIA service in this scenario.

    Cheers

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Tuesday, October 1, 2013 6:00 PM
  • Thanks for the recommendation to go RIA.  I saw a few references to using LightSwitch with Stored Procedures, and new that was how I would need to modify the Extended Properties.  I didn't think the system views would be an issue to wire up, if i just wanted to reference them.  I was hoping that LightSwitch would see through the smoke and mirrors and know the relationships, or do a better job guessing.  

    I think I chose the wrong opportunity for my foray into LightSwitch.  I will fail fast and use an off the shelf solution, instead of building something.  

    I am a data guy, not having enough .NET experience to make me effective with LightSwitch and SQL Server system objects.


    I suppot PASS - www.SQLPASS.org

    Tuesday, October 1, 2013 8:11 PM