locked
Why Does LS verion 2 and 3 limit Imports RRS feed

  • Question

  • I have finally managed to upgrade my version one app to version 2 and then I believe since I have the update installed version 3 of light switch. The only extension that was not supported was Lightswitch Utilities which I used for importing excel tables.

    In actuality I am using it to import TEXT (MS-Dos) files generated by excel by selecting show all files and selecting the text instead. This worked beautifully in version 1 I imported 10,000 to 30,000 entries straight into the database flawlessly.

    In Version 2-3 I have to use the excel importer extension which allows me the same flexibility but when I choose to save it tells me that I can only do 1000 entries of the ones I just watched imported. Can I override this limitation.

    I am aware that straight excel files bigger than a certain size made things hang up too much but with my technique it does work so I want to get rid of the limit.
    Monday, June 10, 2013 6:26 PM

Answers

  • AWE, That is the SWEET thing about SQL Server.  Up to 10 GB Database, and it is FREE, including Limited Reporting Services (including the report builder! -- enough for basic projects).

    The company I work for has the full-blown enterprise SQL server for its data... and when you get big and serious you need it.  But even then, I am using a separate copies of SQL Server Express as a 'Data workbench': a place to store non-transactional data (reporting, conversions) -- the things that are OK with only a daily backup of information such as reporting data.  So if my server goes down I could quickly rebuild everything I need from last night's backup and keep-on booking.  So even in my environment I use the Express version for 1) LightSwitch intrinsic database for published lightswitch projects, 2) Temp data for reporting and data conversions.  Then as discussed above, I connect to the enterprise databases, and keep my transactional data separate from my Intrinsic Database.

    Here is a link to get you started!  (I'm Googling the information, so if you need something different or help beyond my pay grade, Google away!)

    Download SQL Server 2012 Express with SP1 

    Microsoft® SQL Server® 2012 Express (Download) 

    One of may be the place you want to download this from.  As a note: I usually download the full enterprise version (I have MSDN Subscription), but then during installation I choose a Free Version.  I'm not studied up on exactly how to get what you want, but if you don't get exactly the features you want you might download a 'Trial' copy of enterprise, and then do like I do and choose a free version during installation. 

    Here is information about the different versions: 

     Features Supported by the Editions of SQL Server 2012

    Good luck!


    Would someone please turn on the LIGHT?

    Thursday, June 13, 2013 10:01 PM
  • I have SQL Server installed on almost every computer so I'm not sure why you would have problems.

    I did a quick internet search, and found these about using SQL Server inside SQL Server:

    Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 2012 (From our Beth, so a great place to start!)

    Creating a SQL Server Database Project in Visual Studio 2012 (Shows how to do advanced management -- probably things I'm doing directly through SQL Server, but can be done in project form)

    Good luck!


    Would someone please turn on the LIGHT?

    Friday, June 14, 2013 4:33 PM

All replies

  • OK, this doesn't 'Fix' your problem directly.  However, I wanted to detail how to access Excel direct from SQL Server.  In that way you could rewrite what you are using to use Excel.  Also, since it is all on the database side it is likely to 'Fly' compared to any front-end data processing.

    1) Set the USER that is runs the SQL Server services to have network access to the location of your files.  You will need to do that unless the files will always be local to the database server.

    2) Install the ODBC drivers on the server to support Excel access: Download 64 Bit driver for Excel access.

    3) Set up the database to allow the queries required to access the Excel files.

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    USE [master]
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    
    declare @str1 varchar(max)
    set @str1 = 'Excel 12.0 Xml;HDR=NO;Database=C:\Temp\importme.xlsx'
    select * from
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '''' + @str1 +'''' ,'SELECT * FROM [Sheet1$]');

    This works fairly well as long as saving the files to be imported to a common location is not a problem.

    As far as how to use this data is able to be used by lightswitch, you could implement it with an 'INSERT' command assuming that your Table has the same format as the excel file.  Then the command table pattern (using a trigger to run a stored procedure, etc) can be used to execute your processing code.

    Let me know if this is the route you go and how you plan to use it and I can provide more examples on how to loop through all files in a directory, for example, the syntax for filtering the excel sheet, etc.

    Good luck!

     


    Would someone please turn on the LIGHT?

    Monday, June 10, 2013 7:03 PM
  • Interesting

    First off I am not sure about what your saying. I have been giving the idea though. It does change the model of fuzzy thinking I am currently employing. My model allows individual groups to install the program and upload a certain set of data but also makes is possible for them to use their copy of the software to tweak and then export the tweaked model for others to upload. From what I see of your model there is one central online Data Source and everyone accepts the current model. I can though see a use for directly tying in Excel sheets and loading them right now using MS-Dos TEXT sheets I fly just fine given the fact that it is a rare process to implement and then only requires occasional tweaks as desired by an administrator after that. Where I run into issues is that list is frequently used to generate choice lists and when entering data on sheets there is a little lag in initially pulling up '"Items" from the list'. I would also if I had access and knowledge to modify the Autocomplete box control make it possible to enter items not in the list and leave it up to the users digression to use the lookup lists to make sure things are spelled properly. Of course that is another separate issue. It is possible that the users could be entering something not yet added to the database though most unlikely.

    On another note I have a .asp website with a server but it is not private class but personal class the distinction here being that I do not have user rights to modify the server directly as it is a shared. The company I bought the personal class asp site from administers the server and I and others have rights to serve content from it but I am not the only one on it. I say this because I am not sure I have access to should install my software to an internet site until I am the only authorized administrator and site using that server. I have been primarily developing with the intent to distribute software which could be loaded to a local server at a facility it is used at. It would be interesting though to have a way to update a database from my central source possible generated through collaboration of publishers researchers so added data could be standardized like a when an antivirus company updates a all of there customers local computers library of virus protection. I might be able to find a way to leave all of the then individual users small tweaks in place so they can hold onto there fine tuning of reference for a standard like say there own Lab ranges that differ from the standard presented and during the update it could ask for permission to change it or ask if it should be left the same. Of course all of that is above my programming level. It would be important though to allow the staff and oversight staff of healthcare teams to be abel to implement and work out new best practices or company standards. This is of crouse what I mean by fuzzy thinkign ie logic that is mroe human. Up untill now I am using more the idea of this front end infrequent upload combined with the capabilities of editign individual entries to allow the flexibility but relyign on the future front end upload of the data to rein in and standardize the model. I think a back end solution liek the one I am talkign about could be more user friendly and successful but I am still in capable of doign this kind of custom setup. I will try and seeif I can even do the things you are sudgestign on my local machine to connect office documents to the light switch data basses but I am unsure if I fully understand yoru directions for making this happen on my labtop I am using for development.
    • Edited by reigh7 Thursday, June 13, 2013 8:16 AM
    Thursday, June 13, 2013 8:15 AM
  • Where are you doing step 3 from I loaded the drivers to my laptop but what in visual studio or on my laptop do I use to make the setting you speaking of?
    Thursday, June 13, 2013 10:28 AM
  • OK, Sorry about the confusion.

    I ran my SQL code through the SQL Server Management Studio.

    However I believe that .Net has a similar interface where you can execute SQL scripts directly on your SQL Server database.  (I am new to .Net, so am on a SOLID learning curve as to everything you can do -- Google has become my best friend (grin)).

    My approach to using LightSwitch is a little different than the default LightSwitch way.  I am mentioning it so you can understand why I frequently suggest SQL Server as the place for solutions.

    Anyway, This is what I usually do:

    1. When setting up the data, I use SQL Server Management Studio.
    2. This includes specifying my own Identity Columns
    3. This includes setting the relations, using the Database Diagram tool
    4. If a choice is given, I will handle some operations via triggers I set up on the tables.
    5. This database is then added to my LightSwitch project through the Add Data Source option
    6. The really cool thing is that all of those defined relationships come in, ready to go!
    7. Therefore, you skip directly to designing the screens.

    In my opinion, here are the advantages of managing my own database in this way:

    1. Give me absolute control of the data, with very little additional work
    2. Gives me the full power of SQL Server at my finger tips (important because I do a lot of data conversion work)
    3. As you know, when you change certain things in the database in LS, it just DROPS your testing data... ANNOYING!  So that problem goes away!
    4. Allows changing my database in when running from Visual Studio to any of my databases (Such as from the DEVELOPMENT database to the TESTING database -- or even the LIVE database) without loosing anything as long as structures are the same between database installations.  (Changing database by right-click on the datasource, Click Update Datasource, then Click PREVIOUS and update the data source!  -- clicking PREVIOUS wasn't obvious to me at first)

    So, anything DATA, I do at the database, then I let LightSwitch do what it does best, and that is to give awesome interfaces in record time!


    Would someone please turn on the LIGHT?


    • Edited by JoeB_LS103 Thursday, June 13, 2013 7:23 PM
    Thursday, June 13, 2013 7:20 PM
  • I could play around with SQL Server Management Studio is there a free developer version like there is with visual studio. I am on a SOLID learning cure with that because I have been relying on my LightSwitch skills to create the data model. I actually sort of though SQL was a pat of Visual Studio and did not know that full manipulation of it comes from a different IDE software. I am self taught for the most part.
    Thursday, June 13, 2013 9:28 PM
  • AWE, That is the SWEET thing about SQL Server.  Up to 10 GB Database, and it is FREE, including Limited Reporting Services (including the report builder! -- enough for basic projects).

    The company I work for has the full-blown enterprise SQL server for its data... and when you get big and serious you need it.  But even then, I am using a separate copies of SQL Server Express as a 'Data workbench': a place to store non-transactional data (reporting, conversions) -- the things that are OK with only a daily backup of information such as reporting data.  So if my server goes down I could quickly rebuild everything I need from last night's backup and keep-on booking.  So even in my environment I use the Express version for 1) LightSwitch intrinsic database for published lightswitch projects, 2) Temp data for reporting and data conversions.  Then as discussed above, I connect to the enterprise databases, and keep my transactional data separate from my Intrinsic Database.

    Here is a link to get you started!  (I'm Googling the information, so if you need something different or help beyond my pay grade, Google away!)

    Download SQL Server 2012 Express with SP1 

    Microsoft® SQL Server® 2012 Express (Download) 

    One of may be the place you want to download this from.  As a note: I usually download the full enterprise version (I have MSDN Subscription), but then during installation I choose a Free Version.  I'm not studied up on exactly how to get what you want, but if you don't get exactly the features you want you might download a 'Trial' copy of enterprise, and then do like I do and choose a free version during installation. 

    Here is information about the different versions: 

     Features Supported by the Editions of SQL Server 2012

    Good luck!


    Would someone please turn on the LIGHT?

    Thursday, June 13, 2013 10:01 PM
  • I tried to install SQL Server Management Studio but it fails for some unknown reason. I have access to Microsoft SQL Server 2012 Business Intelligence 32/64-bit but I am unsure if this requires a separate box just for a server. Is there a specific management studio for machines running SQL 2012 for Visual Studio 2012?
    Friday, June 14, 2013 4:04 AM
  • I have SQL Server installed on almost every computer so I'm not sure why you would have problems.

    I did a quick internet search, and found these about using SQL Server inside SQL Server:

    Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 2012 (From our Beth, so a great place to start!)

    Creating a SQL Server Database Project in Visual Studio 2012 (Shows how to do advanced management -- probably things I'm doing directly through SQL Server, but can be done in project form)

    Good luck!


    Would someone please turn on the LIGHT?

    Friday, June 14, 2013 4:33 PM