{"id":2925,"date":"2013-02-07T03:26:00","date_gmt":"2013-02-07T02:26:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/"},"modified":"2013-02-07T03:26:00","modified_gmt":"2013-02-07T02:26:00","slug":"sql-server-view-problem-in-lightswitch-2012","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/","title":{"rendered":"SQL Server View problem in LightSwitch 2012"},"content":{"rendered":"

\"\"<\/p>\n

I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.<\/p>\n

Here is my View in SQL Server 2012:<\/p>\n

\"\"<\/a><\/p>\n

During the import process a warning says \u00a0my view doesn’t have any primary key (apparently it’s mandatory in LightSwitch) and that a key has been inferred…<\/p>\n

\"\"<\/a><\/p>\n

When I check my view in my LightSwitch data source…<\/p>\n

\"\"<\/a><\/p>\n

…one of my field(rating_id_session) has been changed to a key which means that I will not be able anymore to view all lines of my views in a grid but just one record per session id, which is just impossible for me…<\/p>\n

To avoid this issue, we have to find a workaround which needs two steps:<\/p>\n

The first one is to avoid that our field rating_id_session becomes a key during the import. For that we will force SQL Server to mark it as “nullable” by converting it.<\/p>\n

\"\"<\/a><\/p>\n

If you now try to re-import your view, you will have the following error:<\/p>\n

\"\"<\/a><\/p>\n

All fields are nullable now, so there is no way to create an inferred key: the view is also simply not imported.
\nWe now have to create a dummy primary key to be able to import our view and to see all records.
\nThe best way to create this primary key – which could be unique for each record – is to use NewID().
\nI will also use the function ISNULL to add my new field to the inferred key.<\/p>\n

\"\"<\/a><\/p>\n

With this new key, my view is now imported and I will be able to use it correctly.<\/p>\n

\"\"<\/a><\/p>\n

To resume<\/h3>\n

When you want to take out a field from an inferred key, you can use CAST or CONVERT functions.
\nTo add a field to an inferred key, use the ISNULL function with 0 for integer fields and N” for char fields.
\nAnd if you don’t have one or more fields to use for keys, you have the possibility to use NEWID() to create one.<\/p>\n

Hope this will help you \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"

I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues. Here is my View in SQL Server 2012: During the import process a warning says \u00a0my view […]<\/p>\n","protected":false},"author":21,"featured_media":2806,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[345,49,51,54],"type_dbi":[],"class_list":["post-2925","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-lightswitch","tag-microsoft","tag-sql-server","tag-sql-server-2012"],"acf":[],"yoast_head":"\nSQL Server View problem in LightSwitch 2012 - dbi Blog<\/title>\n<meta name=\"description\" content=\"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server View problem in LightSwitch 2012\" \/>\n<meta property=\"og:description\" content=\"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2013-02-07T02:26:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"273\" \/>\n\t<meta property=\"og:image:height\" content=\"119\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Savorgnano\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Savorgnano\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\"},\"author\":{\"name\":\"St\u00e9phane Savorgnano\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"headline\":\"SQL Server View problem in LightSwitch 2012\",\"datePublished\":\"2013-02-07T02:26:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\"},\"wordCount\":364,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg\",\"keywords\":[\"LightSwitch\",\"Microsoft\",\"SQL Server\",\"SQL Server 2012\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\",\"name\":\"SQL Server View problem in LightSwitch 2012 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg\",\"datePublished\":\"2013-02-07T02:26:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"description\":\"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg\",\"width\":273,\"height\":119},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server View problem in LightSwitch 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\",\"name\":\"St\u00e9phane Savorgnano\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Savorgnano\"},\"description\":\"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals & Pharmaceuticals, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server View problem in LightSwitch 2012 - dbi Blog","description":"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server View problem in LightSwitch 2012","og_description":"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/","og_site_name":"dbi Blog","article_published_time":"2013-02-07T02:26:00+00:00","og_image":[{"width":273,"height":119,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Savorgnano","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Savorgnano","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/"},"author":{"name":"St\u00e9phane Savorgnano","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"headline":"SQL Server View problem in LightSwitch 2012","datePublished":"2013-02-07T02:26:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/"},"wordCount":364,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg","keywords":["LightSwitch","Microsoft","SQL Server","SQL Server 2012"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/","name":"SQL Server View problem in LightSwitch 2012 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg","datePublished":"2013-02-07T02:26:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"description":"I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Logo.jpg","width":273,"height":119},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-view-problem-in-lightswitch-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server View problem in LightSwitch 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c","name":"St\u00e9phane Savorgnano","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","caption":"St\u00e9phane Savorgnano"},"description":"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals & Pharmaceuticals, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2925"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2925\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/2806"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2925"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}